Generating Unique Numbers on a Form with Multi-users

[Nerida via AccessMonster.com]

Hi there,

Hope someone can help me out. I am developing a little Db to help track
documents. There will be around 6 users of the database and each document
entered in needs a unique number to help keep track of it. The unique number
(DocNo) is the primary key and is an Autonumber.

This is what I think the sequence of events should be and what I've done to
try and acheive my goal:

1. Set the focus to the DocSent field (so uses can enter the date). As soon
as user starts typing new doc no. displays [Me.DocSentDate.SetFocus in
OnCurrent Event].

2. User enters the current date and then TABs or clicks in the next field
and the form is refreshed to add the new doc no. to the dataset to ensure it
is not used again [Me.Refresh in BeforeUpdate Event of DocSentDate field].

At step two I get an error: Run-time error ‘2115’ – the macro or function
set to the BeforeUpdate or ValidationRule property for this field is
preventing Access from saving the data in the field.

I would also like to have some compulsory fields but this doesn't seem
possible if you're going to use the Me.Refresh command ... or without a lot
of serious programming.

Can anyone help me please?

Many thanks,
Nerida.

 

[Keith Wilby]

Hi there,

Hope someone can help me out. I am developing a little Db to help track
documents. There will be around 6 users of the database and each document
entered in needs a unique number to help keep track of it. The unique
number
(DocNo) is the primary key and is an Autonumber.

Using AutoNumber may give you gaps in the numbers, eg 1, 5, 6, 7, 12 ... and
so on. Use this as a text box's default value if you want sequential
numbering:

Nz(DMax("MyField","tblMyTable")) +1

Keith.
www.keithwilby.com

 

[Douglas J. Steele]

Using AutoNumber may give you gaps in the numbers, eg 1, 5, 6, 7, 12 ...
and so on. Use this as a text box's default value if you want sequential
numbering:

Nz(DMax("MyField","tblMyTable")) +1

Not sure that's appropriate in a multi-user environment, Keith. While the
odds are very low, it's possible two users may grab the same value that way.

Of course, there was no mention made that gaps in the numbering were a
problem.

 

[Keith Wilby]

Not sure that's appropriate in a multi-user environment, Keith. While the
odds are very low, it's possible two users may grab the same value that
way.

Works fine for me Doug, but I always do a refresh immediately and don't
allow deletions (provide a "deleted record" flag instead).

Of course, there was no mention made that gaps in the numbering were a
problem.

True, but my money's on no gaps, perhaps that's why I'm always skint ;-)

Regards,
Keith.

 

[Rick Brandt]

Works fine for me Doug, but I always do a refresh immediately and don't allow
deletions (provide a "deleted record" flag instead).

What does refreshing have to do with it? If a user navigates to the new record
position he can let the form sit like that indefinitely before he actually
enters and saves a record. During that interval numerous other users will have
already used the default number on his form as well as many higher ones. He is
virtually guaranteed to get an error unless your idea of "multi-user" is very
conservative.

Not to mention that assigning this value with the DefaultValue property doesn't
work at all on a continuous form (even in a single user app).

 

[Keith Wilby]

Hi Rick.

What does refreshing have to do with it? If a user navigates to the new
record position he can let the form sit like that indefinitely before he
actually enters and saves a record.

I think you may have misunderstood. I have Allow Additions set to False and
the following code in the button's click event.

Private Sub cmdNext_Click()

Me.txtUniqueNo.DefaultValue = Nz(DMax("MyField", "qryMyQuery")) + 1
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
Me.AllowAdditions = False

End Sub

I haven't worked on this app in a while but IIRC "Me.AllowAdditions = False"
has the effect of saving the record, rather like a Me.Refresh.

During that interval numerous other users will have already used the
default number on his form as well as many higher ones. He is virtually
guaranteed to get an error unless your idea of "multi-user" is very
conservative.

Not to mention that assigning this value with the DefaultValue property
doesn't work at all on a continuous form (even in a single user app).

I think that two or more users clicking the button at *exactly* the same
instant is a remote possibility, so remote that the risk is negligible. It
hasn't happened for this particular app in the 10 years it's been in use.

Regards,
Keith.