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 and also all txtBox & cbo's are bound apartfrom txtNHSNo

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
Code:
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