?
Solved

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

Posted on 2014-11-21
11
Medium Priority
?
473 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
[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
  • 7
  • 2
  • 2
11 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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 85
ID: 40458088
You'd need to add single quotes, then:

For each var in YourListbox.ItemsSelected
  s = s & "'" & YourListbox(0,var) & "',"
Next var
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 1600 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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.
Suggested Courses

765 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