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

[David R]

This has to rank up there with one of the stupidest problems ever, but I've been staring at it for several hours now and nothing seems to work.

Problem: I want to automatically filter a subform based on the value of a combobox in the main form (switchboard). But I need to filter based on column(1), not .Value


Main form: Switchboard
Field to filter by: comboUserName

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

Subform: formDrafts
RecordSource: tableCitations
Field to filter on: AdminID

• Text, 6 chars.

Things I have tried:

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

3. =[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,
   

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

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

   or even
   

   "[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

   [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.

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

   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

   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

   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?

   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

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

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

 

[David R]

#8 works. Not even kidding.

 

[murray83]

thanks for asking this question and posting many of your soultions, i myself have treid number 8 but it dosent seem to work for me

i took your code and changed it to relflect mine of course which looks like

 Forms![Form1]![tblMain_SubForm].Form.Filter = "[FileName] = '" & Forms!Form1!Combo12.Column(0)
    Forms![Form1]![tblMain_SubForm].Form.FilterOn = True

my combobox only has 1 column and i belive the first column is numbered 0 but on the form load i just get this message

 

[Gasman]

Firstly, surely the Parent is already the Form?
Why not link the fields as with most subforms, no need to filter.?
I always put the bound field as the first in combos/lists and hide it?, you could try that even if you have to bring it in twice?

No messing with Column() then.?

 

[murray83]

so how would that look like as i wnat to filter down the subform

so would it be someting like and this is very bad air coding

subform.path.filter = mainform.combo12.value

 

[Gasman]

No coding, just set the link properties correctly?

 

[murray83]

No coding, just set the link properties correctly?

just tried to link master fields and Computer Says No

 

[Gasman]

The subform would need to be bound?
Why do you need unbound?

 

[gemma-the-husky]

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

Are you SURE you mean column (1)? Combo boxes are zero based. The first column is column(0) - which is generally (but not always) the value of the combobox.

so - RequiredID = mycombobox
and RequiredID = mycombobox.column(0)

are generally the same.

 

[murray83]

i didnt set it as unbound just used the create a subform wizard

 

[murray83]

sorry for DP but did a quick google about bind/unbound forms and found this aweseome site hehe binding subbforms

and have fixed it cheers all

 

[Gasman]

All you do is give it a Data source?