Subreport and Report with same criteria

[knicolegobigorange]

I have a report called IDP Report and subreport within that pulls from a query with calculated values. How can I get the subreport to pull from the same criteria as the report without having the user type the criteria in 2x? Thanks a lot!

 

[GingerR]

How are you launching your report? The best thing to do is to have a form with a control on it (text box, combo box, etc) that the user fills in or makes a selection from, then they hit a button to open the report. The query that your report(s) are based on then refers to that control on the form rather than prompting the user for information. This way has a lot of benefits: If multiple reports are being run using the same data, the user doesn't have to type it in multiple times; if the data is something that has to be exact or it won't work, like a person's name, picking available names from a list is much better than typing them in each time.

So try that out and let us know how it goes for you.

g

 

[knicolegobigorange]

This is a great start, I have created the form with combo box listing Names. I have my two queries used in my report referenced back to the Combo box. If I run the report and type the names in the two fields (Forms!Name Lookup Form!Combo2) I get the report, however if I click the name in the form, I get a blank report?? I don't know quite enough to know what could be wrong.

Thanks in advance for any advice!

 

[GingerR]

Sorry, I'm a bit confused by this: " If I run the report and type the names in the two fields...I get the report, however if I click the name in the form, I get a blank report" I'm not sure what you mean by typing the names in the two fields (i thought you would just pick it from a combo box?) or what you mean by "click the name in the form"?

a hint on the side: do not use spaces in your object (form, report, query) or control (text box, combo box) names. It can be irritating later on. If you do, make sure to always surround your object and control names with square brackets.

Another hint: it's best to name your controls instead of leaving them as the default names that Access assigns them (i.e. combo1, combo2). It's best to name them something like cboEmpName or something like that.

Typically, I do this:

Form: Name = "Main"

Combo Box: Name = cboName, filled with names from a table that already exists.

Button: Name = btnViewReport. In the OnClick event of the button, you can use the Wizard to open the Report, or put code something like this:

Docmd.OpenReport "ReportName",acviewpreview

Then, as you said you have already done, replace the criteria in the query to be

[Forms]![Main]![cboName]

So if you have done all of these things, and it still doesn't work, please send back more details and we'll figure it out!

g

 

[knicolegobigorange]

Okay, after many hours trying to figure this out, even comparing to a database someone else has sent me with this option in it, and removing my subreport for the time just to work out the kinks, it is still not working.

I have done everything I can think of. What I meant before by: "If I run the report and type the names in the two fields...I get the report, however if I click the name in the form, I get a blank report" is that if I open the report directly, it prompts me to Enter Parameter with Forms!LastNameLookup!cboLastName. If I enter a name, I get the report. No name=blank report.

I think the problem is in the query pulling the information from the Combo box [cboLastName] in the form [LastNameLookup]. If I make the Form open the query directly, still doesn't work, I don't even get a blank line in the datasheet view...(leading me to believe something is wrong.)

Am I right that in the query, under the Last Name field that I should type: "[Forms]![LastNameLookup]![cboLastName]" ?? Anything else I need to do to make it pull from the combo box? Hmmm...Let me know what other details would help. Thanks again! Nicole

 

[GingerR]

Yes that is what you should have in the query.
are you sure the combo box is named cboLastName?

if you have that criteria in the query, which you do, then do not build a WHERE string in your code. Paste here the code that is in the OnClick of the button.

do you have PARAMETERS listed in the query (right-click above the query grid and choose parameters)? that should be blank.

Are the names in the table Last names? What i mean is that are the items in the combo box things that are actually in the table you're pulling from?

 

[knicolegobigorange]

--My combo box is named cboLastName

--Code for OnClick of the button:

Private Sub btnViewIDP_Click()

End Sub
Private Sub btnWork_Click()
On Error GoTo Err_btnWork_Click

Dim stDocName As String

stDocName = "IDP by Last Name"
DoCmd.OpenReport stDocName, acPreview

Exit_btnWork_Click:
Exit Sub

Err_btnWork_Click:
MsgBox Err.Description
Resume Exit_btnWork_Click

End Sub


--Parameters are blank.
--The names in the combo box are pulling from the table _Names with a Last Names field...so, yes. The items in the combo box are actually in my table.

Thanks! Nicole

 

[GingerR]

what's the name of the button? You have two subs listed, one with nothing in it.

what happens when you click the button?

 

[knicolegobigorange]

Button called [btnWork] (I have run out of names )
When the button is clicked, the report opens fine, but blank.

In trying to solve my problem, I quickly created a small, new database and did all this for that and ran a report without a hitch?? Hmmmm...

Thanks!

 

[GingerR]

Yes--it shouldn't be that big of a deal, really. It's something you'll do all the time. As long as you get it to work...great! I'm wondering if since you already had programmed the report, then undid some things to make the new functionality work...there must be something in there that's keeping it from working correctly. Start a brand new report, brand new query...etc. Welcome to the world of troubleshooting.....

 

[knicolegobigorange]

That sounds like a plan...thanks so much for your help! I think it will be valuable in all that I do. Greatly appreciated!