Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    15

    Question Unanswered: VBA Assistance Needed - Generating custom sequence numbers

    Hello Everyone

    I was wondering if someone could give me some assistance here.
    I found this code here on the forums and modified it a bit to generate a specific result format: 120829-090
    Ex. two digit year, two digit month, two digit day, -, PO number 09, and finally individual PO number (maximum is 9, including 0. So 10 records total a day.)

    The sequence generated will be 090 through 099. However, when I reach 099 and add a new record it increments the next number to 0910.

    I need the sequence to end at 099 with an if statement that states if when creating a new record, if number 120829-099 exists then undo new record, and display an error msg box.

    Can someone help point me in the right direction?

    Thanks a ton in advance!


    Private Sub Form_Current()

    Dim strPONumber As String

    If Me.NewRecord Then
    strPONumber = Nz(DMax("PurchaseOrderNumber", "tblPONumber", "Left(PurchaseOrderNumber, 6) = '" & Format(Date, "yymmdd") & "'"), "")
    If strPONumber = vbNullString Then 'No numbers for this year/month
    strPONumber = Format(Date, "yymmdd-090")
    Else
    strPONumber = Left(strPONumber, 9) & Format(Right(strPONumber, 1) + 1, "0")
    End If
    Me.txtPONumber = strPONumber
    End If

    End Sub

  2. #2
    Join Date
    Oct 2006
    Posts
    110
    If you are strictly looking for it not to be more than 99, you could use this:

    Dim strPONumber As String

    If Me.NewRecord Then
    strPONumber = Nz(DMax("PurchaseOrderNumber", "tblPONumber", "Left(PurchaseOrderNumber, 6) = '" & Format(Date, "yymmdd") & "'"), "")
    If strPONumber = vbNullString Then 'No numbers for this year/month
    strPONumber = Format(Date, "yymmdd-090")
    Else
    strPONumber = Left(strPONumber, 9) & Format(Right(strPONumber, 1) + 1, "0")
    End If

    If Right(strPONumber, 3) > 99 Then
    MsgBox "Error"
    DoCmd****nCommand acCmdUndo 'You may want to double check this part I am not familiar with it.
    else
    Me.txtPONumber = strPONumber
    End If
    End If

    End Sub

  3. #3
    Join Date
    Aug 2012
    Posts
    15
    Awesome, that did it.

    Instead of Docmd.unCommand AcCmdUndo, I used Me.Undo.

    Thanks a bunch!!

    Quote Originally Posted by cmays637 View Post
    If you are strictly looking for it not to be more than 99, you could use this:

    Dim strPONumber As String

    If Me.NewRecord Then
    strPONumber = Nz(DMax("PurchaseOrderNumber", "tblPONumber", "Left(PurchaseOrderNumber, 6) = '" & Format(Date, "yymmdd") & "'"), "")
    If strPONumber = vbNullString Then 'No numbers for this year/month
    strPONumber = Format(Date, "yymmdd-090")
    Else
    strPONumber = Left(strPONumber, 9) & Format(Right(strPONumber, 1) + 1, "0")
    End If

    If Right(strPONumber, 3) > 99 Then
    MsgBox "Error"
    DoCmd****nCommand acCmdUndo 'You may want to double check this part I am not familiar with it.
    else
    Me.txtPONumber = strPONumber
    End If
    End If

    End Sub

Tags for this Thread

Posting Permissions

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