Query format for report-mainform/subform

[bangsmic]

Normally when I want to print the record data on the screen (one record) I create the query and use the criteria property in the unique field. ([forms]![formname].[fieldname]. A command button referencing a macro to run the query and print the record in the report always works until now. The difference with my current project is that I am using the record displayed in a main form and a sub form.

For example: Main form is its own table and contains client data (name, address, etc). The sub form is its own table and contains estimate data (Estimate date, Estimate #, Description of work, etc). For each client there will be many estimates. Using the above method I can only get the current client and the first record of the estimate. Whats the trick to this?

 

[RickSpr]

You'll need to select from an inner join between the main form's table and the subform's table.

You'll also need two criteria, one for the main form and one for the subform. To access the subform's unique field, use the Form property of the subform control. For example,
WHERE [mainfield] = Forms![formname].[mainfield]
AND [subfield] = Forms![formname]![subformcontrolname].Form.[subfield]

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[bangsmic]

Works like a charm, thanks a bunch.