J
Jez
I have a database that is Access FE and SQL Server BE.
Once the user has input all the relevant data into the Combo & TextBox's I
have a submit cmdButton which will then update the relevant recordset in the
table. My problem is that once this has been done and the User clicks Add New
record, after inputting a PatientID there is a pop up box stating
"Write Conflict"
"This recod has been changed by another user since you started editing it. If
you save the record, you will overwrite the changes the other user has made.
Copying the changes to the clipboard will let you look at the values the
other user has entered, and then paste your changes back in if you decide to
make changes"
Copy to Clipboard or Drop Changes
How can I make sure that when a new record is created that it will be not
faced with this message?
below are my AddNew Records & Submit Code
Private Sub cmdAddNew_Click()
Dim sQRY As String
Dim varInput As String
Dim varNewID As Integer
'**************************************
varInput = InputBox("Enter NHS Number", "Add new visit")
If varInput = "" Then Exit Sub
'**************************************
DoCmd.RunSQL "INSERT INTO jez_SWM_Visits (NHSNo) " & _
"VALUES ('" & varInput & "')"
varNewID = DLookup("max(VisitID)", "jez_SWM_Visits")
'**************************************
Me.RecordSource = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " &
_
"jez_SWM_Visits.VisitID = " & varNewID & " "
'**************************************
Call UnLockAll
Me.txtNHSNo.Value = varInput
Me.txtForename.SetFocus
End Sub
Private Sub cmdSubmit_Click()
Dim varResponse As Variant
Dim sQRY As String
Dim rs As DAO.Recordset
Dim intNHSNo As String
'**************************************
' On Error GoTo Err
varResponse = MsgBox("Save Changes?", vbYesNo, cApplicationName)
If varResponse = vbNo Then
Me.Undo
Exit Sub
End If
'**************************************
sQRY = "UPDATE jez_SWM_Visits " & _
"SET [NHSNo] = '" & Me.txtNHSNo & "', [Surname] =
'" & Me.txtSurname & "', [Forename] = '" & Me.txtForename & "', [Gender] = '"
& Me.cboGender & "', [Address1] = '" & _
Me.txtAddress1 & "', [Address2] = '" & Me.
txtAddress2 & "', [Address3] = '" & Me.txtAddress3 & "', [Postcode] = '" & Me.
txtPostcode & "', [Telephone] = '" & _
Me.txtTelephone & "', [DateOfBirth] = '" & Me.
txtDOB & "', [ReferralReasonDescription] = '" & Me.cboReferralRsn & "',
[SourceDescription] = '" & _
Me.cboReferralSource & "', [DateOfReferral] = '"
& Me.txtReferralDate & "', [VisitDate] = '" & Me.txtVisitDate & "',
[OpenorClosed] = '" & Me.chkFinalVist & "'," & _
"[Weight] = '" & Me.txtWeight & "', [Height] = '"
& Me.txtHeight & "', [BMI] = '" & Me.txtBMI & "', [BloodPressure] = '" & Me.
txtBlood & "', [ExerciseLevel] = '" & _
Me.txtExercise & "', [DietLevel] = '" & Me.
txtDiet & "', [SelfEsteem] = '" & Me.txtSelf & "', [WaistSize] = '" & Me.
txtWaist & "', [Comments] = '" & _
Me.txtComments & "', [SessionType] = '" & Me.
cboSessionType & "', [NHSStaffName] = '" & Me.txtStaffName & "', [Arrived] =
'" & Me.cboAttendance & "', " & _
"[ActiveRecord] = -1, [InputBy] = '" &
fOSUserName & "', [InputDate] = '" & VBA.Now & "', [InputFlag] = -1 " & _
"WHERE jez_SWM_Visits.VisitID = Forms!frmVisits!
txtVisitID "
DoCmd.RunSQL sQRY
'**************************************
sQRY = "INSERT INTO jez_SWM_UsersLog ([UserName], [RequestDate]
, [RequestType], [NHSNo], [VisitID])" & _
"VALUES ('" & fOSUserName & "', '" & VBA.Now &
"', 'InsertRecord', '" & Me.txtNHSNo & "', '" & Me.txtVisitID & "')"
DoCmd.RunSQL sQRY
'**************************************
Me.lblBMIInfo.Visible = False
Me.txtDummy.SetFocus
Me.txtNHSNo = ""
Me.txtForename = ""
Me.txtSurname = ""
Me.txtAddress1 = ""
Me.txtAddress2 = ""
Me.txtAddress3 = ""
Me.txtPostcode = ""
Me.txtTelephone = ""
Me.cboGender = ""
Me.txtDOB = ""
Me.cboReferralRsn = ""
Me.cboReferralSource = ""
Me.txtReferralDate = ""
Me.txtVisitDate = ""
Me.chkFinalVist = 0
Me.txtHeight = ""
Me.txtWeight = ""
Me.txtWaist = ""
Me.txtBlood = ""
Me.txtExercise = ""
Me.txtDiet = ""
Me.txtSelf = ""
Me.cboSessionType = ""
Me.txtStaffName = ""
Me.cboAttendance = ""
Me.txtComments = ""
Me.txtInputUser = ""
Me.txtInputDate = ""
Me.chkActive = 0
Me.chkInputFlag = 0
Call LockAll
'DoCmd.OpenForm "frmSplash"
'Form_frmVisits.Visible = False
'Err:
' basError.LogError VBA.Err, VBA.Error$, "Form_frmMain - cmdSubmit_Click()
"
End Sub
Once the user has input all the relevant data into the Combo & TextBox's I
have a submit cmdButton which will then update the relevant recordset in the
table. My problem is that once this has been done and the User clicks Add New
record, after inputting a PatientID there is a pop up box stating
"Write Conflict"
"This recod has been changed by another user since you started editing it. If
you save the record, you will overwrite the changes the other user has made.
Copying the changes to the clipboard will let you look at the values the
other user has entered, and then paste your changes back in if you decide to
make changes"
Copy to Clipboard or Drop Changes
How can I make sure that when a new record is created that it will be not
faced with this message?
below are my AddNew Records & Submit Code
Private Sub cmdAddNew_Click()
Dim sQRY As String
Dim varInput As String
Dim varNewID As Integer
'**************************************
varInput = InputBox("Enter NHS Number", "Add new visit")
If varInput = "" Then Exit Sub
'**************************************
DoCmd.RunSQL "INSERT INTO jez_SWM_Visits (NHSNo) " & _
"VALUES ('" & varInput & "')"
varNewID = DLookup("max(VisitID)", "jez_SWM_Visits")
'**************************************
Me.RecordSource = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " &
_
"jez_SWM_Visits.VisitID = " & varNewID & " "
'**************************************
Call UnLockAll
Me.txtNHSNo.Value = varInput
Me.txtForename.SetFocus
End Sub
Private Sub cmdSubmit_Click()
Dim varResponse As Variant
Dim sQRY As String
Dim rs As DAO.Recordset
Dim intNHSNo As String
'**************************************
' On Error GoTo Err
varResponse = MsgBox("Save Changes?", vbYesNo, cApplicationName)
If varResponse = vbNo Then
Me.Undo
Exit Sub
End If
'**************************************
sQRY = "UPDATE jez_SWM_Visits " & _
"SET [NHSNo] = '" & Me.txtNHSNo & "', [Surname] =
'" & Me.txtSurname & "', [Forename] = '" & Me.txtForename & "', [Gender] = '"
& Me.cboGender & "', [Address1] = '" & _
Me.txtAddress1 & "', [Address2] = '" & Me.
txtAddress2 & "', [Address3] = '" & Me.txtAddress3 & "', [Postcode] = '" & Me.
txtPostcode & "', [Telephone] = '" & _
Me.txtTelephone & "', [DateOfBirth] = '" & Me.
txtDOB & "', [ReferralReasonDescription] = '" & Me.cboReferralRsn & "',
[SourceDescription] = '" & _
Me.cboReferralSource & "', [DateOfReferral] = '"
& Me.txtReferralDate & "', [VisitDate] = '" & Me.txtVisitDate & "',
[OpenorClosed] = '" & Me.chkFinalVist & "'," & _
"[Weight] = '" & Me.txtWeight & "', [Height] = '"
& Me.txtHeight & "', [BMI] = '" & Me.txtBMI & "', [BloodPressure] = '" & Me.
txtBlood & "', [ExerciseLevel] = '" & _
Me.txtExercise & "', [DietLevel] = '" & Me.
txtDiet & "', [SelfEsteem] = '" & Me.txtSelf & "', [WaistSize] = '" & Me.
txtWaist & "', [Comments] = '" & _
Me.txtComments & "', [SessionType] = '" & Me.
cboSessionType & "', [NHSStaffName] = '" & Me.txtStaffName & "', [Arrived] =
'" & Me.cboAttendance & "', " & _
"[ActiveRecord] = -1, [InputBy] = '" &
fOSUserName & "', [InputDate] = '" & VBA.Now & "', [InputFlag] = -1 " & _
"WHERE jez_SWM_Visits.VisitID = Forms!frmVisits!
txtVisitID "
DoCmd.RunSQL sQRY
'**************************************
sQRY = "INSERT INTO jez_SWM_UsersLog ([UserName], [RequestDate]
, [RequestType], [NHSNo], [VisitID])" & _
"VALUES ('" & fOSUserName & "', '" & VBA.Now &
"', 'InsertRecord', '" & Me.txtNHSNo & "', '" & Me.txtVisitID & "')"
DoCmd.RunSQL sQRY
'**************************************
Me.lblBMIInfo.Visible = False
Me.txtDummy.SetFocus
Me.txtNHSNo = ""
Me.txtForename = ""
Me.txtSurname = ""
Me.txtAddress1 = ""
Me.txtAddress2 = ""
Me.txtAddress3 = ""
Me.txtPostcode = ""
Me.txtTelephone = ""
Me.cboGender = ""
Me.txtDOB = ""
Me.cboReferralRsn = ""
Me.cboReferralSource = ""
Me.txtReferralDate = ""
Me.txtVisitDate = ""
Me.chkFinalVist = 0
Me.txtHeight = ""
Me.txtWeight = ""
Me.txtWaist = ""
Me.txtBlood = ""
Me.txtExercise = ""
Me.txtDiet = ""
Me.txtSelf = ""
Me.cboSessionType = ""
Me.txtStaffName = ""
Me.cboAttendance = ""
Me.txtComments = ""
Me.txtInputUser = ""
Me.txtInputDate = ""
Me.chkActive = 0
Me.chkInputFlag = 0
Call LockAll
'DoCmd.OpenForm "frmSplash"
'Form_frmVisits.Visible = False
'Err:
' basError.LogError VBA.Err, VBA.Error$, "Form_frmMain - cmdSubmit_Click()
"
End Sub