I have a split Access/SQL Server database. I have multiple Access forms bound to one table. The table stores responses to a large questionnaire. Too many question to fit on one form, so I divided the questionnaire into multiple forms. Each form has a button the opens the next form (next set of questions) to the proper record. Everything works as long as you proceed from one form to the next and then on the last form you save the record and close all the forms.
This is the code I have to let the user proceed to the next form (open the next set of questions):
If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.OpenForm "frmNext_SetOf_Questions", , , "tblQuestionnaire.id = " & Me.txt_id.Value
The problem I'm getting is when the user decides to go back to the previous form to change an answer and then goes back to the last form to save it and close all the forms. For example, the user goes from form 1 to form 2, then goes decides to go back to form 1 to change an answer, and then goes to form 2 to save the record and close forms 1 and 2 from a close button on form 2.
This causes a Write Conflict Error:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
I know that it would be better to use a tabbed form. I did this in the past but the user sometimes would skip one of the tabs by mistake and not ask a bunch of questions, or would select the tabs out of order. The questions should be asked in order. The questionnaire is being done over the phone, so it can be easy to be distracted and to skip or select the wrong tab by mistake.
Is there a way I can open one form after the next, give the user to ability to tab back to double check/change any responses entered in each form and then go to the last form. Hit the close button to save data entered in all the forms without getting a Write Conflict error?
Thank you.
Each new form is open from the current form with this code:
If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.OpenForm "frmNext_SetOf_Questions", , , "tblQuestionnaire.id = " & Me.txt_id.Value
In the Close button of the last form I have:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "frmForm_1"
DoCmd.Close acForm, "frmForm_2"
DoCmd.Close acForm, "frmForm_3"
DoCmd.Close acForm, "frmForm_4"
I did try to requery the forms in the Close button but still got the Write Conflict Error. Forms!frmForm_1.requery
P.S. There are no bit values.