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