Confusion over record sets, locking, muti-user,

[Sonny]

This is my first access project. And I have Hit my first 'Major' stumbling block!

Essentially the project is one main table and a bound form (there are some other static tables, and I may have a question or two about that when it comes to splitting, like where to put them front or back ?)

in the main table there are two fields which are numbers generated when the inputter hits a button on the form
One number is a like a ticket number
The other counts the number of tickets for the given date.
These are generated using Dlookup type functions and are based on values that already exist in the main table. neither of them are the primary key, thats just an autonumber.

It will be used on 5 client machines and potentially, if only occasionally, by 2 or 3 people at the same time.
I am going to split it into front and back end. ( I've done a few experiments with the splitting wizard. )the BE will be on the main server.

while testing I had three people hit the button at the same time and they all got issued the same number

I have been googling around record set locking, but this appears quite a complex issue for the novice theres no real explanation in laymans terms as I have found, and to be honest, despite recognising the issue, I haven't a clue where to begin. (or even which is the more appropriate subforum to post this question in)

Some questions:
Can I lock the (main Table) on the button click(form) event? Or do I have lock it everytime a new form is begun? (this could be a real bind)

Is it the case(once the database is split), that once the inputter starts entering data on the form(in the FE) that the data goes into the table (in the BE) even before the form is complete. sort of like a new row in the main table is reserved On a form being opeened?
if that data is partially filled to the table as the data is entered and before the form is complete? could this have an impact on counting the tickets on a given date?

I would greatly appreciate pointing in the right direction

Oh and just confuse matters even more one of client machines has Msaccess 2007 and the others have MSaccees2003 and the file type of the mdb file is access2000



Much gratitude in advance

[June7]

I have a similar situation and minimize the chance users will get issued same number by immediately saving record to table with minimal information - the primary key and date. Then the data entry form opens to this newly created record. Been running 3 years now without issue.

[Sonny]

I'm not sure i understand. Why would it make it difference how much information (minimal or complete record)was in the table when the record is saved
isn't the chance of duplicating the number the same in either case if the event that generates the numbers occurs on two machines at the same time

[June7]

Could save with just new ID but I save with ID and date because my project allows user to abort login. This login action removes the date and next user to login is assigned this existing number for their new login. All numbers accounted for, no gaps.

The new record and ID are saved immediately and this is so fast it is unlikely (not impossible) two users will get the same number. I did say earlier that this has not been an issue in 3 years. I now remember this duplication attempt did happen once. Second user got message 'record already edit by another...' user canceled record update and started another login with a new assigned number. Not a big deal.

Could allow data entry and assign new number when finished but does user need to see the number? In my project record and ID are commited then data entry form is opened filtered to this record with number displayed and user can take their time entering other data.

[Sonny]

Both numbers are seen and used by the person inputting the data.

In my test in which i deliberatley got users to hit the button at exactly the same time the same numbers were generated.

Can you explain why saving the record with minimal information will reduce this risk? And what to do about fields which have validation eg (is not Null)

The duplicates in my tests were not saved because the ticket number field is indexed no duplicates. creating another issue the data has to inpuuted again.... Ah.. okay.. I see... yes you would avoid this issue of having to reinput the data. I am still not sure this method would work for me though

Here is what I am trying to achieve

 Private Sub Numbers_Click()
If IsNull(Me![Ticket]) Then
IF the Maintable is locked pause for 200ms
While The MainTable is Locked Pause for 200ms 
end while loop
End If
Lock the MainTable and dont allow any other users to read or write to it
 
Me![Ticket] = Nz(DMax("[TBLTicket]", "[Main]"), 0) + 1
Me![Counter] = Nz(DMax("[TBLCounter]", "[Main]", "[TBLDateOpened] = #" & Forms!Main!DateOpened & "# "), 0) + 1
 
Save the record
Unlock the mainTable 
 
End If
End Sub

[June7]

What I meant was that immediately saving the record with the new number, as opposed to just building the new number and holding it in a variable while user inputs all data, reduces the risk of duplicate assignment.

I have never tried to test deliberate generation of duplicate number as you did (one accidental as I mentioned earlier). But doesn't matter how fast they are, even if the code builds the same number, one user is first to save it and open the record. And since this is a unique ID, the second user trying to save the same number can't. Even if the second user opens the same record, attempting to save data to record already in edit by another user generates warning popup. At least, this has been our experience. Maybe some error msg popups, but never a duplicate record, unique ID won't allow that.

The table is set for only one required field, the generated unique ID primary key. Mandatory data entry is controlled by code behind the form. Users can't quit until enter required data or click Abort button.

[Sonny]

Thanks again for your input June7
at the point when the ticketNumber and Counter are generated some data MUST have already been input (the DateOpened for example will not always be the current date and this date is relevant to the counter) I have thought about completely changing the tab order of the form to circumvent this this but cant find a practical way of doing it.

Thanks to your posts, I see how you can short-cut this problem in certain circumstances. (and have duly noted it) but i dont think it will work in this instance. so am still googling and gradually getting closer to where I want to be which i believe tis understanding openrecord set and locking aruguments whilst various functions/methods are caried out