Who was first

[jswanson1]

I use a shared access database at my work and we use progressive work order numbers to track our projects. What happens often is I refresh, create a new line, save and refresh again and all is ok. Well a couple minutes later someone will say that I took their number and sure enough their name & same number is above mine. How does this happen if I save & refresh every time and after a couple minutes passed?

I'm wondering if both people have the database open and I go through my process and save/refresh, but then the other person say doesn't refresh and enters the next number, will theirs be put in first once they do save? I'm not sure if it matters but access 2003 & 2007 are used.

thanks for helping with this pet peeve of mine

[NTC]

well first you must ignore the humans and their believe that you took "their number " - in databases there is no 'their' - - so to speak.

you've got a double assignment design flaw. not sure what is within your control but fundamentally the db should be relying upon an autonumber field to generate unique record IDs. the WorkOrder number field should be set to Indexed/Unique also.

Not sure how your WO value is being generated or 'reserved' - but it clearly is allowing too large of a time window overlap such that multiple users both think the same number is next in line. the design needs to be tweaked to narrow the overlap. you are trying to do it yourself with your double refresh technique which is commendable - but doesn't help if others aren't doing the same thing. fundamentally the db design should not make this necessary.

hope it helps.

[John_G]

Hi -

I solve that sort of problem by keeping a little table with only one record to keep track of the next available number. Then, I use a function to return the next number, and update the table, something like this:

Function Next_ID () as Integer
'
' Assume table name is ControlTable, with a field called CurrentValue
'
Next_ID = Dlookup("CurrentValue","ControlTable")
currentdb.execute "Update ControlTable set CurrentValue = CurrentValue +1"
end function

You can then use Next_id to fill (for example) a form control: me![RecordNo]=next_id().

HTH

John

[jswanson1]

Thanks guys! looks like I am gonna have to buff up my access skills to work this out. Or just tough it out