Incremental number in receipt (report)

[mij via AccessMonster.com]

Hi!

I have a Data File on Access 2002. Is someone has a counsel to give me to do
these:

1) I have a command button in a form associated to each customer consultation,
to open a receipt (report) that I can print. I want a (incremental) number
appear on each receipt each time I print a different one, and the possibility
to print again that receipt with the same number on and the mention
"DUPLICATA" on it.

2) I want a mark appear on that form to indicate the receipt was printed;

Also, how to do a receipt with dates in rows rather than in columns?

Thanks!

 

[Allen Browne]

This idea of just assiging the next available number to get a receipt is
inadequate. You must store what receipt number goes with which consultation
or you will not be able reprint the same receipt again later.

If you are absolutely certain that:
- no customer will ever make multiple part-payments for a consultation;
- no customer will ever pay 2 consultations at once;
- no customer will ever pre-pay for a consultation that has not occurred
yet;
- no customer will ever pay for someone else (e.g. a parent for a child)
etc, then you could just add another field to your Consultation table, with
these properties:
- Name ReceiptNum
- Type Number
- Size Long Integer
- Default Value None (Important to clear this property.)

When the customer pays for the consultation, you can then use code in your
form to lookup the next available number, and assign it to the field, e.g.:
Me.ReceiptNum = Nz(DMax("ReceiptNum", "Consultation", _
"ConsultationID = " & Nz([ConsultationID]),0)),0) + 1

If you could have more than one user entering information at once, that's
not really adequate either.

But more importantly, you need a different data structure if any of the
conditions above could ever apply.

 

[mij via AccessMonster.com]

Hi,

First, thank you for your answer to my last question.

I created a table with a field "Value" which is incremented with an Add
request. Then, when I clic on the command button of my customer-form, the
preview before print of the receipt is show. The receipt is based on a report,
and this report is based on a request which put together each consultation
date and the fee paid by the customer for a specific period where the person
didn't get already a receipt.

Still, I need help on 4 problems:

1) When I clic on the command button of the receipt in the customer
consultation form (each customer has a principal form at his name and a new
consultation form for each consultation), 2 little windows open one after the
other asking the first name and name of the customer, since I build the
request like that to get the customer name on the receipt. But I would want
to associate the command button with the customer name which appear on the
principal form near of this button instead of type it in these little windows.
It will be interesting that the name and first name appear automaticly on the
receipt preview without to type it.

2) The number of the receipt is incremented each time that I clic on the
command button of the receipt of the consultation form which open a preview
of the receipt. But, I want the number be incremented only when the receipt
is printed, because sometime, I will only verify the receipt with the preview,
and close it.

3) If the receipt is printed, I need than the checkbox on each consultation
form be ticked automaticly for each consultation which appear on the print
receipt.

4) If the receipt was printed a first time, I need to have to possibility to
print it again (duplicata) in the future.

How to do that?

Thanks!

This idea of just assiging the next available number to get a receipt is
inadequate. You must store what receipt number goes with which consultation
or you will not be able reprint the same receipt again later.

If you are absolutely certain that:
- no customer will ever make multiple part-payments for a consultation;
- no customer will ever pay 2 consultations at once;
- no customer will ever pre-pay for a consultation that has not occurred
yet;
- no customer will ever pay for someone else (e.g. a parent for a child)
etc, then you could just add another field to your Consultation table, with
these properties:
- Name ReceiptNum
- Type Number
- Size Long Integer
- Default Value None (Important to clear this property.)

When the customer pays for the consultation, you can then use code in your
form to lookup the next available number, and assign it to the field, e.g.:
Me.ReceiptNum = Nz(DMax("ReceiptNum", "Consultation", _
"ConsultationID = " & Nz([ConsultationID]),0)),0) + 1

If you could have more than one user entering information at once, that's
not really adequate either.

But more importantly, you need a different data structure if any of the
conditions above could ever apply.

Hi!

[quoted text clipped - 15 lines]

Thanks!

 

[Allen Browne]

Answers in-line.

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

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

I created a table with a field "Value" which is incremented with an Add
request. Then, when I clic on the command button of my customer-form, the
preview before print of the receipt is show. The receipt is based on a
report,
and this report is based on a request which put together each consultation
date and the fee paid by the customer for a specific period where the
person
didn't get already a receipt.

Still, I need help on 4 problems:

1) When I clic on the command button of the receipt in the customer
consultation form (each customer has a principal form at his name and a
new
consultation form for each consultation), 2 little windows open one after
the
other asking the first name and name of the customer, since I build the
request like that to get the customer name on the receipt. But I would
want
to associate the command button with the customer name which appear on the
principal form near of this button instead of type it in these little
windows.
It will be interesting that the name and first name appear automaticly on
the
receipt preview without to type it.

If you have parameters in your query, there is no way to avoid that. Perhaps
you could design another query to base your report on. This one could
include both your tables (Customer and Consultation), so the names can be
read from the Customer table.

2) The number of the receipt is incremented each time that I clic on the
command button of the receipt of the consultation form which open a
preview
of the receipt. But, I want the number be incremented only when the
receipt
is printed, because sometime, I will only verify the receipt with the
preview,
and close it.

As explained, this approach is inadequate. Not only is it impossible to set
up this report so that it prints differently than it previews, but you are
also missing your original design goal of being able to print a duplicate
receipt (which requires you to track what receipt number goes with what
record.)

3) If the receipt is printed, I need than the checkbox on each
consultation
form be ticked automaticly for each consultation which appear on the print
receipt.

Again, this is impratical. Too many things go wrong with the printing
process (paper jams, toner/ink, power issues, ...) for this to be a reliable
way to design a database.

You could try asking the user if everything printed okay in the Close event
of the report, and if so, executing an UPDATE query statement to update the
yes/no column to Yes for the record(s) in the report, but even that attempt
will be frustrated by a bug in Access where it fails to maintain the
FilterOn property of the report. As a result, there is no way to know which
records were actually in the report reliably (i.e. whether the filter is an
artifact or actually applied.)

4) If the receipt was printed a first time, I need to have to possibility
to
print it again (duplicata) in the future.

You cannot do that without tracing what receipt number goes with what
consultation.

How to do that?

Follow the recommendations in the previous reply.

This idea of just assiging the next available number to get a receipt is
inadequate. You must store what receipt number goes with which
consultation
or you will not be able reprint the same receipt again later.

If you are absolutely certain that:
- no customer will ever make multiple part-payments for a consultation;
- no customer will ever pay 2 consultations at once;
- no customer will ever pre-pay for a consultation that has not occurred
yet;
- no customer will ever pay for someone else (e.g. a parent for a child)
etc, then you could just add another field to your Consultation table,
with
these properties:
- Name ReceiptNum
- Type Number
- Size Long Integer
- Default Value None (Important to clear this property.)

When the customer pays for the consultation, you can then use code in your
form to lookup the next available number, and assign it to the field,
e.g.:
Me.ReceiptNum = Nz(DMax("ReceiptNum", "Consultation", _
"ConsultationID = " & Nz([ConsultationID]),0)),0) + 1

If you could have more than one user entering information at once, that's
not really adequate either.

But more importantly, you need a different data structure if any of the
conditions above could ever apply.