Writing conflict when updating records 1

[Shiner83]

Hello, I have a form that lists towns with a control named Reference Date. When I change the Reference Date, a message box appear, giving the choice to apply this date to every town, or just on the current one. My code works, but the problem I have is when I change town, I get a Writing conflict, asking me to Save, Copy to clipboard or cancel. When I click Save everything works fine, but I don't want this message box appearing everytime! I have this same problem in others forms, for the same reason.

Here's the code I use:


Private Sub txtRefDate_AfterUpdate()
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim choice As Integer

choice = MsgBox("Do you want to apply this date to every town?", vbOKCancel)
If choice = 1 Then
Set db = CurrentDb
Set rs = db.OpenRecordset("Table_Town")
rs.MoveFirst
Do While Not rs.EOF
With rs
.Edit
!RefDate= Me.txtRefDate.value
.Update

.MoveNext
End With
Loop


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End If

End Sub

 

[PHV]

Before opening the Recordset, you may try this:
DoCmd.RunCommand acCmdSaveRecord

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shiner83]

Perfect it now works just fine!

Should I use the DoCmd.RunCommand acCmdSaveRecord command everytime I open a Recordset? I haven't found problems elsewhere where I didn't saved the record, but they might be some hidden problems if I don't...

 

[RoyVidar]

I think that when you have unsaved changes in a form bound to the table, and opening the same table in your recordset, then you might be perceived as two users manipulating the same data - > write conflict, so in such case, either a save or undo of the form record often does wonders;-)

BTW - Welcome to Tek-Tips!

To get the most out of the membership, have a look at one of the faq's in PHV's signature. And for Access related questions, have a look at the "pure" Access fora. Some of them found in the Related Forums box at right.

Good Luck!

Roy-Vidar

 

[Shiner83]

Thanks!

These boards have been so useful! I've been lurking for about a month and I was able to get answers to almost all my questions with the search function. I would have had much more troubles with the project i was engaged for if I didn't find this place

As for the best place to post; if I have Access questions that needs VBA coding, should I post on the access forum or the VBA one?

 

[RoyVidar]

I'd say Access VBA (forum705) - No disrespect for the members frequenting this forum, but the Access VBA forum is "designed" for that;-)

Then, there are seven different Access fora:
Forms (forum702)
Access VBA (forum705)
Queries and SQL (forum701)
Reports (forum703)
Tables and Rel. (forum700)
Other topics (forum181)
ADP (forum958)

- if I've got the numbers right;-)

- but also, for forms coding -> forms forum, report coding -> reports...

Roy-Vidar