Problem understanding Northwind

[John]

I have set up a database for my one man autoelectrical business. I have the
tables and forms setup ok and can input all data.
I want to do something simular to the orders setup in the Northwind sample
database. I would appreciate if someone could give me an overview of what is
going on when a user clicks on the "print invoice" button on the orders
form.

DoCmd.OpenReport strDocName, acViewNormal, "Invoices Filter"
is the primary command from this cmd button. How does this filter query work
and where does it relate to the query "Invoices" that contains the actual
data.

Any help greatly appreciated

John

 

[fredg]

I have set up a database for my one man autoelectrical business. I have the
tables and forms setup ok and can input all data.
I want to do something simular to the orders setup in the Northwind sample
database. I would appreciate if someone could give me an overview of what is
going on when a user clicks on the "print invoice" button on the orders
form.

DoCmd.OpenReport strDocName, acViewNormal, "Invoices Filter"
is the primary command from this cmd button. How does this filter query work
and where does it relate to the query "Invoices" that contains the actual
data.

Any help greatly appreciated

John

If you open the InvoiceFilter query in design view, you'll notice the
criteria for the query is
[Forms]![Orders]![OrderID]

Run the query and enter, when prompted, 10702.
Only records pertaining to that OrderID will be returned. I show 2
items.

Open the Orders form and navigate to the 3rd record (at least it's 3rd
on my computer). The sale is to Alfreds Futterkiste and the Order ID
is 10702; 2 items, the same as returned by the InvoiceFilter query.

When you have a particular order displayed on the form, that is the
OrderID passed to the InvoiceFilter query.

Because the Command Button's OpenReport command specifies that
InvoiceFilter query as the Filter argument, the report uses that query
as it's record source instead of the Invoice query.

An alternative method to filter the data would be to use the Where
clause argument instead of the Filter argument:

DoCmd.OpenReport strDocName, acViewNormal, , "[OrderID] = " &
Me!OrderID

That will translate to "Where the [OrderID] on the report equals the
OrderID displayed in the form.
Or using real values ...
"[OrderID] = 10702"

Now the Invoice query remains as the record source but the report only
prints the one invoice selected.
Note: Change acViewNormal to acViewPreview to run these sample reports
in Preview and save paper.

 

[John]

Thanks for that. Your method works fine, and much simpler.

John

I have set up a database for my one man autoelectrical business. I have the
tables and forms setup ok and can input all data.
I want to do something simular to the orders setup in the Northwind sample
database. I would appreciate if someone could give me an overview of what is
going on when a user clicks on the "print invoice" button on the orders
form.

DoCmd.OpenReport strDocName, acViewNormal, "Invoices Filter"
is the primary command from this cmd button. How does this filter query work
and where does it relate to the query "Invoices" that contains the actual
data.

Any help greatly appreciated

John

If you open the InvoiceFilter query in design view, you'll notice the
criteria for the query is
[Forms]![Orders]![OrderID]

Run the query and enter, when prompted, 10702.
Only records pertaining to that OrderID will be returned. I show 2
items.

Open the Orders form and navigate to the 3rd record (at least it's 3rd
on my computer). The sale is to Alfreds Futterkiste and the Order ID
is 10702; 2 items, the same as returned by the InvoiceFilter query.

When you have a particular order displayed on the form, that is the
OrderID passed to the InvoiceFilter query.

Because the Command Button's OpenReport command specifies that
InvoiceFilter query as the Filter argument, the report uses that query
as it's record source instead of the Invoice query.

An alternative method to filter the data would be to use the Where
clause argument instead of the Filter argument:

DoCmd.OpenReport strDocName, acViewNormal, , "[OrderID] = " &
Me!OrderID

That will translate to "Where the [OrderID] on the report equals the
OrderID displayed in the form.
Or using real values ...
"[OrderID] = 10702"

Now the Invoice query remains as the record source but the report only
prints the one invoice selected.
Note: Change acViewNormal to acViewPreview to run these sample reports
in Preview and save paper.