Report using critera entered list box (2000)

Topic

New Reply

I have a report which currently uses combo boxes to allow the user to select 3 fields which are used as criteria for the report. The 3 fields are Factory, Line and Date.

I would like to select multiple lines (each factory can have a number of production lines) and run the report for each of the lines selected.

I’ve changed the form used to make the selections to use a list box for the entry of the lines, allowing multiple lines to be selected, but don’t know how to run the report using the selected criteria.

I’ve used very little VBA todate – I guess this is the time when I will need it.

Any one able to offer a simple guide to how I can carry out the above?

John

Reply | Quote

Replies

You will need to build a criteria string or where clause for your report while looping through the items selected collection of your listbox. At the end you should have something that resembles “[factory] = “SomeID” AND [line] = “LineID01” AND [date] = #SomeDate# OR [factory] = “SomeID” AND [line] = “LineID02” AND [date] = #SomeDate# OR [factory] = “SomeID” AND [line] = “LineID03″ AND [date] = #SomeDate#;” You can then use this string in the where argument of the OpenReport method to filter the records displayed in your report. This is just an example to get you started in the right direction. Hope it helps!

Reply | Quote

Thanks for getting back so quickly.
I understand the idea of building a criteria line, but not the “how to”.

I don’t know how to reference the entries in the list box to see if they have been selected or not.

There is/are a variable number of lines per factory, so I can’t reference them as line01, line02, line03 etc

I was expecting to have to do something like the following:

start at the top of the line list box
if not the end of the list
if the line has been selected
run report using the factory, date and this entry for line
endif
move down the list
endif

I know the syntax of the above isn’t right, but it’s the thought that counts

Anyone give better guidelines for what it should be?

John

Reply | Quote

If you look at the properties and methods of the listbox object in the online help you should be able to figure this out. Specifically look at the ItmesSelected collection and the ItemData property. With these two you should be able to loop through the collection using a For Each…Next statement and return the selected item with the ItemData property. Here is an example from the online help in Access97 (my 2002 machine is offline at the moment):

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm
End Sub

Replace the object names with the names of your form and listbox. Then, instead of debug.print, concatenate the selected values to build your where string.

Reply | Quote