Report & query (report's Record Source) prompts me for Parameter

[rmrufe]

This is my first post, and I'm brand new to developing MS Access databases, so please bear with me.

I built a database in MS Access 2010 (attached via zip file) with three tables: Clients (primary key: Client Code, which is a Text datatype), Contacts (primary key: Contact Name, which is a Text datatype) and Jobs (primary key: Job Number, which is an AutoNumber datatype). The Clients table has a 1-to-Many relationship with both Contacts and Jobs. The Contacts table has a 1-to-Many relationship with Jobs.

To enter new information, I have a Clients Form with a Jobs Subform. I didn't create a subform for Contacts, but instead dragged the Contacts table itself into the Clients Form. Within the Clients Form, I have a command button to Print Job Report. The command button's Record Source is a Query with information from all 3 tables. Here is the On Click code I have set up:

Private Sub cmdPrintJob_Click()
Dim strReportName As String
Dim strCriteria As String

strReportName = "Job Report"
strCriteria = "[Client Code]='" & Me![Client Code] & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

I'd like to print a report of the current Job Number that I'm viewing. Using only the above code, my report was displaying all jobs assigned to that client. So I added =[Forms]![CTG - Jobs subform]![Job Number] to the Criteria line of my query. After doing this, I began receiving prompts to enter a Parameter value whenever I run the query and whenever I click the "Print Job Report" command button in the Clients Form. I actually receive the parameter prompt 3 times when click the command button. I believe this is because I'm separating information on my report by the 3 separate tables..

When I enter the parameter 3 times, the report runs properly. My goal is to produce the report without having to enter the parameter.

I may be way off base here, but should I use the tables as the record source of my report instead of the query? If so, what is the proper VBA code to print the current Job Number that I'm viewing?

I appreciate any assistance you can offer me!

Ryan

 

[pbaldy]

Your reference to the subform is incorrect:

http://access.mvps.org/access/forms/frm0031.htm

 

[rmrufe]

Are you talking about my reference to the subform on the Criteria line of my query?

Or did I type something incorrectly in my VBA code for the On-Click event?

 

[pbaldy]

The query criteria.

 

[rmrufe]

I appreciate your help, pbaldy. Thanks for posting that link. Unfortunately, I think I'm having trouble deciphering which section to look that. I'm guessing the "Not in these forms" section because I'm editing criteria in a query? Then I'm assuming I look at the "On Sub 1" line since the field I'm referencing my report off of is located in the subform?

Is that correct?

 

[pbaldy]

I don't see in the sample where it's used as a subform, but something like:

Forms!MainFormName.SubformName.Form.ControlName

 

[rmrufe]

The Job Number field is in the subform.

 

[pbaldy]

And what form is that a subform of?

 

[rmrufe]

It's a subform of the Client form.

 

[pbaldy]

Apparently I've gone blind. Try

[Forms]![CTG - Clients USE THIS ONLY]![CTG - Jobs].[Form]![Job Number]

 

[rmrufe]

Thanks again. I tried the recommended Criteria code and it's now prompting me to enter two parameters to run the query.

 

[pbaldy]

Is the form open? Note the incorrect reference was also in the query parameters.

 

[rmrufe]

No, the form wasn't open when I tried typing the Criteria in the query.

 

[pbaldy]

I mean when you tried to run the query.

 

[rmrufe]

Yes, had the form open when running the query and I'm still prompted to enter a parameter.

When I close the query, I'm still prompted to enter the parameter 3 times when I click the "Print Job Report" command button on the form. The report generates successfully when I enter the parameter 3 times, but I need to fix that for my client so they don't have to enter a parameter at all.

 

[pbaldy]

Did you fix it in the Parameters area? Look at the query in SQL view at the first line.

 

[rmrufe]

That did the trick!

I can't thank you enough. I've been trying to solve this for weeks with and have been running into dead end after dead end. Words can't express how thankful I am!

 

[pbaldy]

Happy to help!