Use a list box to select multiple records and open in report

I have created a form based on a query which prompts for a job number and filters the records displayed based on the  value entered. The form opens with an unbound list box which is based on an unmatched query that will allow me to select more than one record .  I want to be able to pass these choices to a report so that I can see if any parts were missed on the jobs I've selected.
LVL 1
lrollinsIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can loop through the ItemsSelected property of the Listbox and buildup a WHERE clause:

Dim var As Variant
Dim s As String
For each var in YourListbox.ItemsSelected
  s = s & YourListbox(0,var) & ","
Next var
'/ trim the last comma off
s = Left(s, Len(s)-1)
s = "YourField IN (" & s &")"

DoCmd.OpenReport "YourReportName" , acViewPreview, , s
0
lrollinsIT ManagerAuthor Commented:
I get a mismatch type error.  My field is a text field.  

Also the list box's query is set to prompt me for the specific job numbers that I am looking for but the actual report is based on a different query (unmatched) that shows the parts that haven't been used yet for the jobs that I picked in the list box and this is a different query all together.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd need to add single quotes, then:

For each var in YourListbox.ItemsSelected
  s = s & "'" & YourListbox(0,var) & "',"
Next var
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lrollinsIT ManagerAuthor Commented:
I'm still getting error 13 - type mismatch.

This is the line that it's highlighting...

 s = s & "'" & YourListbox(0,var) & "',"

And yes I did change the name to what my listbox is called.
0
Helen FeddemaCommented:
The data type of that item might not be String, and therefore the quotes could cause a problem.  Here is some code for using the ItemsSelected collection to create a filtered query, which could then be used as a form or report record source:

Private Sub cmdFilter_Click()
'Created by Helen Feddema 10-Aug-2010
'Last modified by Helen Feddema 10-Aug-2010

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lngCount As Long
   Dim lst As Access.ListBox
   Dim strFilter As String
   Dim strData As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strSQL As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   strFilter = ""
   Set lst = Me![lstStates]
      
   'Check that at least one state has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one state"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   
   'Create concatenated filter string
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(0, varItem))
      strFilter = strFilter & "[LocationState] = " & Chr(39) _
         & strData & Chr(39) & " Or "
   Next varItem

   'Trim last "Or" from filter string
   strFilter = Left(strFilter, Len(strFilter) - 4)
   Debug.Print "Filter string: " & strFilter
   
   'Create filtered recordset for other subform
   strQuery = "qryFilteredProjects"
   Set dbs = CurrentDb
   strSQL = "SELECT * FROM tblProjects WHERE " & strFilter _
      & " ORDER BY [LocationState];"
   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   Parent![subFilteredProjects].Form.RecordSource = strQuery
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

This code is from my Access Archon article on Filtering by Multiple Selections; here is the link for downloading the article and sample database:

http://www.helenfeddema.com/Files/accarch197.zip

and here is a screen shot of the form:

Filtering by Listbox
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lrollinsIT ManagerAuthor Commented:
Helen,

I'm going to try this but it seems very complicated.  I'm good with Access but not that good.
0
lrollinsIT ManagerAuthor Commented:
Helen,

I'm sorry I just don't know enough about this to continue.  I really like the idea because it's exactly what I want but mine uses an unmatched query from two different tables and then also has a parameter when you first open the form to only pull up a certain job and then the list on the form would only show the lot numbers for that job.  What I'm basically trying to do is compare the two table to make sure that nothing was left off of the packing list.

Thanks
0
Helen FeddemaCommented:
Are you checking that the form's record source is the same as the listbox's row source?  The simplest way to do this is to use the same record source for both.
0
lrollinsIT ManagerAuthor Commented:
I would do that but I can't even get that far because I don't know that much about Access. I don't think I can use the same query for the record source because the query has a parameter for the job number.

Maybe I'm going about it the wrong way.  I want to be able to do is this...

1.  Open the form and it prompts for the job number
2.  This will show only the Lot IDs (lstLOTS) that don't match up between qryUnmatched (which compares qryVisual and tblContainer)
3.  Then I can pick the Lot ID (lstLOTS) and have it show me the mismatched parts in the subfilterform.

I'm not sure how to do it since I'm working with a query and not an actual table.
0
lrollinsIT ManagerAuthor Commented:
Helen,

I got my form setup to use your multifilter form and everything looks right.  I ended up using my unmatched query to create the table that I needed but when I run the filter it gives me the following error...

Sub or function not defined

and it highlights CreateAndTestQuery

Lori
0
lrollinsIT ManagerAuthor Commented:
I got it.  I forget to add the basUtilities module.  It works great now.  Thanks so much to everyone.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.