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