Hello all,
Urgent please help. I have an MS Access table, "tbl_Final_Log" and my PK, "FinalLotNum" is a custom Unique Identifier Number (UID) which format like: 63MYDDNN (for example: 63FD2601, 63FD2602…, 63FD2699)

It must start with 63 then
the Month (A=1 for January, F=6 for June ...)
then Y for Year (A=1 for 2001, D=4 in 2004 ...)
then DD for Today Date, (like 26th day of the month, and
NN is the sequence number of the entry. The NN can only go from 01 to 99.

I need to create a command bottom to find the MaxID in the field “FinalLotNum” and
add 1 to it when the user click on the "cmdNextLotNum". The output will go in the "txtFinalLotNum" text box.
If the "txtFinalLotNum" text box is not null then some error message and do not thing else FinalLotNum = MaxID + 1.

Urgent please help.
Thank you

Vandat

Hi Vandat,

If I understand you correctly, you're looking for a way to get your next MAX sequence number (1 – 90) appended to the rest of your UID string. Hope that’s in the ballpark.

If so, I’d suggest first creating a table with a single field to store the current MAX value.

Table: tblMaxValue
-------------------
    Field: MaxValue
    DataType: Number
    Default Value: 0

Then here’s a function you can call to increment the MaxValue field, and return the new MaxValue.

Function GetNextMaxValue() As Integer
    On Error GoTo ErrorHandler

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim intNextMaxValue As Integer

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open "tblMaxValue", cnn, adOpenKeyset, adLockPessimistic

    ' increment current max value by 1
    intNextMaxValue = rst("MaxValue") + 1

    ' validate new max value range
    If intNextMaxValue <= 90 Then
        rst("MaxValue") = intNextMaxValue
    Else
        rst("MaxValue") = 0
    End If
    rst.Update

    ' return new max value
    GetNextMaxValue = rst("MaxValue")

     ' Clean up
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Function

ErrorHandler:
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
    End If
    Set rst = Nothing

    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then cnn.Close
    End If
    Set cnn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If

End Function

I then placed a command button and a textbox on a from, and here’s the event procedure:

Private Sub cmdGetNextMaxValue_Click()
    Me.txtNextMaxValue.SetFocus
    Me.txtNextMaxValue.Text = CStr(GetNextMaxValue)
End Sub

Hope that gets you a little further along. That’s a pretty wicked UID you’re working with.

HTH,

Bob

Hey Bob, any reason you chose to ignore the Domain Aggregate function DMax?
Couldn't it be done something like:
=DMax("Right(FinalLotNum,2)","tbl_Final_Log","")+1



I am a loud man with a very large hat. This means I am in charge

Hi Steven,

Its because the number sequence has to start over again at 1 once it reaches 99. Take the sequence:

63FD2697
63FD2698
63FD2699
63FD2601
63FD2602

The next sequence number you need is 03, but DMax will return 100 because 99 already exists in the database.

Hope that's right?

Also worth is mentioning that this UID format is only good for 99 UIDs per day before you start getting duplicates.

Bob

Hi Bob and Steven,

I just get back from work. I have try Bob method, but I could not get the function to known what is my MaxID from the field name "FinalLotNum"; this field is also a PK and index key.
I will never run into the problem of having the function restart after it reach 63FD2699 and go back to 63FD2601, because we could never reaches to 99 in one day anyway. If that case would every come true, then we will push the work to the next day base on priority like 63FD2701 instead.

Thank you for reply
Vandat

Hi Vandat,

Just as a point of clarification, the method doesn't attempt to get your MaxID from the FinalLotNum field. It simply generates the the next, sequential final two digits of your UID.

If you have a UID like 63FD2601, the method generates the 01 portion and stores is in a variable. You would then have to concatenate the 01 prtion to the 63FD26 portion. The 63FD26 portion would have to be generated by another method, probably involving the DatePart function to extract your day, month, and year values, then some logic to associate the date parts with their respective letters.

Once the 63FD26 portion is concatenated to the 01 portion, then the complete UID string can be written to your FinalLotNum field.

Least thats my best guess at what you're trying to do.

HTH,

Bob