Sequential numbering problem 1

[ChuckCasey]

I created a database for submitting service requests. The request form is filled out by the requestor and submitted, then finance updates the record as needed. When the service is completed, finance closes the ticket by choosing "Completed" from a drop down box. At any time during the process the request can be voided. The records are autonumbered. The problem I'm having is that when finance reports on all of the completed records, the numbering sequence must be sequential. (This is some government requirement for whatever they're doing). This doesn't happen because of the voided records. I'm thinking I can create another field that would populate when the Completed option is chosen, but I can't figure out how to make it increment from the last completed record, which may or may not be the previous record. I hope this makes sense and that someone can help me out. Many thanks.

 

[TheAceMan1]

How are ya ChuckCasey . . .

In VBE help have a look at the [blue]DMax[/blue] function!

See Ya! . . . . . .

 

[ChuckCasey]

You are THE man!! Thank you. It works great! Have a star.

 

[JeremyNYC]

Keep in mind that this can fail if you use it with multiple users, because two people can hit the db at the same time, getting the same number.

There are lots of ways around this. There's code in the Access [version number] Developer's Handbook to do this. The key is to have another table that stores a seed value. Then you lock the table, get the number, increment it, and unlock the table.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[TheAceMan1]

JeremyNYC . . .

Access security, easily prevents two users form accessing the same table/record at the same time! Be more specific as to how this can come about!

See Ya! . . . . . .

 

[JeremyNYC]

Aceman,

No, actually, Access security doesn't prevent two users from seeing the same record. That's a big part of the power of a multiuser database. You can play with locking to make it lock people out, but you would be getting rid of some tremendous power, and it's just generally not the way people set up databases.

In any case, it's quite well documented (search around Groups.google.com on the comp.databases.ms-access newsgroup) that using dmax on a table in a multi-user database is not a reliable way to get the next number to use. That's the reason the code is included in the Developer's Handbook series.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[TheAceMan1]

JeremyNYC . . .

Yes your right. After digging in my library DB I found references to this issue. I somehow managed to it with a similar issue involving autonumber (reason it was hard to find). But its out in the open now, thanks for the wake-up . . .

See Ya! . . . . . .

 

[JeremyNYC]

Yeah, this is one of those issues that is easily "solved". The easy solution usually works in this case, so lots of people see it as a real solution. But it can and does fall down, so it's really worth the time and effort to implement a more robust solution. Which is why I wanted to make sure to post back about this--to make sure folks researching this in the future, or reading this thread now, see a pointer to the fuller solution.

Cheers.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org