Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Unanswered: Some help saving a form - MS Access 2010

    Hey Guys,

    I have an access database that is storing enquiries for a Higher Education system. For the customer ID we can't use autonumber, but have to use a more complex form.

    Instead of 1,2,3,4 we need to use the first initial, last initial and a number. For example John Blog, James Bridge and Heather Harrison would go in as: JB1, JB2, and HH1

    How would I create a form that can generate that automatically? I just want the user to be able to enter the first and last name and a few details, them to click save and it to come up with "This customer has been saved, there ID is: HH1 (for example)"

    Thank you,
    Daniel

  2. #2
    Join Date
    Feb 2012
    Posts
    13

    Vba

    I think you will have to figure out an algorithm, (shouldn't be that hard) and code it in VBA.

    But then again, I'm not the expert

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This should do the trick:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.NewRecord Then
    
     If DCount("*", "YourTableName", "Left([IDNumber],2) = '" & Left(Me.FirstName, 1) & Left(Me.LastName, 1) & "'") = 1 Then
      Me.IDNumber = Left(Me.FirstName, 1) & Left(Me.LastName, 1) & (DMax("val(Mid([IDNumber],3))", "YourTableName", "Left([IDNumber],2) = '" & Left(Me.FirstName, 1) & Left(Me.LastName, 1) & "'") + 1)
     Else
       Me.IDNumber = Left(Me.FirstName, 1) & Left(Me.LastName, 1) & "1"
     End If
    
    End If
    
    MsgBox "This Customer has been Saved. Their ID is: " & Me.IDNumber
    
    End Sub
    You'll have to carefully replace IDNumber, FirstName, LastName and YourTableName with the actual names of your fields/table.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •