Creating an AutoNumber Button

[motoxracer400f]

Hi everyone! I've created a form that populates data from my main table. I am trying to create an AutoNumber button that basically finds the last record in my table and then starts a new record following that number. The problem is that the main index column that I am trying to search is not just a number, it's a text field. It consists of 7 characters total (6 numbers and then one letter) ex. 100102A. I want to be able search only the first 6 characters to find the last record which would be 100102 and then autonumber to 100103 and then by default add an "A" to the ending. So my new autonumber should look like this "100103A". How do I get it to autonumber like this when there is a letter at the end? Please help. Thank you so much!

 

[odin1701]

What is the purpose of the letter at the end?

 

[MSAccessRookie]

Maybe you could have two fields in the table One Number and One varchar(1), and combine them in an UNBOUND control field that the user sees. When the field is updated, an Event could be triggered to split the value into two parts and store the two parts into two additional controls that are BOUND to the appropriate fields from the table. If the update is NULL, the Number part could be updated by 1 and the varchar part could revert to "A"

Of course, the Event might have to validate and reject improperly formatted entries as well

 

[missinglinq]

Here's a routine I've used to do this kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.PKField = "000001A"
Else
Me.PKField = Format(DMax("val(Right([MyPK],3))", "YourTableName") + 1, "000000") & "A"
End If
End If
End Sub

Where PKField is the name of the textbox on the form, MyPK is the name of the field in the table holding the auto-incremented ID, and YourTableName is the actual name of your table.