Use a LISTBOX to Select records for REPORT 2

[ZaZa]

Hi Everyone sure hope you can help me on this...

I have two tables. Table one stores a list of all the reading assignmnets ( ie specifications)that persons in various disciplines should read.[Think of specifications as manuals or technical reference books]. It also shows what is the revision number for each specification. So it has the following fields:

TblONE
---------
Specification no.
Revision no.
Discipline

Table two has a list of employees and the discipline they are in eg, mechanical, electrical, administrative. The table looks something like this:

TblTWO
----------
Name
Discipline

To find the persons who have been assigned a certain spec to read, I can do a parameter query based on the two tables and prompt for a Specification no.

Problem:
When the specifications have been updated ( ie have been issued a newer revision no) I need to run a report to notify the necessary persons that they have to read the newest rev. Sometimes more than ten specifications are revised at one time!I dont want to respond to ten prompts. Also, typo errors are common when typing a spec no.

Instead of creating ten prompts. I want to use a multiselect list box showing all the specs. User can pick all the specs that have been revised , then click a cmd button to run a report that shows the persons that have to re-read thoses specs with the newer rev nos.

I have the dialogue box with the list box and command button.The command button will open the report. I created the report based on a query with the two tables. As I said, the report works fine if the query is a parameter query prompting for one spec no. But the report is not linked to the items selcetd in the list box.

BIG QUESTION:
HOW DO I MAKE THE ITEMS SELECTED (from the list box) BECOME THE PARAMETERS FOR THE QUERY UPON WHICH THE REPORT IS BASED?BEAR IN MIND THAT
A) I CANNOT PREDICT HOW MANY SPECS WOULD BE REVISED IN THE FUTURE(hence cannot put in finite number of parameter prompt in the query)

Should I use the itemdata and items selected properties?? An Array? What what? please help.

Thanks in advance,
Zaza

Ps: Have to show Boss my "progress" next week.Urghhh...

 

[jebry]

Hi!

First build a report based on a query which will pull all(yes all) of the records. Then you can put the following code in your command buttons click event.

Dim cntl As Control
Dim lngCounter As Long
Dim varItem as Variant
Dim strCriteria as String

Set cntl = Me.YourListBox
lngCounter = 0

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & "[Specification No.] = '" & cntl.Column(0, varItem) & "'"
Else
strCriteria = strCriteria & "[Specification No.] = '" & cntl.Column(0, varItem) & "' Or "
End If
Next varItem

DoCmd.OpenReport "YourReportsName", acViewPreview, , strCriteria

This will limit the report to only the specification numbers chosen.

hth
Jeff Bridgham

 

[ZaZa]

Jeff!!

Many Blessing to you and your family!!!

It worked like a charm! I was playing around with the Items selected property but I couldn't figure out the strCriteria bit until I read your message.

Cheers and Thanks
ZaZa

 

[jebry]

Hi!

And thank you for the blessing!

I'm glad it's working for you!

Jeff Bridgham