multiple users and a unique ID field

[MrM121]

Hi,

I don't really know how to ask this question properly, but here goes. My problem is this - I want to have multiple users on a database, and I want them to have access to the same forms so that they can enter the data simultaneously. An auto ID has already been created, using the default value field of the control ("PUB1234", etc...), which auto increments. This works great with one user, but not with many - both users can enter the same ID at the same time, and obviously one will overwrite the other.

I can only think of two possibilities: Check the ID before updating the database, and then changing it accordingly, or (as I have done before) create another table, with only 1 record and 1 field, which can be used to store the ID and check it that way.

Does anyone have any ideas?

Nick

 

[willir]

Nick

See MichaelRed's comment on autonumber and work around...

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=184

When creating a record, with a sequential numbering system, the developer has to decide to issue the number at the start or end of the transaction. Accesses issues the number at the start of the transaction - I suspect this makes it easier to create child records in a subform.

As you have found out, this creates problems with a multiuser environment.

I also provided an alternate solution in the past to this type of thing by using a control number stored in a control table which is updated in a central location. The number would be grabbed before the insert event, and code would make sure the insertion would occur before entering data in a subform. (Refresh).

Richard

 

[MrM121]

Hi,

I found MichaelRed's entry, and that is pretty extensive. As it will only be used by at most 3 people, I have decided on a different work around, but similar to another to what you have suggested.

Basically, this involves one line of code (the same as for the default value of the control), and it updates the control on the before_update event of the form. The downside to this is that the number can be different from what they originally thought it was, but for this database it doesn't matter too much.

However, in the long-run, I think MichaelRed's solution is the best for an extensive multi-user database.

Cheers,

Nick