multi user form

[Rockape]

Hi all,

Grateful for advice.

The theory:

I'm trying to get two users to enter data into a form. However the form picks an ID number when it opens the form. I would like that each user on opening the form gets the next sequential number.

My problem is that both users are getting the same ID number and whoever exits the form gets the number assigned. The second user gets an error message.

Can anyone point me in the right direction.

regards

 

[Rabbie]

We need some more info. How do you allocate the ID number at present and do you store the new ID number in a table as soon as it is allocated. Is the form bound to a table/query. The more info you can give the easier it will be to help you.

 

[Rockape]

Hi, thanks for your interest.

I'm in a multi user environment where two or more users can access the same form.

When the form is opened (in a single user environemnt) there is a field in it that assigns a number

Private Sub Form_Current()
If Me.NewRecord Then
On Error GoTo Err_Form_Error 'Resume Next 'this should not occur, but to be sure
Me![holder no].DefaultValue = Nz(DMax("[holder no]", "holder"), 0) + 1
End If
End Sub

When the form is closed and then opened it assigns the next number and the record is saved accordingly.

I have a button (ADD RECORD) which "refreshes" the form and the form assigns the next number.

Problem:
When two users open the same record, both open with the same number but it will only assign that number to whoever saves first. On Save the second user gets a n error message. (Predictable)

I would like that the second user save with the next number.

May its still not clear.... await comments

 

[fkegley]

Maybe an Update query that sets the number to the next value could take care of this. You would want it to execute IMMEDIATELY after the number is assigned.

 

[Rockape]

Hi thanks for the response.

The problem is that both users can open the form at which point the form is displaying the same ID Number in both forms. however whoever saves the record first is assigned the number. the second user will then get an error message when he saves! and has to open the form again by which time the previous user is already in....

Am I making any sense?

 

[fkegley]

I missed that you were getting the number from a table in the form's record source.

Another thought I have is that you could place a command button on the form that would then go and get the number. You could place code in the After Update event of the form to ensure that there is a number in the text box. You could also Lock the text box so that they couldn't type into it.

It would then be under their control whether or not there is a number in the text box. This would not be foolproof either, I doubt if there is a completely foolproof way to do it, but it might cut down the number of times this happens.

 

[pbaldy]

You might try adding this right after assigning the number:

DoCmd.RunCommand acCmdSaveRecord

It's not the iron-clad solution, but it should prevent most problems. It wouldn't work if you have other required fields though.

 

[Rockape]

Hi,

Thats not quite what I want....lets see..

As it stands, the form opens and it captures a number which is still not saved in a table. I then enter the data save the record with that number.

I then click on an ADD RECORD button and the form clears of data except that the next sequential number appears. This all works fine in a single user environment.

I now want to convert that into a multi user environment. I now have the problem that if two users open the form, both open with the same number. Whoever completes the data first will have "grabbed "that number. The slower user who is still in the form with the "already saved" number cannot save his data because an error message is basically saying that that number is already in use and hence that record cannot be saved.

Trying to overcome this problem is driving me nuts!

Regards

 

[pbaldy]

The line of code I posted should save the record immediately, so unless 2 users started a record at the same instant, they would get different numbers.

 

[Rockape]

Hi thanks,

Hmmm... nearly there I thought with your suggestion... but i have other fields to populate, i would have to close and open that form and edit.....then save again, knowing that i have "captured that number".

Easier said than done!

Thanks...

 

[pbaldy]

Have you tried what I posted? It would not require the form to be closed and opened; it will simply force a save. The form should still be on that new record. The forced save should make sure the next user doesn't get the same number.

Actually I just noticed that you were populating the default value. I'd simply populate the control with the value.

 

[Rockape]

Hi thanks,

Will try tomorrow... thanks once again

 

[rapsr59]

I developed an order system. At times up to four computers are involved with entering orders. The way I was able to overcome the problem of all four computers entering the same order number is as follows.

Let’s assume 4 computers open the order form simultaneously. All four computers will lookup the last order number and assign the next order number for its order. Let’s assume that the last order number was 10803. All four computers set up a new order with the order number 10804. While each order is being processed each order will have the same order number 10804 - temporarily.

I attached a “Commit” command button that when clicked runs code to again check the last order number.

When the first computer commits (saves) its order, it finds that the last Order number is 10803 and uses the (default) order number 10804. Remember that the other three computers are still using order number 10804 - temporarily.

When the second computer commits its order the code checks for the last order number used and it finds that the last order number used was 10804 (by computer 1) and it adds 1 making its order number 10805.

When the third and fourth computers commit their order they check the last order number as computer two did and add 1 to the new order while committing the save operation. The third computer updates its order number to 10806 and fourth to 10807. Beautiful!

Not so beautiful?

I designed this system in 1996. It worked perfectly for over four years. Then the inevitable happened. Two operators committed precisely at the exact same time and an error occurred. The order number field is the Key Field and it is indexed using No Duplicates. What to do? What to do?

