Generate Primary ID based on criteria

[beemomo]

Hi,

I have a table name ExpenseClaim where it has a field named RefNo to store the Primary ID for each record.

In my form, I has a combo box named cboExpenseCode: -
1) If user select "Company Reimbursed", then the prefix for the Primary ID is P-xxx
2) If user select "Project Reimbursed", then the prefix for the Primary ID is OF-xxx

xxx- represent the incremental id (e.g OF-001 , OF-002 / P-001, P-002) for the different expense code selected.

My code goes here :

Private Sub cboExpenseCode_AfterUpdate()
    If Me.NewRecord And Me.cboExpenseCode.Value = "Project Reimbursed" Then
        Dim NextPRefNo As Long
        NextPRefNo = Nz(DMax("Right ([RefNo],3)", "ProjectExpenses"), 0) + 1
        Me![RefNo] = "P-" & Format(CStr(NextPRefNo), "000")
    ElseIf Me.NewRecord And Me.cboExpenseCode.Value = "Company Reimbursed" Then
        Dim NextOFRefNo As Long
        NextOFRefNo = Nz(DMax("Right ([RefNo],3)", "ProjectExpenses"), 0) + 1
        Me![RefNo] = "OF-" & Format(CStr(NextOFRefNo), "000")
    End If
End Sub
 

But I get a problem here, the if let say the table has OF-001, the first ID value for Project Reimbursed will start with P-002. I want it to begin with P-001.
The DMax will only recognize the maximum value for the number. Can anyone please guide me how to get the output that I want? This should be easy, but I just cannot figure out the solution :(

Thank you very much.

[beemomo]

I found the solution at last. I created two queries (MaxPRefNo & MaxOFRefNo) to store the Max value for the Primary ID starting with prefix -> OF- and P-.
Next, I edit to the DMax function as below:

For PRefNo:

NextPRefNo = Nz(DMax("Right ([MaxOfID],3)", "MaxPRefNo"), 0) + 1
 

For OFRefNo:

NextOFRefNo = Nz(DMax("Right ([MaxOfID],3)", "MaxOFRefNo"), 0) + 1
 

[NeoPa]

This has never been an easy question (You're not the first to ask it).

There is a solution however, which involves using WHERE criteria in the DMax() function :

Private Sub cboExpenseCode_AfterUpdate()
    Dim strExpenseCode As string, strWhere As String
    Dim lngNextRef As Long
 
    If Me.NewRecord Then
        strExpenseCode = ""
        Select Case Me.cboExpenseCode
        Case "Project Reimbursed"
            strExpenseCode = "P-"
        Case "Company Reimbursed"
            strExpenseCode = "OF-"
        Case '... Other possibilities easily added
        End Select
        If strExpenseCode > "" Then
            strWhere = "[RefNo] Like '" & strExpenseCode & "*'"
            lngNextRef = Val(Right(Nz(DMax("[RefNo]", _
                                           "ProjectExpenses", _
                                           strWhere), "000"), 3)) + 1
            Me.RefNo = strExpenseCode & Format(lngNextRef, "000")
        End If
    End If
    ...
End Sub

[NeoPa]

NB. This concept doesn't work well in a multi-user environment, as it's possible for another user to get exactly the same value if they select the expense code after someone else does, but before they have saved it away. You may need to consider this.

A solution is to determine the value only at the time of saving the new record. This seems strange but is actually more correct than showing a value which is currently not valid. Until the record has already been saved, the value shown is only a best guess - a logical estimate of what it's likely to end up being. It can only be known after the record's saved, so it should only be shown for an existing record.

[ADezii]

@beemomo
It could actually be done with no Queries at all:

Dim strLastP As String
Dim strLastOF As String
Dim strNextP As String
Dim strNextOF As String
 
strLastP = DLast("[RefNo]", "ProjectExpenses", "[RefNo] Like 'P*'")
strLastOF = DLast("[RefNo]", "ProjectExpenses", "[RefNo] Like 'OF*'")
 
strNextP = Left$(strLastP, 2) & Format$(Val(Right$(strLastP, 3)) + 1, "000")
strNextOF = Left$(strLastOF, 3) & Format$(Val(Right$(strLastOF, 3)) + 1, "000")

[NeoPa]

While DLast() may work in a number of circumstances, it should not be relied upon to produce the highest number. At best it relies on the data being entered in a logical order. I would recommend using DMax().

[ADezii]

@NeoPa
Wouldn't the Last Record be the highest ID Value (P-*/OF-*) assuming the logic is sound? If the data is not being entered in a logical order, the entries are essentially useless, correct?

[NeoPa]

Not at all. That assumes things which are not supportable.

It assumes that :

• All the data was entered via this form after it's working perfectly.
• You know the order the records are stored in the table.
• No records are added after any previous ones are deleted and the space re-used.
• There is no point where the data is moved about by processes you neither know about nor control (EG. Export to another database; do some work; import it back in unspecified, or even different order).

Mainly though, it's just the wrong function. You're not after the Last assuming it's the Max - You're after the Max.

Sorry to sound critical ADezii. I'm simply answering the question as clearly as I can. I certainly don't see it as an ambiguous point. You can often get away with DLast() (for reasons you understand well), but it's not advisable for the reasons stated.

[NeoPa]

I was also hoping to draw the OP's attention to the style of the code in my post.

It's clear from the code which scenario is covered at any point.
It doesn't repeat code unnecessarily.
It's flexible and easily extensible.

This is not to advertise how wonderful my code is, but hopefully to get the OP to try it out and see how few extra problems occur later due to the tidier code.

[beemomo]

Thanks to everyone for your clear explanations!
I am learning new things again. :)