Auto generate client number for each new client

[lexygirl]

I have a client form which i enter client details on. My primary key is client-id, I have another field which i have called client number, this field gets auto generated each time a client record is saved and it’s this field that i want to view on my form not the client-id (PK).

I have a table called tblnextnum with field nextnum which generates the next number and gets placed in the client number field.

I have a command button on my form “SAVE CLIENT” with the following code behind it

Option Compare Database

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

DoCmd.GoToRecord , , acNewRec
Dim NextNo As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT NextNum FROM tblnextnum")
NextNo = rs!nextnum + 1
'now update the table
rs.Edit
rs!nextnum = NextNo
rs.Update
rs.Close
Set rs = Nothing

Me.client_number = NextNo

Exit_Command22_Click:
Exit Sub

Err_Command22_Click:
MsgBox Err.Description


Resume Exit_Command22_Click

End Sub


When I hit the save client button my current client record does not have a client number allocated to it, instead a blank new record appears and that record has the next client number which would have been 8 in the field with all the other fields blank....

Where should i put this code so that the current record has the next sequential client number allocated to it and it shows up on the form with all the other details i have added in.

Ideally I would love for the client number to be say 000001, as I need to auto generate invoice number and gift voucher number and would love for it to have leading zeros

Many thanks for your help

[RuralGuy]

The new record you see is because that is what the 2nd line (DoCmd.GoToRecord , , acNewRec) is doing. You should add in this number in the BeforeUpdate event of the form. You can *always* display 1 as 000001 by simply using the Format() function.