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")
strPONumber = Left(strPONumber, 9) & Format(Right(strPONumber, 1) + 1, "0")
Me.txtPONumber = strPONumber