Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    31

    Unanswered: How to make a tracking number

    Anyone can help how to make a tracking number using vba codes..

    like for example..

    the first nmuber is 00000001
    the second is 00000002
    and so on...

    plssss..

    badly needed..
    thanx in advanced

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Replace TrackingNumber in the code with the actual name of your Control/Field and AI2 with the name of your Table or Query, and define the Field as a Text Datatype.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.NewRecord Then
     If RecordsetClone.RecordCount = 0 Then
      Me.TrackingNumber = "00000001"
     Else
      Me.TrackingNumber = Format(DMax("Val([MyPK])", "AI2") + 1, "00000000")
      End If
    End If
    
    End Sub

    Linq ;0)>
    Last edited by Missinglinq; 12-28-12 at 10:16.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2012
    Posts
    31

    Follow-up

    Great reply ling
    Another one is

    I need to display during onload the added number in the txtbox..
    For example my last tracking number is 0000002...

    I need to display the 0000003 in the textbox during onload and that 0000003 will be save in my db when i click the save button..Could it be..?

    thant 0000003 comes from 0000002 which is added by 1...
    thanx ling...

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If this is a stand alone database, i.e. there will only be one person entering the data on a single PC, you can move the above code to the Form_Current event instead of using the Form_BeforeUpdate event.

    If, however, you're going to have multiple users on multiple machines, you're stuck with having the tacking number assigned just before the Record is saved; otherwise two or more users could start a new Record, be assigned the same number , and then save multiple Records with this same tracking number!

    I've used this method in the Form_BeforeUpdate for years, in multi-user environments, without ever having a duplicate generated.

    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
  •