How to increment a number on a form in Access 2010

LillyC
LillyC used Ask the Experts™
on
Hi

Could anyone advice the best way I could increment a number on a form, so that I can use this unique number as a job number.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Create a unique index on your job number column in the table definition. Use Nz(DMax("JobNumber", "JobsTable"), 0) + 1 to calculate the job number. Assign the value in the forms before update event:

 
Private Sub Form_BeforeUpdate(Cancel As Integer)

  If Me.NewRecord Then
    Me![JobNumber] = Nz(DMax("JobNumber", "JobsTable"), 0) + 1
  End If
  
End Sub

Open in new window

LillyCDatabase Developer

Author

Commented:
Thank you :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial