Solved

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

Posted on 2014-11-21
11
353 Views
Last Modified: 2014-12-03
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.
0
Comment
Question by:lrollins
  • 7
  • 2
  • 2
11 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 40457812
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
 
LVL 1

Author Comment

by:lrollins
ID: 40457891
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
 
LVL 84
ID: 40458088
You'd need to add single quotes, then:

For each var in YourListbox.ItemsSelected
  s = s & "'" & YourListbox(0,var) & "',"
Next var
0
 
LVL 1

Author Comment

by:lrollins
ID: 40462066
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 400 total points
ID: 40472503
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:lrollins
ID: 40475874
Helen,

I'm going to try this but it seems very complicated.  I'm good with Access but not that good.
0
 
LVL 1

Author Comment

by:lrollins
ID: 40476079
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40476401
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
 
LVL 1

Author Comment

by:lrollins
ID: 40476911
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
 
LVL 1

Author Comment

by:lrollins
ID: 40478556
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
 
LVL 1

Author Comment

by:lrollins
ID: 40478577
I got it.  I forget to add the basUtilities module.  It works great now.  Thanks so much to everyone.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now