Getting the Write Conflict Error

[mrsgwen90]

I am trying to update a record using access as the front end and VB 6.0 as the backend. I am using a SQL statement to accomplish this. But I am getting the "Write Conflict Error". I have tried adding the 'Timestamp field as someone suggested but that isn't working. Here is my code. Please advise

Private Sub cmdAddRec_Click()
On Error GoTo Err_cmdAddRec_Click
 
 
Dim rstTrans As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String
Dim Msg, Response
 
 
Msg = "Do you want to update another record?"
 
Sqlstmt = "SELECT * FROM dbo_tbl_HR_Shuttle WHERE VehicleID = " & Form_Daily_Vehicle_Tic_Sheet_Data_Form!VehicleID
 
rstTrans.Open Sqlstmt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 
rstTrans!DateModified = Now()
rstTrans!UpdateUser = gUser
 
 
rstTrans.Update
'DoCmd.Save
Response = MsgBox(Msg, vbYesNo)
If Response = vbYes Then
'MsgBox ("result is yes")
DoCmd.Close
DoCmd.OpenForm "Daily_Vehicle_Tic_Sheet_Data_Form"
Else
    DoCmd.Close
End If
rstTrans.Close
 
Exit_cmdAddRec_Click:
    Exit Sub
 
Err_cmdAddRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRec_Click
 
End Sub

[NeoPa]

Please look in POSTING GUIDELINES: Please read carefully before posting to a forum and consider rewording your question or adding extra relevant information. As it stands I'm afraid, it's too unclear.

ADMIN.

NB. VB6 is a programming language (can't be a back-end)
We are not a code debugging service.

[Jim Doherty]

I am trying to update a record using access as the front end and VB 6.0 as the backend. I am using a SQL statement to accomplish this. But I am getting the "Write Conflict Error". I have tried adding the 'Timestamp field as someone suggested but that isn't working. Here is my code. Please advise

Private Sub cmdAddRec_Click()
On Error GoTo Err_cmdAddRec_Click
 
 
Dim rstTrans As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String
Dim Msg, Response
 
 
Msg = "Do you want to update another record?"
 
Sqlstmt = "SELECT * FROM dbo_tbl_HR_Shuttle WHERE VehicleID = " & Form_Daily_Vehicle_Tic_Sheet_Data_Form!VehicleID
 
rstTrans.Open Sqlstmt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 
rstTrans!DateModified = Now()
rstTrans!UpdateUser = gUser
 
 
rstTrans.Update
'DoCmd.Save
Response = MsgBox(Msg, vbYesNo)
If Response = vbYes Then
'MsgBox ("result is yes")
DoCmd.Close
DoCmd.OpenForm "Daily_Vehicle_Tic_Sheet_Data_Form"
Else
DoCmd.Close
End If
rstTrans.Close
 
Exit_cmdAddRec_Click:
Exit Sub
 
Err_cmdAddRec_Click:
MsgBox Err.Description
Resume Exit_cmdAddRec_Click
 
End Sub

Given you have posted your program flow I'm not going to debug alter it per se you obviously have it in that order for a particular reason I assume?

My guess on this based on your post is that the form on which your cmdAddRec button is mounted has its recordsource bound to the table and that you are asking to open THE SAME TABLE as a second recordset in code. In other words you have TWO datasets open derived from the same table. if this is 'not bound' then you must have at least the same table open in memory twice.

The 'write conflict' will be occurring IF at the point at which the user (or 'you' as its in in development at this stage) clicks the command button, and the record is in an 'EDIT' state (look at the record selector left hand side of the form, if you see the pencil then it most certainly is) the row that you wish to update will be LOCKED and as such non editable until such time as you SAVE the current record BEFORE opening the second recordset in code.

If I am correct in my assumption then you will need to save the record BEFORE opening the second recordset in code.

The form from which you have clicked the command button will be in what is referred to as a DIRTY state ie: 'unsaved' a condition that will be TRUE. You need to reverse that stateto FALSE this in turn saves the record if you understand me.

The line of code you need at the appropriate point and I leave that up to you to decide its placement in your logic is this

 If Me.Dirty = True Then Me.Dirty = False

Regards

Jim