Unique field in unbound form w/ more than one user?

[Profector]

I'm working on an unbound form that wrties a record back to a single table. The form is used supposed to be used by at least two people at once.

On the form is a field called txtOrderNum, for a sales order number, that writes a concatenated key back to the table; it's a combo of the dmax(primary key) + 1 & a combobox the user uses to select their location. So for example the field could be 1-00018. The problem is that if more than one person is working on it at the same time they could get the same sales order number because the field txtOrderNum is filled in when the form is opened and but the record isn't written till the form is saved.

So the problem is how do I get the txtOrderNum to display when the user opens the form and keep it from being duplicated when they save later?

Any ideas?

 

[missinglinq]

...the field txtOrderNum is filled in when the form is opened

The standard way to do this is to assign the txtOrderNum at the last possible moment before the record is committed to the table, not when the record is first created.

 

[ben.hornshaw]

another way to do this would be to bound the form and only assign a control to the dmax(primary key) cell. then when you launch the form fire a macro so it goes to a new record. Acces has a built in function that makes sure primary keys are not the same no matter how many users are connected therefore you would alwars end up with a different order number

Have Fun

 

[Profector]

The standard way to do this is to assign the txtOrderNum at the last possible moment before the record is committed to the table, not when the record is first created.

I agree. I just wanted the user to know the order number while the form is open so they can write down or communicate to someone else for later reference.

 

[Profector]

another way to do this would be to bound the form and only assign a control to the dmax(primary key) cell. then when you launch the form fire a macro so it goes to a new record. Acces has a built in function that makes sure primary keys are not the same no matter how many users are connected therefore you would alwars end up with a different order number

Have Fun

So what I hear you saying is. On Load create a record using the txtOrderNum (that will create the primary key too). This would be the a place holder of sorts. Then finished writing the rest of the record went the user clicks save?

I think that make since.

 

[ben.hornshaw]

Yes i have many forms like this
one creates a request for money. when the form opens it loads a new record. therefore the request ID reads (AutoNumber).
As soon as the user starts typing this changes from autonumber to the next available number. from the instance you start typing access assigns you a ID number therefore there is no crossover. my particular table has default values set for Cretor and Date therefore the second the user starts typing it automatically creates a ID number, inputs the current time and date using the Now() function and enters the Users Name. haha

Hope this helps

 

[Profector]

I would also have to delete the record if the user clicks cancel.


Thanks for the replies. I'll give it a go. The code should be easy enough now you've helped me think it through.

 

[missinglinq]

Bound or unbound, it doesn't matter! The number has to be assigned at the last instance before the record is saved. Unless you want to assign the number then immediately force a save, then fill out he rest of the record, you have the same problem, and who wants users to constantly be seeing Access warnings about duplicate primary keys?