what criteria to use to filter results

[Guest]

I am building a database with 2 forms. One form is called residents which
contains 2 fields called name and accountnumber. The other form called
invoice contains 2 fields called invoicenumber and accountnumber.

I want to be able to click a command button in the residents form which will
preview a report that shows all invoices for a particular accountnumber (the
accountnumber of the record you are currently looking at).

Presumably this would be done using a query on the data contained in the
invoice form. What criteria would I have to use?

Seddon

 

[Allen Browne]

See:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

The article explains how to put a button on your form that opens the report
to show just that one record, by using the WhereCondition of OpenReport.

 

[Guest]

I don't think I explained myself properly.

The common field in each of the forms is accountnumber. A command button on
the residents form generates an invoice (a new record in the invoice form is
thus created). There will therefore be more than one record in the invoice
form that contain the same accountnumber value.

I want to be able to preview a report in the residents form that shows a
list of invoices generated for a particular resident.

eg

John Doe is a resident. His accountnumber is 238. He has had 6 invoices
raised. When I am viewing his record and click on the command button to
preview his invoices I get a report showing these 6 invoices.

Does that make sense?

Seddon

 

[Allen Browne]

If the name field is unique in the resident table, then you can:
strWhere = "[ClientName] = """ & Me.[ClientName] & """"
DoCmd.OpenReport "Invoice", acViewPreview, strWhere
This opens the report for containing all the invoices for the client.

In practice, client names are not unique (e.g. a father and son who have the
same name), so it would be better to use a ClientID instead of the
ClientName as the linking field between the 2 tables.

Also, if you field really is called "Name", this will confuse Access. Most
objects (including forms and reports) have a Name property, and so Access
will get confused between the name of the form and the contents of the Name
field.

 

[Guest]

The field account is unique and acts as the primary key in the resident table.

All references in the code to ClientName should therefore be changed to
account?

The first reference presumably relates to data in the invoice table and the
second reference to data in the resident table?

Seddon

If the name field is unique in the resident table, then you can:
strWhere = "[ClientName] = """ & Me.[ClientName] & """"
DoCmd.OpenReport "Invoice", acViewPreview, strWhere
This opens the report for containing all the invoices for the client.

In practice, client names are not unique (e.g. a father and son who have the
same name), so it would be better to use a ClientID instead of the
ClientName as the linking field between the 2 tables.

Also, if you field really is called "Name", this will confuse Access. Most
objects (including forms and reports) have a Name property, and so Access
will get confused between the name of the form and the contents of the Name
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I don't think I explained myself properly.

The common field in each of the forms is accountnumber. A command button
on
the residents form generates an invoice (a new record in the invoice form
is
thus created). There will therefore be more than one record in the invoice
form that contain the same accountnumber value.

I want to be able to preview a report in the residents form that shows a
list of invoices generated for a particular resident.

eg

John Doe is a resident. His accountnumber is 238. He has had 6 invoices
raised. When I am viewing his record and click on the command button to
preview his invoices I get a report showing these 6 invoices.

Does that make sense?

Seddon

 

[Allen Browne]

Yes, the Criteria argument for DLookup() matches the related table's foreign
key to the matching value from the main form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The field account is unique and acts as the primary key in the resident
table.

All references in the code to ClientName should therefore be changed to
account?

The first reference presumably relates to data in the invoice table and
the
second reference to data in the resident table?

Seddon

If the name field is unique in the resident table, then you can:
strWhere = "[ClientName] = """ & Me.[ClientName] & """"
DoCmd.OpenReport "Invoice", acViewPreview, strWhere
This opens the report for containing all the invoices for the client.

In practice, client names are not unique (e.g. a father and son who have
the
same name), so it would be better to use a ClientID instead of the
ClientName as the linking field between the 2 tables.

Also, if you field really is called "Name", this will confuse Access.
Most
objects (including forms and reports) have a Name property, and so Access
will get confused between the name of the form and the contents of the
Name
field.

message

I don't think I explained myself properly.

The common field in each of the forms is accountnumber. A command
button
on
the residents form generates an invoice (a new record in the invoice
form
is
thus created). There will therefore be more than one record in the
invoice
form that contain the same accountnumber value.

I want to be able to preview a report in the residents form that shows
a
list of invoices generated for a particular resident.

eg

John Doe is a resident. His accountnumber is 238. He has had 6 invoices
raised. When I am viewing his record and click on the command button to
preview his invoices I get a report showing these 6 invoices.

Does that make sense?

Seddon

:

See:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

The article explains how to put a button on your form that opens the
report
to show just that one record, by using the WhereCondition of
OpenReport.

message
I am building a database with 2 forms. One form is called residents
which
contains 2 fields called name and accountnumber. The other form
called
invoice contains 2 fields called invoicenumber and accountnumber.

I want to be able to click a command button in the residents form
which
will
preview a report that shows all invoices for a particular
accountnumber
(the
accountnumber of the record you are currently looking at).

Presumably this would be done using a query on the data contained in
the
invoice form. What criteria would I have to use?