Filtering A Recordset On A Bound Subform In VBA Using A Combobox Selection

[Amras]

Good afternoon, gentlefolk. Thank you for taking the time to review my problem.

I've got a form with a tabbed subform on it. In one of the tabs (and, eventually, all of the tabs) I've got a bound subform in which I'm trying to filter the recordset using an unbound combo box. Here's the code I'm using:

Me.RecordSource = "SELECT tblExpeditedShipping.* " & vbCrLf & _
    "FROM tblExpeditedShipping LEFT JOIN tblRushTeam ON tblExpeditedShipping.DispensingSite = tblRushTeam.Site " & vbCrLf & _
    "WHERE (((tblExpeditedShipping.Complete)=""No"") AND ((tblExpeditedShipping.RequestSelection)=[Forms]![frmExpedited]![cboFilterRequest]) AND ((tblRushTeam.LANID)=[Forms]![frmExpedited]![txtUserLANID2]));"
    
Me.Requery

I'm not getting an error message, but Access throws up an entry box asking for [Forms]![frmExpedited]![cboFilterRequest] and then [Forms]![frmExpedited]![txtUserLANID2], which kinda defeats two-thirds of the WHERE clause. The SQL is formatted as it is as I found and downloaded a db online that converts SQL to VBA with a button click (that db name is 'SQL2VBA' - don't have the address where I got it anymore).

I would appreciate any help that anyone could provide. I've got several other unbound combo boxes that I'll be using as filters, so as soon as I get the code right on this first one, it's all downhill from there. Please advise if I've been unclear or if you have any questions. Thanks!

[June7]

Where is the code and where is the unbound combobox?

Be aware that subforms actually load before the main form - I know, weird but true. So if the code is behind subform but combobox is on main form, the combobox is not available when the subform loads.

[Amras]

Thanks for your quick reply, June7! Both the code and the unbound combo box are within the subform. The database is loaded with a recordset that's set in the subform at the Form_Activate() event on the main form and the user then has the option of filtering the subform by their combo box selection.

[June7]

SQL statements from query objects usually need some editing. Such as concatenating variables. References to controls on form are variables.

Is Complete a Yes/No Boolean type field? Are RequestSelection and LANID both number type fields?

Try:

Me.RecordSource = "SELECT tblExpeditedShipping.* " & _
"FROM tblExpeditedShipping LEFT JOIN tblRushTeam ON tblExpeditedShipping.DispensingSite = tblRushTeam.Site " & _
"WHERE tblExpeditedShipping.Complete=False AND tblExpeditedShipping.RequestSelection=" & Me.[cboFilterRequest] & " AND tblRushTeam.LANID=" & Me.[txtUserLANID2] & ";"

or if answer is no to both:

Me.RecordSource = "SELECT tblExpeditedShipping.* " & _
"FROM tblExpeditedShipping LEFT JOIN tblRushTeam ON tblExpeditedShipping.DispensingSite = tblRushTeam.Site " & _
"WHERE tblExpeditedShipping.Complete='No' AND tblExpeditedShipping.RequestSelection='" & Me.[cboFilterRequest] & "' AND tblRushTeam.LANID='" & Me.[txtUserLANID2] & "';"

Note the apostrophe delimiters for text field parameters.

However, I seldom use code to modify RecordSource (only done this once for a report, never form). I set the form Filter and FilterOn properties.

[JLCan]

Hi Amras!

One thing I don't understand: does the combo need to be within the subforms; i.e. each one will display different subset of records? If so, there is a very simple solution.

1 - In each page create the combos you will use to filter the subform data.
2 - Then create the subform.
3 - Select the SUBFORM then display the data tab.
4 - In the master link enter the name of the combos.
5 - In the child link enter the name of the filter targets: RequestSelection and LanID
6 - Access will process the filter automatically.

At load time you can hide the subforms and make them visible after the 2nd combo is set.

P.S. - I have noticed that this procedure is generally ignored by average users.

Good luck, JLCantara.

[Amras]

My sincere thanks, June7! Your second suggestion was exactly what was needed! The end-users have demanded to be able to see this information in a Datasheet view as well as the form, so I couldn't use Boolean or number type fields and had to use words instead. The correct syntax of an SQL statement in VBA typically gives me headaches that would drive one to drink!

Thanks for your input, JLCan. You've brought a very interesting design idea to mind; I may try to use that in a later db! Must keep this thread address handy to come back to your suggestion!

[June7]

Why not Boolean or number types?

Can use Boolean (Yes/No) fields in Datasheet view. If you don't want to see checkbox, set the Format property to display Yes or No values. I use combobox on Yes/No fields.

[Amras]

Well, now - don't I feel stupid! Yeah, that's excellent advice; next time. I'm not going to go back and change everything that I've built now, but I'll have to do that with the next db I build. Thanks again!