Dear Sally,

I have been through a similar experience recently. I found it easier to handle using VBA code but not a querry. If you would like the number to come automatically, then you would probably use the Dmax function and add 1 to each new record.

However, if you would like the user will type in the last part of the string, below is what worked for me. (i will use your example)

First, i expect that the user will not enter the "TXO-061510-" part of the string. They will only enter the last four digits of the string. This creates five possible scenarios
1. The user might enter say 5 in this field
2. The user might enter say 05 in this field
3. The user might enter say 005 in this field
4. The user might enter say 0005 in this field
5. The user might enter the whole or part length string


Therefore, in the after update event, i would evaluate the length of what the user had entered and then i concatenate the "TXO-061510-" part of the string

Below is a possible code assumming your field name is called SerialNumber:

Private Sub SerialNumber_AfterUpdate()


    If Len(SerialNumber) = 1 Then 'The user might enter one digit
    SerialNumber = "TXO-" & Format(Date, "mmddyy") & "-000" & SerialNumber
    Else
    If Len(SerialNumber) = 2 Then 'The user might enter two digits
    SerialNumber = "TXO-" & Format(Date, "mmddyy") & "-00" & SerialNumber
    else
    If Len(SerialNumber) = 3 Then 'The user might enter three digits
    SerialNumber = "TXO-" & Format(Date, "mmddyy") & "-0" & SerialNumber
    else
    If Len(SerialNumber) = 4 Then 'The user might enter four digits
    SerialNumber = "TXO-" & Format(Date, "mmddyy") & "-" & SerialNumber

    Else 'The user might enter the whole string. The minimum length is 12
    If Len(SerialNumber) >= 12 And SerialNumber Like "TXO-" & Format(Date, "mmddyy") & "-" Then
    SerialNumber = SerialNumber
    End If
    End If
    End If
    End If
    End if


End Sub

This is not over though.
1. What if the user entered a string between 5 and 11 characters long or more than 15 (maximum possible)?
You would then use the BeforeUpdate event to reject the entry

Private Sub SerialNumber_BeforeUpdate()
    If Len(SerialNumber) >= 5 And Len(SerialNumber) <= 11 Then 'Reject the entry
    msgbox "The Serial Number you have entered is invalid. Please enter a four digit number"
    Cancel = true
    End if

    If Len(SerialNumber) >15 Then 'The user might enter more than maximum number of 15 digits. Reject the entry
    msgbox "The Serial Number you have entered is invalid. Please enter a four digit number"
    Cancel = true
    End if
End Sub

2. To completely lock the number that has been entered in the field, you would need the On Got Focus event (or on current event if you are using a single form). Your code would look like this
   Private Sub SerialNumber_GotFocus()
    'After a valid number has been entered, block any possibility to edit the field
    If Not IsNull(SerialNumber) Then
    SerialNumber.Locked = True
    ElseIf IsNull(SerialNumber) Then
    SerialNumber.Locked = False
    End If
    
End Sub


3. What if the user entered the whole string but the first 11 characters are not in your desired format? What if the first four characters are Text?
I leave this to Richard Rost to answer

I hope my answer helps you out

MICAH