Duplicate record numbers....

  • Thread starter Thread starter tonybury via AccessMonster.com
  • Start date Start date
T

tonybury via AccessMonster.com

I am not sure where to post this question as it applies to multiple areas
here. I am having a problem with record numbers being duplicated. I am
calling a piece of code from a module to create a new sales order number
based upon a sql query of max(sono) from my sales table. This works great
with one exception. I am connecting this database back to foxpro tables via
odbc and when multiple users use the database and just happen to click the
submit button to write the data back to the foxpro table there is times where
they are both grabbing the same order number. Which is causing me a freaking
headache from banging my head against the wall trying to figure out a way
around it. I have put the call function to retrieve the max(sono) from the
sales table at the last step before I run the insert statement. I would have
a hard time writing a dummy record to grab the number as the table (which I
cannot modify) does not allow any of the 100+ fields to be null. I was
thinking about just using a select statement within my insert statement to
grab the last order number (max(sono)) but although I am pretty decent with
SQL I am not that advanced. I don't even know if it is possible. I only run
into this problem when two people just happen to click submit at the same
time. I have even thought about putting some type of random pause function to
get around this but there just has to be a better way that someone knows that
is more advanced than me with coding.

Please, please, please help!

Regards,

Tony
 
hi Tony,
I am not sure where to post this question as it applies to multiple areas
here. I am having a problem with record numbers being duplicated. I am
calling a piece of code from a module to create a new sales order number
based upon a sql query of max(sono) from my sales table. This works great
with one exception. I am connecting this database back to foxpro tables via
odbc and when multiple users use the database and just happen to click the
submit button to write the data back to the foxpro table there is times where
they are both grabbing the same order number.
Click to expand...
Don't know if it works with Foxpro, you need a unique constraint on your
record number field, then the insert will fail:

Dim db As DAO.Database
Dim i As Long

Set db = CurrentDb
i = 0

db.Execute "INSERT INTO Table () SELECT Max() + 1, Values FROM Table"
Do While (db.RecordsAffected = 0) Or (i < 5)
i = i + 1
Loop
If db.RecordsAffected = 0 Then
MsgBox "not saved"
End If



mfG
--> stefan <--
 
Back
Top