Can I do a DMax on multiple fields in the same query?

[mdgibbs88]

I have built a database to store case files. The case number is automatically assigned starting at number 1 on January 1. I use this code to get my next sequential case number when I click to save the record:


Me!txtCASE_NUMBER = DMax("CASE_NUMBER", "tblCaseDetail") + 1
MsgBox "Your Case Number is " & Chr(13) & Me![txtCASE_NUMBER], vbOKOnly
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

This works just fine incrementing the CASE_NUMBER by 1. My problem is what happens when I start at a new year with #1 again? I want to find the current year which would be DMax("CASE_YEAR", "tblCaseDetail") and DMax("CASE_NUMBER", "tblCaseDetail") and increment that CASE_NUMBER by 1.

I guess my question is this, can I do multiple DMax's in one statement?

THanks,
Mark

 

[DCrake]

Yes

DMax("CASE_NUMBER", "tblCaseDetail" , "YearField"= Year(Date())) +1

However a word of warning tell the user what the new case number is AFTER it has created the new record, not before. If there are more than one user adding at the same time the intended new Id could be pinched by another user while you were reading the message box and waiting for you to click Ok

 

[Galaxiom]

The DMax technique can still have trouble with users simultaneously hitting the database for a new case number and getting the same one. The potential problem grows with the number of records as the time to complete the DMax increases. The save will fail for the second user so this error needs to be captured and dealt with.

A safer (and faster) way is to store the next case number in a single record table. Get the new number by opening a recordset and setting it to edit so that nobody else can get in while you retrieve the value, increment and save it.

This technique also requires code to capture the error then manage a cyclic delay and resubmit of the next number request if it fails due to another user.

Also note the use of DoMenuItem has been deprecated for a decade and is only included for backwards compatability.
The modern method is:
DoCmd.RunCommand acCmdSaveRecord

 

[mdgibbs88]

Thanks so much for the help! This is right on, and I do appreciate the tips about storing the case numbers in a separate table. Eventhough this database will not have a lot of users, it is better to be safe than sorry down the road.

Thanks again,
Mark