dlookup or dmax which is better

[niteraven]

Hello All

I have done a little research and am wondering which would be best for the following scenario. I have multiple users coming into a database to add a change notification. The “Unique” identifier is to be formatted to 9999-9. This is numeric, first 4 numbers are a work order number and after the dash number is to be incremented by one based on the last entry (which may have been months to years ago, or never have had a change notification)

Also this needs to be available when copying the current record to a new record. (User has 5 work order numbers (not in sequential order) not the same work order numbers: 2626-1, 5658-4, 8595-2, etc.. I have a copy function that copies the data to new record and will have to be able to then search again for the last number used. (Requeried)

So if 2626-1 is the number of the new change the user would type in the 2626 and then using dmax or dlookup, find that no change notifications exist for this #, and the 2626-1 is added in the field.

This will be a database used by 15 or so people, but run on a network, and more than one person will be entering or searching for information at a time.

Any opinions appreciated!
Thanks
Raven

 

[vbajock]

I pretty much use dmax+1 100% of the time in all my record counter logic.

 

[missinglinq]

I do, as well. The only caveat, in a multi-user environment, to prevent the possibility of two users being assigned the same number, is that you do this at the last second before the Record is saved, which means in the Form_BeforeUpdate event. Using this kind of code, in this manner, for over a decade, I've never experienced a 'duplication' problem.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[niteraven]

Hi all

I am finally back on this project. And the only way i can get The “Unique” identifier to accept the 9999-9 scheme is to make it text. I cannot get the dmax function to work with auto increment code because it is text.

Any ideas?>

Thanks
raven

 

[dhookom]

DMax() allows you to use expressions etc. Personally, I would create my own user-defined-function to do this.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

No one has said it so I will... I'd store the Work order number and iteration as separate columns. Then you can display them however you want.

 

[niteraven]

Thanks guys.
Lameid: That is exactly what i did. now to make code work!
Thanks!
Raven

 

[lameid]

I would default iteration to 1 and have the work order as an autonumber... then on one of the update events, increment the iteration appropriately or increment however makes sense to your process.