I need assistance to create a function to do the following please: Besides the 'autonumber' in my table I have field named MemberNum. Also FirstName & LastName. I need to have the MemberNum updated after the LastName has been updated. (see After Update Procedure)

I would like to have the MemberNum look like this: The LastName is Eastwood. Result in MemberNumber = "CEAS0001"

My effort:
Private Sub Surname_AfterUpdate()
Me.MemberNumber = "C" & Left(Me![LastName], 3) & [memID] + 1 'memID is the autonumber
End Sub

What can I do to make the end result look like this: CEAS0001 I'm certain I miss inserting a Format() here for the number result, but I'm not entirely certain how to phrase it. Requiring the leading 3 zero's as part of the number which when it increments will eventually be replaced looking like 0010 or 0102

I would really appreceate any assistance.

Thank you.

jjstccean.