Well, I created a table called Busy. The Busy table has 1 field only. The name of the field is Busy and is a Yes/No field. I then added code to the commit button that checks the Busy field in the Busy table. If the Busy field is FALSE then code sets the Busy field to TRUE and goes about its business of saving the new order (record). If the Busy field to TRUE then that means another computer is in the process of saving it’s new record and the code runs a loop checking the Busy field until it finds the Busy field set to false and then sets the busy field to true and saves it’s order (new record). Upon successfully saving it’s new order (record) it sets the Busy field to false. Then the other computers can save their orders.

The code, to do all of this, is a lot less writing than this explanation.

Incidentally, I am pleased to say that we write over 80 orders per day 6 days a week and have never had the error “The changes you requested to the table were not successful because they would create duplicate values etc. etc. etc” again.

Good luck. I hope this will help you in overcoming your dilemma,

Richard

 

[Rockape]

Hi Richard,

Gosh you've hit the nail on the head. Would it be too presumptious of me assume that you might get me on the right track by illustrating this with some code.

I'm facing a deadline in four days time.. !

 

[Rockape]

Hi,

Tried this ,,, but it didnt work.. back to the drawing board!

Thanks once again.

 

[rapsr59]

Hi,
Thanks for your previous message.

Do you have some code that could well illustrate your point.

It would be grately appreciated.

Many Thanks

----------------------------------

I will include code at a later date. But first I must make one point clear which I did not make clear last night. I apologize for any confusion I may have created.

The very important point that I did not include earlier is that I use two sets of identical tables. One set of tables are the actual data tables that will contain the stored data in a persistent manner. The second set of tables are temporary tables. When the users are entering data into a new record the data is stored in the temporary tables. The temporary tables are local on each computer. When the user clicks the commit button, and after the next order number is computed, a query appends the new record to the actual data tables. Then a second query deletes the temporary records. Only In this manner can all computers use the same Order number to begin with - which is then updated by the code as described earlier.

The form that is used for data entry has a dual purpose. The first use is to enter the new record into the temporary tables. The second use is to edit existing data in the actual data tables. When this form is opened, the form used to open it instructs it as to which mode is to be used by sending it "1", for the new record mode and "2" for the edit record mode, in the OpenArgs argument.

Example: DoCmd.OpenForm "Orders", , ,"1" ‘New record – (2 for editing records)

When the Orders form opens code in the forms open event checks its OpenArgs and then sets its Record Source to the appropriate set of records. Two queries are used. The first query uses the temporary tables as its underlying record source. The second query uses the actual data tables. The code to accomplish this is as follows:

If me.OpenArgs = "1” then
Me.RecordSource = "QueryTempOrders"
Else
Me.RecordSource = "QueryOrders"
End if

Well it is Thanksgiving and I’m scheduled to visit with my family so I will get back with you as soon as time allows.

May you and yours have a very Happy Thanksgiving!


Richard

 

[Rockape]

thanks,

Hope to talk to you soon.

Happy Holidays

 

[rapsr59]

Ok! I promised you some code.

I am assuming that you have several computers that users utilize to create data - a multi-user environment.

With this in mind, I suggest you use one computer to store the persistent data the users collect. This computer will have a master records database that will contain a table we will refer as to asthe MasterDataTable. This table will contain the fields and data that all users will collect and edit. Some refer to this database as the Back-End Database. No Forms, No Queries, No reports, No nothing, just Tables that contain the persistent data. Backing up this database daily is recommended.

The second thing you do is to design a database on one of the user computers. This database will contain the tables local to the user machine, links to the tables in the Master Records Database containing the MasterDataTable, queries, forms, reports, macros and modules used in manipulating your data and for dissemination purposes. We will call this the User Database.

After creating the User Database import a copy of the MasterDataTable located in the Master Records Database using the File-Get External Data-Import command. This will create a duplicate of the MasterDataTable located in the Master Records Database. Rename this file using a the suffix temp. This table will be used for entering a new record.

Next create a link in the User Database to the MasterDataTable located in the Master Records Database by using the File-Get External Data-Link Tables… command. This table will be used for editing existing records.

Now you have two tables. 1 – the MasterDataTable link and 2 - the DataTableTemp.

Now the fun begins.

This is the easy part. Create a form using the MasterDataTable – the linked table - as it’s record source. This form can immediately be used for entering a new record or editing an existing record. The problem is that in a multi-user environment you will get an error when two computers try to use the same record number and the subject field is set to NO DUPLICATES. I believe this is the problem you are encountering.

The answer to this dilemma is to change the Record Source on the form to the DataTableTemp table while entering a new record. Run the code…

[FONT=Times New Roman][SIZE=3]Me.RecordSource = [/SIZE][/FONT][FONT=Arial Unicode MS]"[/FONT][B][I][FONT=Times New Roman][SIZE=3]DataTableTemp[/SIZE][/FONT][/I][/B][FONT=Arial Unicode MS]"[/FONT]

before entering a new record. When the user is entering a new record it will be stored in the DataTableTemp table – temporarily.

To run the above code, you can create a button called cmdNew and place the above code in the click event. You can also place the following code in the same event.

[FONT=Times New Roman][SIZE=3]Me.cmdCommitNew.Visible = True[/SIZE][/FONT]

The cmdCommitNew command button is invisible when editing is performed and will be used to save the NEW record upon its completion by the user.

A temporary record number must be used as the field property Required will undoubtedly be set to true.

A simple way to do this is to run the following code in the Before Update event.

[FONT=Times New Roman][SIZE=3]Me.RecordNumber = 0 [/SIZE][/FONT][B][FONT=Arial Unicode MS]' [/FONT][/B][FONT=Times New Roman]Change this field to actual name of field[/FONT]

And now I will include the code that runs when the new record is completed and the cmdCommitNew command button is clicked.

[FONT=Times New Roman][SIZE=3]Private Sub cmdCommitNew_Click()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]On Error GoTo Err_cmdCommitNew_Click[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman]     Screen.PreviousControl.SetFocus[/FONT][/SIZE]

[SIZE=3][FONT=Times New Roman]     Dim dbs As DAO.Database[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Dim rst As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Dim lngRecordNumber As Long[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     'Make sure the record has been saved[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     If Me.NewRecord Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          DoCmd.RunCommand acCmdSaveRecord[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     'Update the record number to the next record number[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Set dbs = CurrentDb 'This db[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Set rst = dbs.OpenRecordset(MasterDataTable, dbOpenSnapshot)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     With rst[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]          If Not .EOF Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               .MoveLast 'Move to last existing record[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               'Change the new record number from 0 to the next record number[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               Me.RecordNumber = ![RecordNumber] + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               Me.RecordNumber = 1 'No previous record so make it record # 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     End With[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     'Save the record number for later use[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     lngRecordNumber = Me.RecordNumber[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     'Append the temporary record in DataTableTemp to the existing[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     ' records in MasterDataTable[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     DoCmd.RunSQL "INSERT INTO MasterDataTable " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "SELECT DataTableTemp.* " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "FROM DataTableTemp;"[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     'Now delete the new temporary DataTableTemp record[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     DoCmd.RunSQL "DELETE DataTableTemp* " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "FROM DataTableTemp;"[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     Set dbs = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Set rst = Nothing[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     'Reset the recordsource and then goto the new record[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Me.RecordSource = "MasterDataTable"[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     'Locate the newly appended record[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Me.RecordsetClone.FindFirst "[RecordNumber] = " & lngRecordNumber[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Me.Bookmark = Me.RecordsetClone.Bookmark[/FONT][/SIZE]

[FONT=Times New Roman][SIZE=3]     ' Now hide the commitcontrol[/SIZE][/FONT]
     Me.cmdCommitNew.Visible = True
[SIZE=3][FONT=Times New Roman]     'That’s All Folks[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]Exit_cmdCommitNew_Click:[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Exit Sub[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]Err_cmdCommitNew_Click:[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]MsgBox Err.Description[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Resume Exit_cmdCommitNew_Click[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

And that’s it

As an alternate you can create two queries

• An Append Query…

INSERT INTO Orders
SELECT OrdersTemp.*
FROM OrdersTemp;

• A delete query…

DELETE OrdersTemp.*
FROM OrdersTemp;

Then you can run them in lieu of running the SQL statement.

Please let me know if this has helped you.

Best of luck - Richard

 

[Rockape]

Hi,

Thanks for your reply. Sorry for not having replied earlier (bed with cold!). Have just read your reply.

Thanks again i will try this out. Looks good.

Eddie

 

[rapsr59]

Eddie


Add a cmdCancel to your form.

When clicking the cmdNew button you can add the following code.

[FONT=Times New Roman][SIZE=3]Me.cmdCancel.Visible = True[/SIZE][/FONT]

This button is designed to cancel a new record. If the user decides to cancel adding a new record, by clicking this button the new record in the DataTableTemp table is cancelled and deleted and the record source is reset to the MasterDataTable.

The code behind the cmdCancel button is…

[FONT=Times New Roman][SIZE=3]Private Sub cmdCancel_Click()[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman]   'Set focus on previous control so this control can be hidden[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Screen.PreviousControl.SetFocus[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]   'Reset record source to the MasterDataTable[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Me.RecordSource = "MasterDataTable"[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]   'Now delete the new temporary DataTableTemp record[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   DoCmd.RunSQL "DELETE DataTableTemp* " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   "FROM DataTableTemp;"[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]   'Now hide the new and cancel buttons[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Me.cmdCommitNew.Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Me.cmdCancel.Visible = False[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

This should complete the code required for entering a new record in a multi user environment.

There are other considerations. You may want to hide the record selectors, the navigation buttons, etc… when entering a new record and then make these visible when committing the new record. The code for this would be run when the cmdNew button is clicked.

By hiding the navigation buttons the user will be not be able to navigate records in the new record mode since there is only 1 record to view: The new one.

By hiding the record selector, the user will be forced to use the cancel button to delete the new record.

These controls can then be set to visible when the user clicks the cmdCommitNew or the cmdCancel buttons.


Richard