How do I avoid writing conflicts here? 1

[mrdod]

To anyone -
I have a form where scrap is entered into a listview control on that form. I have made it so if the user dbl clicks the control he/she can edit the information. I can update the information no problem. My problem comes when I go to close the form I get a message saying Write Conflict and someone else may be editing it. I'm the only one on there so that's not the problem. My code is a bit patchy as I've been trying to move stuff around to avoid this so please be gentle with me.

strcompname = Environ("COMPUTERNAME") & "scrap"
strSQL = "UPDATE " & strcompname & " SET[Date Entered]=#" & dtmdate & "#,"
strSQL = strSQL & "[Time Entered]=#" & dtmtime & "#,[Name]=""" & strname & ""","
strSQL = strSQL & "[Shift]='" & strshift & "',[Area]='" & strarea & "',"
strSQL = strSQL & "[Part Number]='" & strpartnumber & "',[WO Number]='" & strwonumber & "',"
strSQL = strSQL & "[Category]='" & strdefectgroup & "',[Reason]='" & strdefectreason & "',"
strSQL = strSQL & "[Qty]=" & lngqty & ""
strSQL = strSQL & " WHERE([Incr]=" & intincr & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True





strSQL = "SELECT * FROM " & strcompname & ";"

On Error GoTo ErrorHandler

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

With Form_frmproduction.lviewscrap
'Set ListView style
.View = lvwReport
'This is not supported by ListView 5
.GridLines = False
.FullRowSelect = True
.GridLines = False
.FullRowSelect = True
.AllowColumnReorder = True
'Clear Header and ListItems
.ListItems.Clear
End With

' Add items and subitems to list control.

rs.MoveFirst
Do Until rs.EOF
Set lstItem = Form_frmproduction.lviewscrap.ListItems.Add()
lstItem.Text = Nz(rs!Incr)
lstItem.SubItems(1) = Nz(rs![Date entered])
lstItem.SubItems(2) = Nz(rs![Time Entered])
lstItem.SubItems(3) = Nz(rs![Name])
lstItem.SubItems(4) = Nz(rs![Shift])
lstItem.SubItems(5) = Nz(rs![Area])
lstItem.SubItems(6) = Nz(rs![Part Number])
lstItem.SubItems(7) = Nz(rs![WO Number])
lstItem.SubItems(8) = Nz(rs![Category])
lstItem.SubItems(9) = Nz(rs![Reason])
lstItem.SubItems(10) = Nz(rs![Qty])
lstItem.SubItems(11) = Nz(rs![Comments])


'Next row
rs.MoveNext
Loop
'close recordset
rs.Close
Set rs = Nothing
DoCmd.Echo True
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 3021 Then ' no current record
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

DoCmd.Close acForm, "frmscrapchange" 'This is where I'm getting the Write Conflicts

Any help would be WONDERFUL!!!

Thanks

 

[lameid]

Sounds like your form's recordsource is the same as the table being updated and by changeing the record with an update statement when it tries to close the form (and hence save the record) you are gettting a write conflict.

If this is the cause you should consider updating the field on the form directly or making the form on unbound which may break a lot more things.

 

[ZmrAbdulla]

May be because your both SQLs are defined with same name."strSQL". Try changing one wiith another name.

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[mrdod]

After doing some looking I found a couple threads saying something about having write conflicts with bound forms. I did make the form unbound and it works fine. Thanks for the responses. I'm pretty much a noob here so what do you mean when you say update the field on the form itself?

 

[lameid]

I mean if the form is BOUND then you could update the control on the form instead of the update statment notice the <> are part of a value to replace.

Me!<FieldOrControlName> = <ValueToSetTo>

Or...

Me![Date Entered] = dtmdate

Naturally you would have to update all the fields or related controls that are in your update statement this way.

I generally prefer bound forms for anything involving entering data in the table and unbound for special processes. Either this bound form solution or making the form unbound will solve the write conflict. I was assuming that you had it bound for a reason.

 

[mrdod]

I only bound it because it was easier to fill. I typically use the dlookup function when filling a form. There may be a better way but I wouldn't have a clue how. So yeah I unbound it and it works fine. I appreciate your help a great deal.
And have a pink thing on me.