If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Generating Unique Numbers on a Form with Multi-users
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. -- Cheers, Nerida Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#2
|
|||
|
|||
Generating Unique Numbers on a Form with Multi-users
"Nerida via AccessMonster.com" u15138@uwe wrote in message
news:6ac7b8cb13e38@uwe... 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 |
#3
|
|||
|
|||
Generating Unique Numbers on a Form with Multi-users
"Keith Wilby" wrote in message
... "Nerida via AccessMonster.com" u15138@uwe wrote in message news:6ac7b8cb13e38@uwe... 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 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) |
#4
|
|||
|
|||
Generating Unique Numbers on a Form with Multi-users
"Douglas J. Steele" wrote in message
... "Keith Wilby" wrote in message ... "Nerida via AccessMonster.com" u15138@uwe wrote in message news:6ac7b8cb13e38@uwe... 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 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. |
#5
|
|||
|
|||
Generating Unique Numbers on a Form with Multi-users
"Keith Wilby" wrote in message
... "Douglas J. Steele" wrote in message ... 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). 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). -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Generating Unique Numbers on a Form with Multi-users
Hi Rick.
"Rick Brandt" wrote in message . net... "Keith Wilby" wrote in message ... "Douglas J. Steele" wrote in message ... 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). 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. |
Thread Tools | |
Display Modes | |
|
|