How to run a report while just entering parameters once? (1 Viewer)

[vbaInet]

Get rid of the button and recreate it to open the report.

 

[jimtimber]

I have done that now (created a button that just opens the report). When I click it, I have to enter ClientID 6 or 7 times.

Why can't I have it open the form I have created (Form1...with ClientID, CLientName) that is linked to the criteria in each of the queries like I have on other reports?

 

[jimtimber]

This is what I get (embedded macro)

 

[vbaInet]

jimtimber, we're going round in circles here. I've already told you that the form must be open and remain open before you open the report. The form will be used to capture whatever values you enter in the textbox.

Each report runs its own instance of the query so each report will prompt you for input if the form is not already open.

 

[jimtimber]

Sorry, I think I've not made it clear/I'm getting very confused.

Our current report system, once split, offers the user a list of buttons linked to various reports. When they click the button, a second form opens with options (such as date, venue, ClientId) that the user selects from drop-down lists on the form. Once they click run, the report the form is linked to opens. The vba I posted above tends to do this.

For this one, I want a button that, once pressed will open Form1, then I can select the clientId/username from the drop down box created on Form1 and it'll run the Report "Merged1"

Am I thinking something different?

Apologies for confusion, J

 

[vbaInet]

Our current report system, once split, offers the user a list of buttons linked to various reports. When they click the button, a second form opens with options (such as date, venue, ClientId) that the user selects from drop-down lists on the form. Once they click run, the report the form is linked to opens. The vba I posted above tends to do this.

For this one, I want a button that, once pressed will open Form1, then I can select the clientId/username from the drop down box created on Form1 and it'll run the Report "Merged1"

But you've already got code that does that like you just explained. Why can't you replicate what you already have?

 

[jimtimber]

I don't know how to, I think that's the issue.

I looked at what happens with the other reports my colleague set up. When I click one of her reports, it first opens a form with drop down boxes which I can choose certain parameters. But, there are other buttons on that form such as "print" and "preview". So, for example, I type a date into the form and click preview, it opens that report.

I'm got 2 parts, the form set up for filtering, and the final report, I just need to figure out how to get the 2 to sync....which is what I'd like to ask for help with please.

Sorry for being dumb here VBAInet, I am struggling.

 

[vbaInet]

But that's what I've been explaining ever since.

Upload a sample db so I can see what you've got.

 

[jimtimber]

Thank you, I genuinely appreciate your time. I'll strip it down to the basic tomorrow and post it up. Can I email it or must it be posted?

Thanks again,

J

 

[vbaInet]

Best to upload it on here. Only need test data (nothing confidential) and the relevant tables, queries, forms and reports. Plus mention which form and report to look at.

 

[jimtimber]

I'll do it first thing in the morn. I'll change names in the sample tables and remove and logos etc from the report. Thank you.

 

[vbaInet]

Oki dok!

 

[jimtimber]

Morning VBAINet,

Please see the attached for a very stripped out version.

The procedure:

Main Form - Frm_Training Main Screen

The command button on there then opens

Form1

Here is a list of made up Clients that I'd like to use as parameters for the report ( report name is Merged1) and the button on here runs the report. This is the form referred to in the criteria of all the queries used to make the subreports.

Merge 1 is the master form with all the subreports dropped on to it. They are all linked via CLIENTID.

The report runs as expected when run directly from the report (i.e. it asks for the clientID several times) but the button on Form1 doesn't do this.

Thank you once again!

Jim

 

[vbaInet]

See attached.
I've made the change to the query qry_COMPLETEDDATES to point to the right control. You will need to update any other query that's linked to the other subreports.

I'm not sure why you thought that referencing a label in the query would get the value selected in the listbox. And you never mentioned that a listbox was used.

 

[jimtimber]

Morning VBAInet, thank you for your work on this.

I've added the following to VBA and it seems to stitch together okay now:

Private Sub btnClient_Click()
DoCmd.SetWarnings False
CBOclient.SetFocus
DoCmd.OpenQuery ("qry_COMPLETEDDATES")
DoCmd.OpenQuery ("qry_Cancellations")
DoCmd.OpenQuery ("qry_non-attends")
DoCmd.OpenQuery ("qry_Other Qualifications")
DoCmd.OpenReport ("MERGED1"), acViewReport
DoCmd.SetWarnings True
End Sub


I've altered the combobox and and it seems to be working I have learned something today

Thanks for all your patience.

Jim

 

[vbaInet]

I'm guessing that you're opening the queries just as a test? And why are you turning off and on the warnings?

 

[jimtimber]

Hi,

I added the open queries part because when I ran the report from the button, it just opened a blank report with no data entered.

When I told it to open just 1 query, it only filled in the data on the report relevant to that query and left the remainder blank. I tried many variations but nothing would work. I used a Youtube video for the rest!

 

[vbaInet]

All of that is not necessary. All you had to do was replicate what I had done with one of the queries getting the criteria from the same listbox.

 

[jimtimber]

I couldn't see anything different with the qry_COMPLETEDDATES other than the criteria had changed? I copied that into all the relevant queries but it just returned a blank report.

Jim

 

[vbaInet]

Uploaded the relevant tables, queries, form and report let me see what you did.