Solved

access 2010 - create a query based on multiple values from a listbox

Posted on 2011-09-12
17
1,215 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:ssailer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36526663
This shows how you would build criteria for FileNumber based on the selections from a multi-select listbox:

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 & ")"    
    msgbox strCriteria
    
End Sub

Open in new window

0
 

Author Comment

by:ssailer
ID: 36527083
Brilliant!  Then how would I translate it into a query, instead of outputting msgbox strCriteria?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36527820
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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 61

Expert Comment

by:mbizup
ID: 36528261
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?
0
 

Author Comment

by:ssailer
ID: 36529039
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...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36529369
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

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 

Open in new window


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.
0
 

Author Comment

by:ssailer
ID: 36529460
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!
0
 

Author Comment

by:ssailer
ID: 36529482
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.  
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36529512
Here you go -

The report name needs to be in quotes if it is a literal (non-variable) value:

DoCmd.OpenReport "rptSUMMARY", acViewPreview, , strCriteria

Open in new window

0
 

Author Comment

by:ssailer
ID: 36530458
It's opening the report, but the report is coming up blank - no records.  The underlying query is still showing all records.  Thanks.  
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36530503
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36530541
Also, is FileNumber a text or numeric field?
0
 

Author Comment

by:ssailer
ID: 36530789
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.  
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36530833
Okay - if it is a text field, then each criteria selected needs to be enclosed in quotes to delimit it as text.

Revise the function I gave you 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) & "',"  '<--- I've added single quotes here to delimit Text filenumbers
    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 

Open in new window

0
 

Author Closing Comment

by:ssailer
ID: 36533050
EXCELLENT!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36533097
Glad we got it ;-)
0
 

Author Comment

by:ssailer
ID: 36533109
Couldn't have done it without all of your help :D
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using a Symbol barcode scanner in a form that was created in Access 2010 7 48
Linking Master Child Fields 2 28
Microsoft Access 2016 Bug? 9 47
Access 2010 7 51
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question