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

Where in this code is the error appearing? Are the tables connected to the database?

One problem you may be having here is that you are using DAO with SQL Server, which does not support DAO, only ADO. Normally with a SQL Server back end, I would do something like this:

Dim rs As New ADODB.Recordset
Dim sSQL As String

sSQL = "INSERT INTO jez_SWM_Visits (NHSNo) " & _
       "VALUES ('" & varInput & "')"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

This assumes the tables are connected to Acces, which I am assuming since you are still able to use DAO somewhat, so there is no ADODB remote provider statement.

The act of opening the recordset does the insert, so it acts the same way as DoCmd.RunSQL

Anyway, without knowing where in the code you are getting this error, its ahrd to say. There are 3 transactions involving saves.


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks,

Currently the code falls over on this line

Me.RecordSource = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " &
_
     "jez_SWM_Visits.VisitID = " & varNewID & " "

when inputing another record.

My tables are connected to Access through using Link Tables

trying the ADO code you wrote that has worked great :-)

How would be best to write all the other unbound objects to the DB now as the current code I have doesnt send the data to the table now?

First, I would try this:

sSQL = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " & _
       "jez_SWM_Visits.VisitID = " & varNewID

Me.RecordSource = sSQL

Note that you don't need the closing space as in your original string.
Also, try not to build strings at runtime. Access doesn't like that. Build the string in a variable, and then run the variable.

Did that help?





mmcdonal

Look it up at: http://wrox.books24x7.com

Excellent, thats worked far better. I understand what you have said.
So this is now my AddNew_Click

Private Sub cmdAddNew_Click()
Dim rs As New ADODB.Recordset
Dim varInput As String
Dim varNewID As Integer
Dim sSQL As String

    varInput = InputBox("Enter NHS Number", "Add new visit")
    If varInput = "" Then Exit Sub

            sSQL = "INSERT INTO jez_SWM_Visits (NHSNo) " & _
                    "VALUES ('" & varInput & "')"
    varNewID = DLookup("max(VisitID)", "jez_SWM_Visits")
    Set rs = New ADODB.Recordset
    rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

            sSQL = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " & _
                    "jez_SWM_Visits.VisitID = " & varNewID
    Me.RecordSource = sSQL

    Call UnLockAll
    Me.txtNHSNo.Value = varInput
    Me.txtForename.SetFocus
End Sub

It seems to run faster aswell.

How would it be better to commit the other fields within the form to the record as in my Submit_Click?