Query Criteria based on a Combo Box

[puppy39]

I am trying to filter a query based on what the user selects in a combo box.

Example

User clicks button from swithboard called view list. This opens a pop up form with just a combo box called Select State. They select the state and then click on a button called "View List Based on Selection" this then opens datasheet form with the a list including only the contacts for that state selected. I am using a query because the contact information uses 3 tables.

 

[dhookom]

I would open a form that has datasheet subform on it. The combo box on the form can be used as the Link Master of the link master/child properties.

Duane
Hook'D on Access
MS Access MVP

 

[puppy39]

Thank you so much! Wow that was so easy but now for the other part of what I am trying to accomplish. I am trying to get the filtered list to set up a mail merge with microsoft word. Any ideas?

 

[dhookom]

When merging to Word, I export records to a Word Merge file. I do this with some code like:

DoCmd.TransferText acExportDelim, , "myquery", strFile

I change the SQL property of "myquery" prior to the export.

Duane
Hook'D on Access
MS Access MVP

 

[puppy39]

Where would you put this code. My intent is to place a button called "MailMerge" on the main form then in the click event a SQL statement like

Private Sub cmdMergeIt_Click()

"SELECT * FROM Qry_Merge " & _
"WHERE State = " & cbomState.Value

ADD HERE THE CODE THAT GENERATES THE MAIL MERGE.
End Sub

 

[dhookom]

I have a saved query named "myquery". Then use code like:

Private Sub cmdMergeIt_Click()
    Dim strSQL as String
    Dim strFile as String
    strFile = "C:\Temp\MergeFiles\MyMerge.csv"

    'assuming [State] is numeric?????
    strSQL = "SELECT * FROM Qry_Merge " & _
        "WHERE State = " & cbomState.Value
    Currentdb.QueryDefs("myquery").SQL = strSQL
    DoCmd.TransferText acExportDelim, , "myquery", strFile

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[puppy39]

Ahh!!! Got an error message

Run-Time error '91':
Object variable or With block variable not set

When I click debug the code stops on this line

CurrentDb.QueryDefs("myquery").SQL = strSQL

Do I have to change ("myquery") with Qry_Merge

:-(

Thank you! Thank you! Sooooo much for helping.

 

[dhookom]

In my mdb file I have a query named "myquery". If your query has a different name then change it or change the code. I don't think you created a new query that can be the actual query pushed to the merge file.

Your Qry_Merge is unfiltered. I would have a new query named "myquery" with a SQL of "Select * FROM Qry_Merge;"

Private Sub cmdMergeIt_Click()
    Dim strSQL as String
    Dim strFile as String
    Dim strQueryName as String
    strQueryName = "MyQuery"
    strFile = "C:\Temp\MergeFiles\MyMerge.csv"

    'assuming [State] is numeric?????
    strSQL = "SELECT * FROM Qry_Merge " & _
        "WHERE State = " & cbomState.Value
    Currentdb.QueryDefs(strQueryName).SQL = strSQL
    DoCmd.TransferText acExportDelim, , strQueryName, strFile

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[puppy39]

Now I am really confused. Could it be because I am not working with an mdb file is why it may not be working. I am working with an adp project connected to SQL? Are you saying I have to create another query called "MyQuery"? The QRY_Merge is the query I created with all of the fields needed for the merge. I copied your code and I got the same error. I made the following changes to the code but I still got the same error. I thought the strSQL is filtering Qry_Merge based on the combobox. Does the code creates a new query everytime. Do I just create a blank query called MyQuery. I am so sorry but I just don't get it.

Private Sub cmdMergeIt_Click()

Dim strSQL As String
Dim strFile As String
Dim strQueryName As String
strQueryName = "MyQuery"
strFile = "C:\Temp\MergeFiles\MyMerge.csv"

'assuming [State] is numeric?????
strSQL = "SELECT * FROM Qry_Merge " & _
"WHERE State = " & cbomState.Value

MyQuery = "SELECT * from Qry_Merge"

CurrentDb.QueryDefs(strQueryName).SQL = strSQL
DoCmd.TransferText acExportDelim, , strQueryName, strFile

End Sub

 

[dhookom]

Ok, I missed any mention earlier that this was an ADP. There are no QueryDefs in ADPs. My solution won't work.

I'm not sure how I would do this and don't have the time to create and test this with and ADP.

Duane
Hook'D on Access
MS Access MVP

 

[puppy39]

Okay at least I know how to create with mdb. Thank you so much.