Chronological Order Number (Access 2000)

[79schultz]

<img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> I'm new to Access and trying to do some programming. Can anyone tell me how to make an expression that will update an order number chronologically with each order? Should I use AutoNumber or can I build an expression? By what I've read, I'm not sure I want AutoNumber. Like I said, I'm new so I don't understand much about it.

[Francois]

Don't use an autonumber. You will have gaps in the count if you enter a new record and change your mind and don't save the record.
In the before insert of the form use the following code :<pre>Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(DMax("YourNumberField", "YourTable")) Then
Me.YourNumberTextBox = 1
Else
Me.YourNumberTextBox = DMax("YourNumberField", "YourTable") + 1
End If
End Sub</pre>

[Francois]

Your email :

<hr>Thanks for your advice. I am really new at Access. I can find a 'Before Update' field but not a 'Before Insert' field. When I type the code into VB, it highlights the word 'then' and gives me a message Expected: end of statement. I am also new to the Lounge and couldn't figure out how to add a thread to my original message.
<hr>

1- To answer to a post in the lounge, just clik on the icon marked 1 in the picture.
2- It is the Berore insert of the form you have to use. To select the form, click on the square in the circle marked 2 on the picture. Then select the before insert event.
3- For the "then" error I suspect a typo error. Check the spell or use copy and past to enter the code.
Sorry for the messy picture, but I want to use one picture for the two applications

[79schultz]

Merci beaucoup!