By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Generate Primary ID based on criteria

P: 50

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 :

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboExpenseCode_AfterUpdate()
  2.     If Me.NewRecord And Me.cboExpenseCode.Value = "Project Reimbursed" Then
  3.         Dim NextPRefNo As Long
  4.         NextPRefNo = Nz(DMax("Right ([RefNo],3)", "ProjectExpenses"), 0) + 1
  5.         Me![RefNo] = "P-" & Format(CStr(NextPRefNo), "000")
  6.     ElseIf Me.NewRecord And Me.cboExpenseCode.Value = "Company Reimbursed" Then
  7.         Dim NextOFRefNo As Long
  8.         NextOFRefNo = Nz(DMax("Right ([RefNo],3)", "ProjectExpenses"), 0) + 1
  9.         Me![RefNo] = "OF-" & Format(CStr(NextOFRefNo), "000")
  10.     End If
  11. 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.
Feb 18 '09 #1
Share this Question
Share on Google+
9 Replies

P: 50
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:
Expand|Select|Wrap|Line Numbers
  1. NextPRefNo = Nz(DMax("Right ([MaxOfID],3)", "MaxPRefNo"), 0) + 1
For OFRefNo:
Expand|Select|Wrap|Line Numbers
  1. NextOFRefNo = Nz(DMax("Right ([MaxOfID],3)", "MaxOFRefNo"), 0) + 1
Feb 18 '09 #2

Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboExpenseCode_AfterUpdate()
  2.     Dim strExpenseCode As string, strWhere As String
  3.     Dim lngNextRef As Long
  5.     If Me.NewRecord Then
  6.         strExpenseCode = ""
  7.         Select Case Me.cboExpenseCode
  8.         Case "Project Reimbursed"
  9.             strExpenseCode = "P-"
  10.         Case "Company Reimbursed"
  11.             strExpenseCode = "OF-"
  12.         Case '... Other possibilities easily added
  13.         End Select
  14.         If strExpenseCode > "" Then
  15.             strWhere = "[RefNo] Like '" & strExpenseCode & "*'"
  16.             lngNextRef = Val(Right(Nz(DMax("[RefNo]", _
  17.                                            "ProjectExpenses", _
  18.                                            strWhere), "000"), 3)) + 1
  19.             Me.RefNo = strExpenseCode & Format(lngNextRef, "000")
  20.         End If
  21.     End If
  22.     ...
  23. End Sub
Feb 18 '09 #3

Expert Mod 15k+
P: 31,418
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.
Feb 18 '09 #4

Expert 5K+
P: 8,623
It could actually be done with no Queries at all:
Expand|Select|Wrap|Line Numbers
  1. Dim strLastP As String
  2. Dim strLastOF As String
  3. Dim strNextP As String
  4. Dim strNextOF As String
  6. strLastP = DLast("[RefNo]", "ProjectExpenses", "[RefNo] Like 'P*'")
  7. strLastOF = DLast("[RefNo]", "ProjectExpenses", "[RefNo] Like 'OF*'")
  9. strNextP = Left$(strLastP, 2) & Format$(Val(Right$(strLastP, 3)) + 1, "000")
  10. strNextOF = Left$(strLastOF, 3) & Format$(Val(Right$(strLastOF, 3)) + 1, "000")
Feb 18 '09 #5

Expert Mod 15k+
P: 31,418
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().
Feb 18 '09 #6

Expert 5K+
P: 8,623
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?
Feb 18 '09 #7

Expert Mod 15k+
P: 31,418
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.
Feb 18 '09 #8

Expert Mod 15k+
P: 31,418
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.
Feb 18 '09 #9

P: 50
Thanks to everyone for your clear explanations!
I am learning new things again. :)
Feb 19 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.