Results 1 to 11 of 11
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Have Access decide which report to open based on criteria.

    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?

  2. #2
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    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.

  3. #3
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    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.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,781
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  5. #5
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    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.

  6. #6
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    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...

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,716
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,781
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    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.

    Click image for larger version. 

Name:	Label Query.PNG 
Views:	14 
Size:	30.3 KB 
ID:	28300

  10. #10
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    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!

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,781
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-23-2014, 02:02 PM
  2. Open form based on search criteria
    By cactuspete13 in forum Forms
    Replies: 7
    Last Post: 03-06-2013, 10:17 AM
  3. Open 1 of 2 Forms based on Criteria
    By DCV0204 in forum Forms
    Replies: 28
    Last Post: 11-23-2011, 03:09 PM
  4. Replies: 1
    Last Post: 07-02-2010, 03:55 AM
  5. Open form based on Subform criteria
    By Suzan in forum Programming
    Replies: 0
    Last Post: 04-25-2006, 02:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums