Pass a Where statement to a combo box?

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Pass a Where statement to a combo box?

Post by Jeff H »

Here’s a follow up to my question from yesterday.

Now I have two combos, cboPID_filter and cboVID_filter. The user can make a choice in only one of them. Each of those combos determine the contents of a second combo, cboVID_match and cboPID_match respectively. The ‘match’ combos have record source queries called qryVIDMatch and qryPIDMatch.

The question is, how can I specify a Where clause directly in the combo boxes instead of hard coding it in the queries? As I set it up yesterday, in the query iteself I defined a parameter and the PID field criteria as:

Code: Select all

[Forms]![frmServiceEventsFind]![cboPID_filter]
This is the SQL statement for qryVIDMatch:

Code: Select all

PARAMETERS [Forms]![frmServiceEventsFind]![cboPID_filter] Text ( 255 );
SELECT DISTINCT qryServiceEvents.VolunteerID, qryServiceEvents.VID,
qryServiceEvents.FirstName_tblVolunteers, qryServiceEvents.LastName_tblVolunteers,
qryServiceEvents.PID
FROM qryServiceEvents
WHERE (((qryServiceEvents.PID)=[Forms]![frmServiceEventsFind]![cboPID_filter]));
That works fine in the one instance of the form that is used to view the data. But that form has a button to open a report, too. The report button opens a parameter form before opening the report. By default the parameters are set to whatever the user had set on the form, but I want them to be able to change the parameters for the report. To do that, the parameters form needs to have the same combo box setup as the form, and it makes sense to use the same queries, but I don’t know how to do that.

I get it that I may be over-complicating the whole thing. I can certainly make all access to the report through the viewing form so it acts as a means to review the filtered data and then print it. When the report closes it returns to the form where they can apply another filter and print that.

But I’d like to know if it’s possible to pass a Where clause to a combo box record source.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pass a Where statement to a combo box?

Post by HansV »

You can set the Row Source of cboVid_match in the After Update event of cboPID_filter:

Code: Select all

Private Sub cboPID_filter_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT DISTINCT VolunteerID, VID, FirstName_tblVolunteers, " & _
        "LastName_tblVolunteers, PID FROM qryServiceEvents"
    If Not IsNull(Me.cboPID_filter) Then
        strSQL = strSQL & " WHERE PID='" & Me.cboPID_filter & "'"
    End If
    Me.cboVID_match.RowSource = strSQL
End Sub
You don't need the parameter in qryVID_match anymore.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Pass a Where statement to a combo box?

Post by Jeff H »

Glad I asked!! These are the things I need to learn. I tend get convoluted.

Thank you very much.

- Jeff