Have Access decide which report to open based on criteria.

[PeakH]

I have a series of reports for printing labels with different templates depending on the type of product that the label will go on. Currently, I have it set up so that users manually decide which template they need. This works most of the time, unless there is a user error and they choose the wrong one, which can create many issues down the road.



I would like to take out the possibility for user error by having access choose the correct report based on the product information. They are already manually selecting the type of product, so can that determine which report is opened?

[jwhite]

If you don't have it already, create a table that has all your report names. Then create a junction table for your Reports table and your Products table. Then in the junction table you pair up products with their proper report.

[PeakH]

There is one more hiccup I forgot about.
Which report is needed is based on two things, not one. The first being the product type, but second is the product number.

Every product has a number that either begins with our UBI or any other combination of numbers. So if the first half of the product number is our UBI, it is an In House product. If the product number is anything else, it is an outsourced product.

So any one product type has two possible label reports, in house or outsourced. I'm sure there is a way it could detect what the product number is, but I'm lost as to how.

[Micron]

So the reports table should have a field for ProdType and one for ProdNum. It might look like

RptName	ProdType	ProdNum
report1	widget	UBI123
report1b	widget	222
report1b	thingy	985
report1c	thingy	UBI777

Report1b is used for 2 different types, which I presume means different numbers as well. When discerning the in house products, your query would look for those whose first 3 characters begin with UBI by using the Left function.

[PeakH]

Sorry for the late response. I had to put this project on hold for a couple weeks.
I have created a report name table, just a list with all the report names.
I have also created a table that has all the possible combinations of criteria and their corresponding report. I changed some of the data so that I didn’t have to use UBI numbers, after realizing that it would not always be reliable. So my combinations table looks like this:

RptName	ProdType	InHouse	Sample
Rpt1	TypeA	YES	NO
Rpt2	TypeA	NO	NO
Rpt2	TypeB	NO	NO
Rpt3	TypeB	NO	YES

My next question is then how to put it into practice.
Each criterion is based in a different related table. How do I now get access to compare the current record against my combinations table and open the report?
I’m assuming some kind of query that would result in the last three fields, but still can’t see how to get the correct report to open.

Thanks for all the help! I’m very excited to get this working.

[PeakH]

I have now created a query that will result in the correct report name.
What I would like to do is have a button on my form that runs the query and then opens the report with the same name as the results in the query.

I thought of using something like DoCmd.OpenReport [QProdLabel].[LabelName]

I'm very new to VBA and am basically just trying out a whole bunch of variations of the above, but I keep getting errors.
I feel like I'm so close but so far away...

[pbaldy]

You can't get a value from a query like that. Presuming the query returns a single record, along the lines of

DoCmd.OpenReport DLookup("[LabelName]", "[QProdLabel]")

Though I would expect the first argument to be RptName based on your table above.

[Micron]

You can get the report name returned by the query, but that would require you to create a recordset out of it, then extract the report name from the appropriate field. Not too difficult, but a simpler DLookup should work just as well. If using DLookup you'll need criteria for that, which ought to be similar to your query criteria. Once you have the report name, you pass it to the OpenReport method of the DoCmd object. Something like
DoCmd.OpenReport DLookup("RptName", "tblReports", [criteria goes here]). You might be able to figure out the syntax with the required criteria if you take a look here
https://www.techonthenet.com/access/...in/dlookup.php
It's basically the WHERE part of sql without the word WHERE, but criteria that is text must be surrounded by quotes (usually singles) and multiple criteria needs to be concatenated. There are examples of this all over the web.

[PeakH]

My query has all of the same fields as the combinations table: [LabelName] (changed from RptName), [ProdType], [InHouse], and [Sample]
PLUS two more fields [ConfNum] which gets the Confirmation number from an open form, and [Retail] which requires that you enter the unique retail number.
The combinations table can't have either [ConfNum] or [Retail] fields as there are hundreds of records, they are constantly being added and never have the same number twice. So the query more easily relates the [ConfNum] and [Retail] to [ProdType], [InHouse], and [Sample]

I looked at the link, but am not certain exactly how to relate these fields and in a Dlookup and also require that the user enters the Retail number.
I attached a picture of the query. I would like to figure out how to keep those same relations/criteria in the Dlookup. Or if it would end up being easier to create a recordset from the query and get the report name that way.

[PeakH]

I couldn't figure out the Dlookup with all the different criteria, so I went another route.

Not sure if this is what you were talking about Micron, but I created a new table [T-OpenLabel] to append the Query results to and then created a delete query to clear out the old results.
From here, I created a function to run the queries, so that there is only one line on the table making the Dlookup criteria simple.

DoCmd.OpenReport DLookup("[LabelName]", "[T-OpenLabel]", "[ConfNum]=" & "GetValue()"), acViewPreview

The GetValue function just looks at my open form to get the Confirmation Number.

I probably don't need criteria since there is only one line.
Or did I not even need to make a new table? Could I have just used the dlookup on the query instead?



Thanks for everyone's help!

[Micron]

Yes you could use a lookup on a query, with which you should be able to also eliminate (i.e not return) the records you're subsequently deleting. Surprised this works
"[ConfNum]=" & "GetValue()"
It looks like you're saying the field must contain "GetValue()" - the quotes ought to be causing this to be interpreted as a literal string and not the value returned by a function.
I would have thought it should be "[ConfNum]='" & GetValue() & "'"
Glad you got it working anyway.