Filter subform with multiple controls

[jimster68]

I'm trying to filter a subform with multiple combo boxes and eventually some text fields tied to one command button to execute the search/filter. I want the user to determine which filter or combination of filters to use.
I started with the combo boxes and can get one to work, however, when I add the second one I am forced to use both or an error is produced. The error is Runtime Error '2448' you can't assign a value to this object.
Here is a sample.

Private Sub cmdFilter_Click()
sfrm_Server_Data.Form.Filter = "Loc_ID=" & cboLoc.Value & _
"AND Cab_ID=" & cboCab.Value

sfrm_Server_Data.Form.FilterOn = True

End Sub

If I add a value in both combo boxes everthing works fine but I don't want to force the use of the second combo box.

How would I write this to allow for one or more filter selections tied to one command button?

Thanks,
Jim

 

[Wiz47]

I'm trying to filter a subform with multiple combo boxes and eventually some text fields tied to one command button to execute the search/filter. I want the user to determine which filter or combination of filters to use.
I started with the combo boxes and can get one to work, however, when I add the second one I am forced to use both or an error is produced. The error is Runtime Error '2448' you can't assign a value to this object.
Here is a sample.

Private Sub cmdFilter_Click()
sfrm_Server_Data.Form.Filter = "Loc_ID=" & cboLoc.Value & _
"AND Cab_ID=" & cboCab.Value

sfrm_Server_Data.Form.FilterOn = True

End Sub

If I add a value in both combo boxes everthing works fine but I don't want to force the use of the second combo box.

How would I write this to allow for one or more filter selections tied to one command button?

Thanks,
Jim

SELECT YourTable.*
FROM YourTable
WHERE (([TableField1]=Forms!YourFormName!ControlName Or Forms!YourFormName!ControlName Is Null)=True) And (([TableField2]=Forms!YourFormName!ControlName2 Or Forms!YourFormName!ControlName2 Is Null)=True) And

Keep going to include all of the controls that you want to use as filters in the form. Just keep adding an AND between them. Then you can either filter with or without them having any data in them. Together, separately, or not at all.

 

[jimster68]

thanks for the response. I guess I failed to mention that this subform is based on a query that pulls from multiple tables. I assume I would substitute the query name for the table name in your example?

I did manage to find an VBA example of what I was looking for and implemented that code. All is working well now.

 

[Wiz47]

thanks for the response. I guess I failed to mention that this subform is based on a query that pulls from multiple tables. I assume I would substitute the query name for the table name in your example?

I did manage to find an VBA example of what I was looking for and implemented that code. All is working well now.

I'm glad to hear that you got it working. An answer to your question for future use - you could inner join the tables then use nearly the same WHERE statement to pull the data after that.

 

[ScrmingWhisprs]

I am working on a shelflist database for the library I work at, and I have used this code in my shelflist filter. There are 6 fields that I can filter by, one being Call Number. I am trying to use something like

Like Forms!frmShelflistBrowse!tboCallNo & "*"

as part of the criteria. I tried putting it in as

(([Call No]=Like Forms!frmShelflistBrowse!tboCallNo & "*" Or Forms!frmShelflistBrowse!tboCallNo Is Null)=True)

but I get a syntax error (missing operator) message for my expression when I try to save.

Any help here is greatly appreciated!


ScrmingWhisprs