multi user form

[rapsr59]

Hi Eddie!

More code...

When entering a new record the user may inadvertently want to close the form, or close the database, without saving (committing) or canceling the new record. Should this be allowed to happen, then the new record in the DataTableTemp table will be “stuck” in the table with no way to retrieve the record. My suggestion is this.

Do not allow the user to close the form or the database application without either committing or canceling the new record.

This can be done by entering the follow code in the forms Unload event.

[COLOR=black][FONT=Verdana]Private Sub Form_Unload(Cancel As Integer)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Me.RecordSource = "DataTableTemp" Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   MsgBox "A New Record Is In Progress!" & Chr(13) & Chr(13) & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   "You will not be allowed to close this form or this application until you..." & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Chr(13) & Chr(13) & "Commit the new record - or - Cancel the new record."[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Cancel = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

In this manner the user will be required to make a decision to either commit the new record or cancel the new record.



There is one final possibility. Should the electrical power go out when the user is entering a new record then the new record will also inevitably be “stuck” in the DataTableTemp table.

The next bit of code checks for this possibility and will open the form to the “Stuck” new record the next time the form is opened by the user. The below code will run in the forms Open event.

[COLOR=black][FONT=Verdana]Private Sub Form_Open(Cancel As Integer)[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]'Check if a new record exits, notify the user, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' and load the existing new record[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim dbs As DAO.Database[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim rst As DAO.Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set dbs = CurrentDb[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set rst = dbs.OpenRecordset("DataTableTemp", dbOpenSnapshot)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Not rst.EOF Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   'New Record exists[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   MsgBox "A new Record exists and will be opened at this time!" & Chr(13) & Chr(13) & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   "After the new record opens please decide to commit or cancel the new record."[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Me.RecordSource = "DataTableTemp"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Me.cmdCommitNew.Visible = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Me.cmdCancel.Visible = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Set rst = Nothing[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set dbs = Nothing[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

This just about concludes the code and controls I use in my Multi User Orders environment at work and has been used since 1996 without any problems.

Should you run into any snags let me know and I will be happy to try and resolve them for you.

Best Of Luck!

Richard

 

[rapsr59]

Hi Eddie!

Another consideration.

The program I designed to use at work is an orders program. The main form I designed contains the customers information and the sub form contains the product information. If your design contains only a main form, then the programming techniques that I have described previously will work. If your design contains a sub form then further programming will be necessary to successfully carry out your task.

You will be required to create a temporary table for the sub form records – the records that are related to the main form record. This temporary table will be located in the User Database. A link to the sub forms persistent records that are located in the Master Records Database will also be required. When entering a new record you will be required to change the record source on the sub form to the temporary records and, after entering the new sub form temporary data (records) you will be required to append the sub forms temporary records, as well as the main forms record, to the linked persistent records and then delete the sub forms temporary records.

If you require an explicit explanation and code to accomplish this let me know and I will explain this technique in detail.

Richard