Drop Changes or Copy to clipboard

[TedMosby]

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 StringDim varInput As StringDim 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.SetFocusEnd Sub


Code:

Private Sub cmdSubmit_Click()Dim varResponse As VariantDim sQRY As StringDim rs As DAO.RecordsetDim 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

[CreganTur]

Welcome to the forum- it's always good to see new members!

Do you have any Form Load settings that are adding in a default value to one of your table fields? I've had the same message appear when I programatically set a default value and then try to save the table.

Also, your code snippets are illegible becasue it's all smashed together. In the future when you post code, please wrap it in VBA tags (click the green VBA button). This will format the code according to VBIDE and make it easier to read.

[TedMosby]

No problem, I'll sort the code out :)
The only thing I have in the FormLoad is a routine to make sure the fields in the form are locked so people cant just type anything in at any point