Automatic Row Numbering Without Using AutoNumber

[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

 

[Stefan Hoffmann]

hi,

I need help to automatically number a field sequentially without using
AutoNumber.

Use Nz(DMax(),0)+1.

The format of the numbering is four digits “0000â€.

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 <--

 

[pushrodengine via AccessMonster.com]

Stefan,

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

Thank you

 

[John W. Vinson]

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

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]

 

[pushrodengine via AccessMonster.com]

So, in the form design view I hightlight the textbox, right click, properties,
when what?

Thank you Everyone for the Help!!

 

[John W. Vinson]

So, in the form design view I hightlight the textbox, right click, properties,
when what?

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]