Merging information to create ID field.

[Raga32]

Hi



I'm creating an access database to keep track of clients, to replace a previous db we had in place. The old database created a field which merged the first 4 letters of a clients last name with the last 4 numbers of their ssn to create this unique "ClientID" field. I'm wondering what code I could use to do this automatically when the information is first entered into the form, but instead of using ss# I'll be using the last four digits of their phone number. Field names for my db are LastName and HomePhone, which I want to populate the ClientID field with this unique identifier. Looking at the old database shows the code used was

Private Sub ssn_LostFocus()
Dim ws_err_flag As String
'ws_err_flag = "No"
'If Me!last Is Null Then
' MsgBox "Last name is required!"
' Me!last.SetFocus
' ws_err_flag = "Yes"
'End If
'If Me!ssn Is Null Then
' MsgBox "SSN is required!"
' Me!ssn.SetFocus
' ws_err_flag = "Yes"
'End If
'If ws_err_flag = "Yes" Then
' GoTo end_sub
'End If
Me.ClientID = Left$(Me.last, 4) & Mid$(Me.ssn, 8, 4)
Me.IsClient = True
end_sub:
End Sub

This was an access 2000 db and I'm now using 2010. Any help would be appreciated.

Thanks

[ssanfu]

You could try:

Private Sub Form_BeforeUpdate(Cancel As Integer)

'if clientID exists, don't change
   If Not (Len(Trim(Me.ClientID & "")) > 0) Then  ' 1
   'check if last name is NULL
      If Len(Trim(Me.lastname & "")) > 0 Then  '2
         MsgBox "Last name is required!"
         Me!last.SetFocus
         Exit Sub
      End If    '2
      'check if phone is NULL
      If Len(Trim(Me.homephone & "")) > 0 Then  '3
         MsgBox "Home Phone is required!"
         Me.homephone.SetFocus
         Exit Sub
      End If  '3
      Me.ClientID = Left(Me.lastname, 4) & Right(Me.homephone, 4)
      Me.IsClient = True
   End If  '1
End Sub

This is using the form before update event, so the value won't be generated until the record gets saved.

[pbaldy]

What problem are you having applying the old code? From the sound of it, you simply need to adjust this reference:

Me.ssn

to look at the phone number. Hopefully you realize this is not a good ID, given the possibility of duplication (perhaps not likely, but possible).

[Raga32]

Yes I know it's not the best ID, but we need something that uses basic information that we get with first contact with clients, and ssn is not something you give out over the phone. I'm willing to take the chance of possible duplication so that I can at least get the db up and running soon. Thanks I'll try those things, I'm not well-versed in the code portion of access, up until now I've used macros for buttons and other functions, so I don't always know what I'm looking at.

Thanks again