multi-user question

S

scubadiver

I have a split database with users that log queries into the same table. For
the primary key I have a "dmax" code to add 1 to the highest number.

If two people add queries, one of them may not be accepted because of a
duplicate ID number.

I have seen the "refresh rate" in the options. If I lower the number will it
reduce the possibility of unaccepted records?

Cheers
 
P

Pieter Wijnen

Nope, but you can save the record immediately
ie

Private Sub GetID()

On Error Goto ErrDup

Get_ID:
VBA.Err.Clear
Me.ID.Value = Nz(DMax("ID",MyTable"),0) +1
Me.Dirty = False
Exit Sub

ErrDup:
Resume Get_ID ' should check for the actual err, but ..
End Sub

HTH

Pieter
 
S

scubadiver

thanks for the reply.

How does that code work?

Pieter Wijnen said:
Nope, but you can save the record immediately
ie

Private Sub GetID()

On Error Goto ErrDup

Get_ID:
VBA.Err.Clear
Me.ID.Value = Nz(DMax("ID",MyTable"),0) +1
Me.Dirty = False
Exit Sub

ErrDup:
Resume Get_ID ' should check for the actual err, but ..
End Sub

HTH

Pieter
Click to expand...
 
P

Pieter Wijnen

Probably like yours, except that I save the record by using Me.Dirty =
False (simmilar to DoCmd.RunCommand acCmdSaveRecord)
If the save fails - somebody else is doing it at the same time & I retry
till I succeed.

Pieter
 
S

scubadiver

I have written a question on "short time" format in the query section (first
in the list).

Maybe you can help me with that?
 
B

BruceM

This sample database shows a way to increment the number in a multi-user
environment.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

I used a custom function AssignNumber to create an incrementing number. It
includes letters, so it is a text field. I call the AssignNumber function
at the first opportunity, and assign the result to a string variable. In
the form's Before Update event I run the function again, and compare the
result to the string variable. If they are the same, all is well. If not,
the new value from the function becomes the value for the incrementing
field. To be safe, I assign the new result to the string variable, and run
the Before Update comparison again, just in case in that second or two
somebody cuts in on the line again.
 
B

BruceM

In case I have not been clear, the sample database and the method I
described are two different things.
 
R

Rick Brandt

scubadiver said:
I have a split database with users that log queries into the same
table. For the primary key I have a "dmax" code to add 1 to the
highest number.

If two people add queries, one of them may not be accepted because of
a duplicate ID number.

I have seen the "refresh rate" in the options. If I lower the number
will it reduce the possibility of unaccepted records?

Cheers
Click to expand...

What event are you using to make the DMax() calculation and assignment in?
The BeforeUpdate event is the only one that is reliable in a multi-user
environment.
 
Top