Sequential Report Numbers for Access 2003

[mjones]

Hi All,
Reading old posts tells me not to use auto numbers. If I do, there’s
a high probability of skipped numbers.

There are so many posts for different Access versions that a summary
of the best method would be great.

Given a table, a form and a report (e.g. tReceipt, fReceipt,
rReceipt), how would you describe the best way to sequence receipt
numbers?

Much appreciated,

Michele

 

[John W. Vinson]

Hi All,
Reading old posts tells me not to use auto numbers. If I do, there’s
a high probability of skipped numbers.

There are so many posts for different Access versions that a summary
of the best method would be great.

Given a table, a form and a report (e.g. tReceipt, fReceipt,
rReceipt), how would you describe the best way to sequence receipt
numbers?

Much appreciated,

Michele

If you want to permanently store the receipt number (as I'd expect you would,
in order to look it up when you get a question from the customer), use a Long
Integer field ReceiptNo in tReceipt. You can make it the Primary Key if you
wish.

In fReceipt's BeforeUpdate event put code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<do any validity checking first, and Cancel the update if it fails>
Me!ReceiptNo = NZ(DMax("[ReceiptNo]", "tReceipt")) + 1
End Sub
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mjones]

Hi All,
Reading old posts tells me not to use auto numbers.  If I do, there s
a high probability of skipped numbers.

There are so many posts for different Access versions that a summary
of the best method would be great.

Given a table, a form and a report (e.g. tReceipt, fReceipt,
rReceipt), how would you describe the best way to sequence receipt
numbers?

Much appreciated,

Michele

If you want to permanently store the receipt number (as I'd expect you would,
in order to look it up when you get a question from the customer), use a Long
Integer field ReceiptNo in tReceipt. You can make it the Primary Key if you
wish.

In fReceipt's BeforeUpdate event put code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<do any validity checking first, and Cancel the update if it fails>
Me!ReceiptNo = NZ(DMax("[ReceiptNo]", "tReceipt")) + 1
End Sub
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Brilliant. You're a God. Thank you soooo much.