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

[jimtimber]

Hi,

I've made a report on a blank report template. I've dropped in 5 sub reports. I have entered into the criteria for each of the queries based on [forms]![ClientForm]!CleintID. to run the report.

It wortks but I have to enter the ClientID 5 times. Usually, when I create a button to run the form from, it only asks for the CleintID once. What can I do to resolve this please? The 5 reports are based on 3 separate queries.

Thank you!

Jamie

 

[vbaInet]

If you're being prompted to enter the criteria it could be one of two things:

1. You've mispelled the criteria

[forms]![ClientForm]!CleintID

2. The form ClientForm is not opened before you run the report.

 

[jimtimber]

Sorry, I was speed typing!

So I should create VBA code to say open the Form once the button is clicked?

Thanks for your reply!

Jim

 

[vbaInet]

I can speed tpye too

What button are you referring to? And what form is it on?

Basically the form from which you get criteria needs to be open and there must be something in it to be used as criteria.

 

[jimtimber]

Hi VBAInet

I have created a form that includes ClientID, CLient Name in a drop down box. I have referred to that form in my criteria of my queries. When I run the reports individually, it asks for the CLIENTID and runs fine. When I've added them all into 1 report as subreports, its asking for the ClientID 5 times before running the report. The report is correct when It runs.

I have added a button to run the report to the Main form. When I click it, it asks for the ClientID 5 times still, but doesn't open the form I made with the ClientID and name. I think I need to tell it via VBA to do so.

I wish I could type faster without errors!

 

[vbaInet]

It should never ask you for the ClientID if it's being pulled from the form. Let me see the SQL of the report's Record Source.

 

[jimtimber]

Record source is empty on the master report. (master report is just a blank report with sub reports dropped in)

Only the sub reports have record sources?

J

 

[vbaInet]

So let me see the Record Source of one of the subreports.

 

[jimtimber]

1 - "Final 1" record source = qry_COMPLETEDDATES
2 - "Final 2" = qry_COMPLETEDDATES
3 - "Final 3" = qry_COMPLETEDDATES
4 - "Final 4" - qry_Other Qualifications
5 - "Final5" = qry_Cancellations
6 - "Final 6" = SELECT dbo_Tbl_Client.ClientID FROM dbo_Tbl_Client WHERE (((dbo_Tbl_Client.ClientID) Like [forms]![Form1]![ClientID_Label]));
7 - "Final 7" = SELECT dbo_Tbl_Client.ClientID FROM dbo_Tbl_Client WHERE (((dbo_Tbl_Client.ClientID) Like [forms]![Form1]![ClientID_Label]));

All the queries have "Like [Forms]![Form1]![ClientID_Label]" in the ClientID criteria.

 

[vbaInet]

Does it work as a standalone query? Looks like it's a linked table to what server?

 

[jimtimber]

I couldn't stick it all into one query as the latter ones bring through multiple records per ID and was causing problems. Also, the format the person who wants the report was very specific.

The data source(s) are 3 spreadsheets I uploaded to Access, set as tables.

 

[vbaInet]

I think you misunderstood my question. Final 1 to 7 are the subreports right? I'm asking you if the following query runs without prompting you? Running it directly in a query:

SELECT dbo_Tbl_Client.ClientID FROM dbo_Tbl_Client WHERE (((dbo_Tbl_Client.ClientID) Like [forms]![Form1]![ClientID_Label]));

And are the forms actually open before you run it? It needs to be and it needs to have values in them like previously mentioned.

Are the spreadsheets actual local tables or linked?

 

[jimtimber]

Hi, when I run that code it brings through the ClientID in the output that I put in, so yes it works. (it was only supposed to bring through a number, not anything else, right?)

Thanks for helping VBAInet, both with my Access and spelling issues

 

[vbaInet]

(it was only supposed to bring through a number, not anything else, right?)

That's your code, I just copied and pasted from what you gave me so I'm not sure what you're implying?

 

[jimtimber]

Ah okay. What should I do now?

 

[vbaInet]

If that's working in a query then it should also work in your subreport. Like I mentioned, the form must be opened before you open the report and should remain open. Is this what you did?

 

[jimtimber]

I haven't set the form up to the report yet.

When I run the report by double clicking, it asks for the ClientID several times, but does display the report correctly after that.

 

[vbaInet]

So where will the report get the ID from if it hasn't been set up?

 

[jimtimber]

When I click the command button, it should open the FORM1 form, based on some VBA like:

Private Sub Command9_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ReportName = "MERGED1"

Exit_Command9_Click:
Exit Sub

The user chooses the record they want the report to run for, then clicks run and it runs.

however the code above isn't working :/

 

[jimtimber]

I have fixed the attached error, but now my code just opens the form I want to use to filter the final report, it doesn't do anything beyond that.

any ideas please?

J