Problem Generating Key

[Wes]

I am having a strange problem trying to create a key for my table. The
key will be in the form NN-NNNN

Here is my code

Private Sub AssociateID_GotFocus()
Dim strMax, strNewKey As String
Dim intNewKey As Integer
Dim strTestKey As String

strMax = Nz(DMax("AssociateId", "SalesAssociate"), 0) ' get the highest key

' Create new Key
intNewKey = Int(Mid(strMax, 3, 4)) + 1 ' pick up the last 4 bytes
and increment
strNewKey = Str(intNewKey) ' cast to string

DoCmd.GoToRecord , , acLast ' go to the last row

strTestKey = Mid(strMax, 1, 2) & strNewKey ' check the key debug only

Forms!NewEmployee.AssociateID = Mid(strMax, 1, 2) & strNewKey ' move to form

End Sub


The format used in the field definition is @@-@@@@


Here are the Debug Watches

strMax "101002"

IntNewkey 1003

StrNewKey " 1003" Notice the leading space

strTestKey "10 1003"

As seen in the form 10 1003

as seen in the table

AssociateID
10 -1003

Of course, the sort is now wrong. What is actually stored appears to be
10 1003

The other keys, manually entered, are correct 101001, 101002

I obviously have something wrong in the way I am trying to do this, but
about 20 tests have failed to show me the error of my ways.

Help!!!


Thanks

Wes

 

[J_Goddard via AccessMonster.com]

Hi -

Your statement to create the new key is wrong - it should be
intNewKey = Int(Mid(strMax, 4, 4)) + 1
to start at the 4th character of the string.

John