help please

[Jessica]

I accidentally posted this in forms which is probably better posted here
can anyone help me with this?



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

 

[Angi]

Jess,
I can help you set up the reports for the breakdowns, but what if the
user selects numbers that match more than one report?? Is that
possible?

For example: they pick, 1111 and 4444 and 7777. That would pull up 3
reports with one button?

Let me know,
Angi

 

[Jessica]

Thank you Angi for your reply. I was thinking if the user had to print
out like 5 reports individually that would take some time rather than
just selecting all the upc they wanted right at the start and print
reports for all at once. If that is possible. Also I would like to
create a button to do the same but preview instead of print which I'm
sure I can figure out once I get started with the print coding.

FYI I have my reports drawing data from their own queries. Right now the
user has to open up the correct report for a certain upc and it will ask
to enter the upc and the report shows all the information for that upc.
The reason I want to set it up like I described in my previous post is
that if a user is not familiar with which upc belongs to which report
then they won't receive the wrong information.

Also not sure if it matters but just so you know I have all materials in
their own table and a master table called upc where all the data from
the other tables are linked to an individual upc.

Thank you so much for your help Angi,
Jess

 

[Angi]

Jessica,
Don't thank me yet...I don't know how much help I will be. Why are you
breaking down the UPC's? Are the reports different depending on the
UPC? Can you send all the UPC's to the same report and have the report
broken down into pages instead?

For starters, you can breakdown your reports with the queries. Add the
code : Between 1111 AND 3333 to the criteria line of the UPC column
for report 1, same for report 2. For report 3, you'll want it to say
Not Between 1111 AND 6666. If you need to break numbers apart just
join them with AND. ie: Not Between 1111 AND 3333 AND Not Between
5555 AND 6666. That will only allow those records.

To create a btn that prints the report, put a command button on your
form and put this in the OnClick event of that button:

Docmd.openreport "YourReportName", acNormal, "YourCriteria"

Replace YourReportName with your report name. As far as the criteria,
I don't know where you are getting it so I can't tell you that one.
I've never done a multiple select combo box. To create a button that
previews, it would be the same except acNormal becomes acViewPreview.

There is one problem with this...it's only going to print one report.
You're going to need something else to determine which UPC goes with
what report, but I'm not sure how you would do that without a lot of
code. Hopefully, the experts are watching this thread and will answer
that for you. I hope this helps you get started at least.

Good luck!
Angi

 

[MacDermott]

"Expert" may be an overstatement, but I've seen code at
www.mvps.org/access/forms which will help you set criteria from a
multi-select listbox. (Only listboxes can be multiselect, never
comboboxes.)
In fact, that's a great site for all kinds of Access ideas and
techniques.

Your suggestion to limit the queries that feed the individual reports is a
great one; that should give Jessica most of what she wants.
For the 5 reports, she can just use 5 DoCmd.OpenReport .... commands, one
for each report.
The criteria argument will be developed using the reference above.
Those criteria (essentially - "use only UPCs that were selected in the
listbox") will be "added" to the built-in filters on the queries, so each
report will show only UPCs which are both selected in the listbox and
appropriate for that report.

Initially, she may get blank pages for reports which don't have any data
matching both requirements -
Jessica, if you get to that point, please post back for further help.

BTW - you can get the report to show on the screen instead of printing
directly by using acPreview instead of acNormal in the DoCmd.OpenReport
command.

HTH
- Turtle

 

[Jessica]

Thank you Angi and Turtle you two are great. I've got another idea from
a post that I accidentally posted in the wrong area from Rob. I haven't
tried it yet but will later.

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

Thanks again to you both you have been really helpful,
Jess