Sandy Sailer
asked on
access 2010 - create a query based on multiple values from a listbox
I have a listbox named List2. My form is named frmMulti. I want to select multiple values from the listbox and filter a query based on my selections. fileNumber is the unique field within the recordsource of the listbox. I'm having trouble getting the query to filter properly based on the selections of the listbox. Thanks!
ASKER
Brilliant! Then how would I translate it into a query, instead of outputting msgbox strCriteria?
As a rule you don't want users accessing data directly through tables or queries. The typical use for this type of criteria is to filter down records in a table or form to display what the user needs.
So you would create a form with a record source consisting of the whole table and filter the records when opening the form like this :
Docmd.opeform "formname",,,strcriteria
So you would create a form with a record source consisting of the whole table and filter the records when opening the form like this :
Docmd.opeform "formname",,,strcriteria
Alternatively, you could build the full SQL string for a query based on the criteria like this:
strSQL = "SELECT * FROM YourTable WHERE " & strCriteria
strSQL could then be applied in a variety of ways:
Me.MyListBox.Rowsource = strSQL
Me.MyForm.Recordsource = strSQL
etc...
It all depends on what you need to do.
Can you describe how you want to use the resulting query?
strSQL = "SELECT * FROM YourTable WHERE " & strCriteria
strSQL could then be applied in a variety of ways:
Me.MyListBox.Rowsource = strSQL
Me.MyForm.Recordsource = strSQL
etc...
It all depends on what you need to do.
Can you describe how you want to use the resulting query?
ASKER
Ideally, what I want to do is allow the user to select a bunch of file numbers from the list box, have the query filtered based on the selections (the query would be the underlying record source for a summary report as well), and then create another process for looping through the email addresses and sending a PDF report to each recipient. I have already created a process for emailing a report to ONE person from a combo box, but I would need to create a different process for emailing a group of people as well. Thanks. I haven't worked with Access for about ten years, so I feel like I'm starting over with this one...
Okay - thats very similar to the form scenario I described in my last comment.
To get this up and running, create your report using a recordsource with no criteria (you should see all records in it).
Then open the report after setting up the criteria string like this
Using a WHERE condition in the OpenReport statement effectively and dynamically adds a WHERE clause (criteria) to the query that you are using as the recordsource of your report.
For the emailing routine, go ahead and post a seperate question. Many of the Access Experts have sample code to do what you have described.
To get this up and running, create your report using a recordsource with no criteria (you should see all records in it).
Then open the report after setting up the criteria string like this
Private Sub YourCommandButton_Click()
Dim strCriteria As String
Dim varItem As Variant
If Me.List2.ItemsSelected.Count = 0 Then
MsgBox "Nothing was selected"
Exit Sub
End If
For Each varItem In Me.List2.ItemsSelected
strCriteria = strCriteria & Me.List2.ItemData(varItem) & ","
Next
If Right(strCriteria, 1) = "," Then strCriteria = Left(strCriteria, Len(strCriteria) - 1)
strCriteria = "fileNumber IN (" & strCriteria & ")"
Docmd.openreport YourReportName, acViewPreview,, strCriteria '<----- Add this
End Sub
Using a WHERE condition in the OpenReport statement effectively and dynamically adds a WHERE clause (criteria) to the query that you are using as the recordsource of your report.
For the emailing routine, go ahead and post a seperate question. Many of the Access Experts have sample code to do what you have described.
ASKER
I created a report called rptSUMMARY, with the underlying query record source containing NO criteria. It's causing an error at this line in the code:
DoCmd.OpenReport rptSUMMARY, acViewPreview, , strCriteria
The error is run-time error 2497.
The action or method requires a Report Name argument.
Thank you!
DoCmd.OpenReport rptSUMMARY, acViewPreview, , strCriteria
The error is run-time error 2497.
The action or method requires a Report Name argument.
Thank you!
ASKER
This is my query:
SELECT tblFiles.fileNumber, tblFiles.client, tblFiles.Contact, tblFiles.email, tblFiles.advparty, tblFiles.resident, tblFiles.fileopened, tblFiles.facility, tblFiles.amtclaimed, tblFiles.amtcollect, tblFiles.telephone, tblFiles.c_claimamt, tblFiles.update, tblFiles.inactive, tblFiles.who, tblFiles.dcfilenum, tblFiles.resppartner, tblHistory.notes
FROM tblFiles LEFT JOIN tblHistory ON tblFiles.fileNumber = tblHistory.fileNumber;
I need to join two tables together to get all of the related data to show up on the report.
SELECT tblFiles.fileNumber, tblFiles.client, tblFiles.Contact, tblFiles.email, tblFiles.advparty, tblFiles.resident, tblFiles.fileopened, tblFiles.facility, tblFiles.amtclaimed, tblFiles.amtcollect, tblFiles.telephone, tblFiles.c_claimamt, tblFiles.update, tblFiles.inactive, tblFiles.who, tblFiles.dcfilenum, tblFiles.resppartner, tblHistory.notes
FROM tblFiles LEFT JOIN tblHistory ON tblFiles.fileNumber = tblHistory.fileNumber;
I need to join two tables together to get all of the related data to show up on the report.
Here you go -
The report name needs to be in quotes if it is a literal (non-variable) value:
The report name needs to be in quotes if it is a literal (non-variable) value:
DoCmd.OpenReport "rptSUMMARY", acViewPreview, , strCriteria
ASKER
It's opening the report, but the report is coming up blank - no records. The underlying query is still showing all records. Thanks.
That generally means that there are no records matching the criteria specified (ie: your listbox selections).
Verify that the fileNumber selections you have made are indeed present in your query results.
Verify that the fileNumber selections you have made are indeed present in your query results.
Also, is FileNumber a text or numeric field?
ASKER
FileNumber is a text field. This is the way it was originally set up when I inherited it. Yes, there were records present when I manually ran the query based on a list box selection.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
EXCELLENT!!!
Glad we got it ;-)
ASKER
Couldn't have done it without all of your help :D
Open in new window