Automatic Row Numbering Without Using AutoNumber

  • Thread starter Thread starter pushrodengine via AccessMonster.com
  • Start date Start date
P

pushrodengine via AccessMonster.com

I need help to automatically number a field sequentially without using
AutoNumber. The format of the numbering is four digits “0000â€. There is
currently no records entered into the table.

Thank You
 
hi,
I need help to automatically number a field sequentially without using
AutoNumber.
Click to expand...
Use Nz(DMax(),0)+1.
The format of the numbering is four digits “0000â€.
Click to expand...
This is is just a small check as your number may not be larger then
9999. Formatting is done when you display your number on forms or reports.


mfG
--> stefan <--
 
Stefan,

Sorry, I'm fairly new to MS Access, but where exactly do I enter this: Use
Nz(DMax(),0)+1 ?

Thank you
 
Sorry, I'm fairly new to MS Access, but where exactly do I enter this: Use
Nz(DMax(),0)+1 ?
Click to expand...

Do all your data entry using a Form (not the table datasheet), and put this
code (adapted to your actual table, there needs to be something in the ()
after Dmax) in the form's BeforeInsert event.

John W. Vinson [MVP]
 
So, in the form design view I hightlight the textbox, right click, properties,
when what?

Thank you Everyone for the Help!!
 
So, in the form design view I hightlight the textbox, right click, properties,
when what?
Click to expand...

Actually, don't deal with the textbox at all. Instead, click the little square
at the upper left intersection of the rulers to select the Form's properties.
On the Events tab find the BeforeInsert event. Click the ... icon by it and
choose "Code Builder". Access will give you the Sub and End Sub lines; just
add one more -

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtID = NZ(DMax("[ID]", "[tablename]")) + 1
End Sub

For txtID use the name of a control on the form bound to the field you want
incremented; for ID use that fieldname; for tablename use the actual name of
your table.

Select Debug... Compile <my database name> from the menu; post back if it
gives you an error. Otherwise you should be good to go.

John W. Vinson [MVP]
 
Back
Top