Select report from array using query as criteria (1 Viewer)

ctpoodle · Aug 3, 2018

I feel like my brain is just going in circles and I can't figure this one out.:banghead: I have one form, Issue Escalation Screen, which has some data pulled from a previous table and some new data input. A key input field is RESOLVER. When a button (TEMPLATE) is pushed on this screen, a query is run against the RESOLVER field and another table to find the name of the Template Report that should be opened. When I had just a few it was an easy case of if/then statements, but I now have a full array with a dozen reports. I know this should be a relatively simple case of some sort of lookup but I seem to be suffering from brain fog.

Thanks for your help.

June7 · Aug 3, 2018

What is meant by 'previous' table and 'new data' - how is that relevant?

Why is selection of report complicated? Why so many to choose from for same data?

If you want to provide db for analysis follow instructions at bottom of my post.

ctpoodle · Aug 3, 2018

June7 said:
What is meant by 'previous' table and 'new data' - how is that relevant?

Why is selection of report complicated? Why so many to choose from for same data?

If you want to provide db for analysis follow instructions at bottom of my post.

The form itself is driven by data selections. If A is selected then 1, 2, and 3 are auto-populated with certain data. One of those auto-populated is the RESOLVER field. We have approximately 100 Resolvers, not all of which will get their own report. Each report, while pulling in some of the same data, has its own differences. Therefore, it is important that the correct report be pulled for the appropriate Resolver.

Lastly, there were no instructions at the bottom of your post. However, my database consists of 2 databases - a back end and a front end anyway.

MajP · Aug 3, 2018

So do you have a table that holds the data for which report which resolver gets under what conditions? What are the fields in that table and why do you need any if then.

June7 · Aug 3, 2018

Sorry, confused with another forum site. Instructions:

To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Provide both files or import data into copy of frontend.

ctpoodle · Aug 3, 2018

Yes I do. That's why I was thinking I'd be able to do some kind of a lookup.

However, if I need to do a bunch of If/then/else lines, then I'll just do that.

June7 · Aug 3, 2018

DLookup, If/Then/Else, or dynamically construct report name with concatenation. Approach depends on info we don't have.

ctpoodle · Aug 3, 2018

No worries. I think I've got it. Thanks for letting me talk it out.

MajP · Aug 3, 2018

It sounds like the information should be contained in a table not an if then. However, if you really had a very long series of if then checking a single condition it is often much easier to use a Select Case instead of if thens.
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/select-case-statement

Select report from array using query as criteria (1 Viewer)

ctpoodle

Registered User.

June7

AWF VIP

ctpoodle

Registered User.

MajP

You've got your good things, and you've got mine.

June7

AWF VIP

ctpoodle

Registered User.

June7

AWF VIP

ctpoodle

Registered User.

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread