vba coding

[Jessica]

Hi Everyone,


I want to set up a form where the user selects a UPC number or many UPC
numbers from a list box and then clicks on the button where a certain
report will be previewed or printed of the information of just the UPC
numbers that were selected. I will have over 500 UPC numbers in my table
and probably 5 different reports. The thing is, can I have it so that a
UPC range is directed to a certain report. i.e upc between the range of
1111 to 3333 will be printed using report 1, 4444 to 6666 will be
printed using report 2, and everything else not in these ranges will be
printed using report 3.

Thanks,
Jess

 

[Rob Parker]

The following code should help you:

Private Sub cmdOpenReports_Click()
Dim varSelectedUPC As Variant
Dim lngUPC as Long

For Each varSelectedUPC In lstUPC.ItemsSelected
lngUPC = lstUPC.ItemData(varSelectedUPC)
Select Case lngUPC
Case 1111 to 3333
DoCmd.OpenReport "report1", acViewPreview, ,"UPC = " & lngUPC
'preview report; change to acViewormal to print immediately
Case 4444 to 6666
DoCmd.OpenReport "report2", acViewPreview, ,"UPC = " & lngUPC
Case Else

DoCmd.OpenReport "report3", acViewPreview, ,"UPC = " & lngUPC
End Select
Next varSelectedUPC
End Sub

This assumes that your list box is named lstUPC (multi-select, with UPC
being the bound column), that your reports have a recordset which includes
UPC (which is a number), and that you want a separate report
previewed/printed for each UPC (rather than a single report for all UPCs
within a range). You should probably add error checking/trapping to cope
with no data, or ... It's up to you where you take this.

BTW, a list box with over 500 items seems a little user-unfriendly ;-)

HTH,

Rob

 

[Jessica]

Thank you so much Rob I will try this when I get home tonight. I thought
that maybe what I wanted was asking too much but I guess not.

Thanks a million,
Jess