Report/Subreport Problems

[owenh]

I am designing a database for invoicing. I have designed three tables: Customers, Invoices, and InvoiceLoads. They are related so that one-to-many from left to right (many loads per invoice, many invoices per customer). The tables are working together splendidly. Then I created a form (two counting the subform) using all the fields from the Invoices and InvoiceLoads tables. The form works splendidly. I put a button on the form that would preview a report. I have designed a report with a subreport that shows the customer. The report only shows the data from the Invoices table, not from the InvoiceLoads table. How do I get the subreport to the the information in the subform/in the InvoiceLoads table?

I have much to learn!
Owen

 

[llkhoutx]

You build a subreport using the same techniques you use in building a subform, except of course its a report.

 

[owenh]

You build a subreport using the same techniques you use in building a subform, except of course its a report.

Each invoice needs information from three tables: Customers, Invoices, and InvoiceLoads.

When I create the form, I use the wizard and all of the fields from the Invoices table and the InvoiceLoads table.

When I create a report using a wizard and the same fields, I do not get a subreport, just one report. Do I even need a subreport? I though I did.

Owen

 

[llkhoutx]

One solution is to create a form with two subreports, one subreport beng for invoices (related to the customer form) and the other being invoice loads (related to the invoice subreport).

Another solution is with the subreport having a query as a recordsource. That query joins invoices and invoice loads. The Invoice is displayed in the subreport header and invoice loads in the subreport detail. This subreport cannot be built with the wizard, but has to be built manually and then drug onto the report. Use the Master and Child link properties of the subreport to relate the subreport to the paren report.

Clear?

 

[owenh]

I'm back finally...had to design another (simple ) database.

The form I've created with a wizard that works great uses all the fields from the Invoices and InvoiceLoads tables, not the Customers table. The Invoices table has a field (CustomerID) that is related to the Customers table, so that is all that is necessary; I don't need to be able to modify Customer information in the form.

I have made a report in design mode that gets data from a query (Customers & Invoices fields) and threw in a subreport that gets data from InvoiceLoads and linked the two with the InvoiceID field.

How do I specify that the report should only get data from one record of the Invoices table? Do I need to put a command button on the form? That doesn't really seem to work either.

Owen

 

[Pat Hartman]

reports and subreports are linked the same way that forms and subforms are linked - with the master/child link property. Once this property is properly set, the main form/report will control what shows on the subform/subreport.

 

[owenh]

I think I've done everything right now, but I am once again plagued by parameter questions.

Every time I try to preview the report, I am asked for an "Invoices" parameter. It makes no difference what I type in, I always get the Invoices table data and no InvoiceLoads (subreport) data. Also, I don't see what I type in.

I didn't type anything in manually during the design of this report, so I couldn't have typed in a field name wrong. The problem does not exist in the subreport because I can preview it without problems (outside of the main report).

I just tried deleting the subreport from the main report and found that without the subreport I do not get a parameter dialog. Whyever is that?

Thanks, Owen

 

[Pat Hartman]

Verify that none of your queries asks for parameters.
Verify that you haven't changed any column names in the table or modified the query since you built the form.
Open the form in design view and remove any sorting or filtering properties.
Verify that the master/child links are correct.