Command Button - Add New Record +1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone tell me what is wrong with this code? I followed the
instructions from an Access source. Basically, I want a command button that
when clicked will fill in the file ReceiptNum with the next sequential
number.

Private Sub Add_Receipt_Click()

Me.ReceiptNum = "REC-" & Right(DMax("ReceiptNum", "tblReceipt"), _
Len(DMax("ReceiptNum", "tblReceipt")) - _
InStr(1, DMax("ReceiptNum", "tblReceipt"))) + 1

End Sub
 
You would be better off if you did not carry "REC-" in your table for every
record. It wastes space and time and makes manuipulating the number require
more code. If you have to present it to users with the REC-, then do it at
that time. Also, this should be a Numeric Long Integer field rather than a
text field. Make these changes, then creating the next receipt number
becomes easy.

Private Sub Add_Receipt_Click()

Me.ReceiptNum = Nz(DMax("ReceiptNum", "tblReceipt"), 0) + 1

End Sub

So when you need to present it to a user:
Format(ReceiptNum, "\R\E\C\-00000")

Number of zeros depends on how long you want the number to be.
 
Sharon said:
Can someone tell me what is wrong with this code? I followed the
instructions from an Access source. Basically, I want a command button
that
when clicked will fill in the file ReceiptNum with the next sequential
number.

Private Sub Add_Receipt_Click()

Me.ReceiptNum = "REC-" & Right(DMax("ReceiptNum", "tblReceipt"), _
Len(DMax("ReceiptNum", "tblReceipt")) - _
InStr(1, DMax("ReceiptNum", "tblReceipt"))) + 1

End Sub
Click to expand...

Hi Sharon, you're making this way too complex! Assuming your form has a
text box bound to the "ReceiptNum" field, try this as your text box default
value:

Nz(DMax("ReceiptNum", "tblReceipt")) + 1

That way, whatever method you use to generate a new record you'll always get
the next available number.

HTH - Keith.
www.keithwilby.com
 
Back
Top