Creating a unique booking number

[MattCollins]

I have a booking table with Bookingno (Text) and various other fields
What i was hoping to do was create a unique Bookingno using part of the clients name and however many records there are for that client in my bookings table.

At the bottom of my booking form i have a button with this code:
Private Sub Continuebtn_Click()
On Error GoTo Err_Continuebtn_Click

Dim response As Integer

response = MsgBox("Are these details correct?" & (Chr(13)) & "Sample date: " & Form_frmbooking.Sampledate & (Chr(13)) & "Sample reference: " & Form_frmbooking.Sampleref & (Chr(13)) & "Sample place: " & Form_frmbooking.Recieveddate & (Chr(13)) & "Recieved by: " & Form_frmbooking.Recievedby & (Chr(13)), vbOKCancel, "Confirm details")

If response = 1 Then
Form_frmbooking.Bookingno = Left(Form_frmClient.Clientname, 3) & "/00" & Form_frmbooking.Recordset.AbsolutePosition
DoCmd.GoToRecord , , acNewRec
Else
End If

Exit_Continuebtn_Click:
Exit Sub

Err_Continuebtn_Click:
MsgBox Err.Description
Resume Exit_Continuebtn_Click

End Sub


When i open this form i get an error saying Primary key cannot contain a null value and my code returns a booking number of "con/00-1" for my first record and then stops working completely after that. My form has data entry set to true.

Any ideas?

 

[MattCollins]

Possible solution?

The way i'm thinking of doing this is to assign a booking number when the booking form is opened using the BOF statement so that if there are no records in the recordset assign xxx/00/01 to it. But if there are records then i need to return the last booking number with xxx at the front.

Anyone know how i can do this?