Record locking

[Naz]

Hi

In my DB i have a form to capture information about files that we have put
in boxes for filling. The form is launched from a switchboard and when the
form opens a NewBox number is generated, and a new RMS number.... the new box
number is simply the max box number in the underlying table + 1 as is the RMS
number, code is like below

DoCmd.GoToRecord , , acNewRec

Forms!frm_ArchivedInfo!.RMS_FILE_REF = DMax("[RMS_FILE_REF]",
"tbl_FileInfo") + 1
Forms!frm_ArchivedInfo!.WANDSDYKE_BOX_NO = DMax("[WANDSDYKE_BOX_NO]",
"tbl_FileInfo") + 1

My problem is that if i go into the database and click new box i get number
say, 50, but if someone else also goes into the DB on their PC....instead of
getting the next number 51, it also gives them 50. The database simply sits
on a shared drive that anyone can access, there is no security settings etc,

I have tried changing the record lock but can't figure this out.....can
anyone help?



All help is much appreciated.

 

[Jordan]

What you can do is create a table that holds the counters for the next
available numbers and when someone creates a new record it pulls the number
from the table, then increments it by one. This way the next person does
the same. The only drawback is that if someone cancels an order midstream
you lose the number they pulled, but I think that is the same in practially
every other system.

One other thing I did just to help prevent the counter from getting screwed
up was to query both the counter in the table and the highest record in the
data table. This way if I saw the counter at 50 and the records in the data
table already had a 50 I knew to pull 51 and set the counter for the next
available number to 52.