Okay, first off, just want to say thanks for the assist. Here is what I need to figure out

The way I have this setup is the user inputs a number into a control on a form, the control is called LotNumber (its a text box) and it is a separate column in the table bound to the form. But I don't know how to actually take this number and place it in code so it can be used to create the first 6 digits of another number I call KESerialNumber. I need to add an auto incrementing number to the end of this 6 digit number to make it a 9 digit number. Here is the process we would like to happen in plain English, because I do not know how to code it :/ ---> Step 1. User types in a LotNumber in its own control (text box) in a form Step 2. Access Takes that LotNumber and combines it with an autoincrementing sequence number starting at 001 and going up by one for each new record. Step 3. The sequence number resets to 001 once a new LotNumber is entered OR goes to the highest LotNumber and KESerialNumber avail in case previous records were entered using an older or previous LotNumber. The user needs to be able to go back and enter records based on previous LotNumber(s). So if the the LotNumber is 042313 and the auto incrementing number is 030 (which would make the KESerialNumber 042313030) because 30 records were created using LotNumbrer 042313 then if a new lot number is entered, say 042413, the incrementing number starts at 001, but if the old lot number is entered then the next incrementing number would be 031, which would make the KESerialNumber 042313031). I have fields in the bound table for LotNumber and KESerialNumber, I do not need to store the autoincrementing (i.e. 001,002,003 etc.) number.

I have put together some code that uses the date to create the first 6 digits, but I need the first 6 digits to be derived from the user input typed into the form. I don't know if it would be easier to modify this code or start from scratch, but this code works great if the date is used to generate the first 6 digits. here's the code

Public Function Autonum(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
  dv = Format(Now(), "mmddyy") * 1000 + 1
  Autonum = dv
  Exit Function
End If

'format the number as an 9 digit number
dv = Format(dmval, "000000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 6)
'get today's date
dt2 = Format(Now(), "mmddyy")
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
  Seq = Seq + 1 'increment the sequence number
  'add the sequence number to the date and return
  Autonum = Format(Val(dt1) * 1000 + Seq)
  Exit Function
Else 'the dates are different
  'take today's date and start the sequence with 1
  Autonum = Format(Val(dt2) * 1000 + 1)
End If

End Function
What do you think?