Drop Changes or Copy to clipboard

[JezLisle]

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

 

[boblarson]

Sounds like you may be using the SQL to save, but that the form is already bound and therein lies the problem. Is the form bound or unbound?

 

[JezLisle]

The form is bound, apart from the txtNHSNo.

What would be best way to get around this problem?

 

[boblarson]

The form is bound, apart from the txtNHSNo.

What would be best way to get around this problem?

Either use an unbound form or don't use the update query and just save the record in the form.

 

[JezLisle]

How would I update the record to the DB if I didnt use the UPDATE Query?