asked on

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.

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lrollins

ASKER

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.

Scott McDaniel (EE MVE )

You'd need to add single quotes, then:

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

lrollins

ASKER

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.

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lrollins

ASKER

Helen,

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

lrollins

ASKER

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

Helen Feddema

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.

lrollins

ASKER

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.

lrollins

ASKER

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

lrollins

ASKER

I got it. I forget to add the basUtilities module. It works great now. Thanks so much to everyone.