Filter subform based on main form combo.column.(x) (1 Viewer)

• 4 columns.
• Column 1 is tied to tableStaffing.EmployeeID, Text input, 6 chars.
• None of the columns are zero width.

• Text, 6 chars.

1. Enabling/Unlocking the comboUserName appears to make no difference.
2. I do have Allow Filters turned on.
3. Code:Copy to clipboard

   =[Parent].[Form].[comboUserName].[Column](1)

   This works just fine for an unbound field in the subform, but when I try to filter by it in Form Properties,
   
   Code:Copy to clipboard

   [tableCitations].[AdminID] = Me.[Parent].[Form].[comboUserName].[Column](1)

   Code:Copy to clipboard

   [AdminID] = [Parent].[Form].[comboUserName].[Column](1)

   or even
   
   Code:Copy to clipboard

   "[tableCitations].[AdminID] = '" & [Parent].[Form].[comboUserName].[Column](1) & "'"

   I get Undefined function: '[Parent].[Form].[comboUserName].[Column]' in expression and no results are returned (the filter doesn't match anything, so everything's filtered out).
   
4. Per http://access.mvps.org/access/forms/frm0031.htm, I tried
   Code:Copy to clipboard

   [AdminID] = Forms!Switchboard!comboUserName.Column.(1)

   The error here instead is Syntax error in query expression '[AdminID] = Forms!Switchboard!comboUserName.Column.(1)' and no filter is applied at all.
5. I have tried filtering on another field with the same content type (Text, 6), and a hardcoded value works fine.
   Code:Copy to clipboard

6. Looking at an older database, I gave up on Form Properties and went to VBA. A dummy button with
   Code:Copy to clipboard

   Me.Filter = "[AdminID] = '" & Me.Parent.comboUserName.Column.(1) & "'"

   throws the mystifying error Expected: identifier or bracketed expression on compile, and then highlights the (. Changing it to
   Code:Copy to clipboard

   Me.Filter = "[AdminID] = '" & Forms!Switchboard!comboUserName.Column(1) & "'"

   works when I push the button, but the whole point here is for this to be an automatic filter.
7. OK, so make it automatic the hard way. In the subform
   Code:Copy to clipboard

   Private Sub Form_Load()
       Me.Filter = "[AdminID] = '" & Forms!Switchboard!comboUserName.Column(1) & "'"
       Me.FilterOn = True
   End Sub

   Nope; Forms!Switchboard!comboUserName.Column(1) is Null at that point in the form's loading cycle! (http://office.microsoft.com/en-us/a...nts-for-database-objects-HA010238988.aspx#BM3) - so no results come back due to a bad filter.
8. So what if we load it from the Switchboard's Load event?
   Code:Copy to clipboard

   Private Sub Form_Load()
       Forms![Switchboard]![subformDrafts].Form.Filter = "[AdminID] = '" & Forms!Switchboard!comboUserName.Column(1) & "' AND CitationMailed Is Null"
       Forms![Switchboard]![subformDrafts].Form.FilterOn = True
   End Sub

   Same problem, that field is not yet populated, so no results can possibly match.
9. :banghead:
   I am about ready to try
   Code:Copy to clipboard

   DLookup("EmployeeID","tableStaffing","EmployeeLogin = '" & Forms!Switchboard!comboUserName & "'"

   which will be the dumbest solution in the history of science if it works...

Gasman said:

No coding, just set the link properties correctly?

Click to expand...

• 4 columns.
• Column 1 is tied to tableStaffing.EmployeeID, Text input, 6 chars.
• None of the columns are zero width.

Click to expand...