Write Conflict Dialog (1 Viewer)

[doco]

With rs
    .Edit
    .Fields("full_name").Value = szFullName
    .Fields("issue_date").Value = szIssueDate
    .Fields("eff_from_date").Value = szFromDate
    .Fields("eff_to_date").Value = szToDate
    .Fields("status").Value = szStatus
    .Fields("type").Value = szType
    .Fields("paid").Value = CInt(Me.paid.Value)
    .Fields("dob").Value = Nz(Me.dob.Value, "")
    .Fields("revoked").Value = CInt(Me.revoked.Value)
    .Fields("revocation_date").Value = Nz(Me.revocation_date.Value, 0)
    .Fields("revoke_reason").Value = Nz(Me.revoke_reason.Value, "")
    .Fields("notes").Value = Nz(Me.notes.Value, "")
    .Fields("wcso_staff").Value = Nz(Me.wcso_staff.Value, "")
    .Fields("case_no").Value = Nz(Me.case_no.Value, "")
    .Update
    .Close
End With

All data has been verified correct before this code is run. However, Access displays a 'Write Conflict' dialog. It would be really good to not have this happen. How would one go about keeping the PITA from appearing?

TIA

 

[pbaldy]

Is the form bound to the same table the recordset is updating? That's often the culprit.

 

[doco]

Yes it is. I have tried

    Me.Dirty = False

Which creates another error dialog.

Is there a work around?

 

[pbaldy]

Why is it set up that way? I would use a recordset or a bound form, not both. That said, saving the record should help. What error does that throw?

 

[mdlueck]

I have forced changes back into the table where I have editable forms bound to tables. On each editable field control I have:

Private Sub addpartrecordflg_AfterUpdate()

  'This will force the UI change back into the table
  If Me.Dirty Then
    Me.Dirty = False
  End If

End Sub

It seems like you want to manipulate many fields of the record - sometimes reading from table fields, some times from variables, etc... databases typically are record level based, not each individual field of one record. They, after all, are not spreadsheets! I prefer to code that I check out a record (SELECT), bring it into an unbound edit form, then when I wish to check back in the current values, I run through validation to insure all values are correct - if not then stop and flag the bad field(s) in error via a red background - and then once properly validated transport the field values back into VBA variables to go back to the database table via a SQL UPDATE statement. I perform my UPDATE based on both the record's unique ID field and the LastSaveTimestamp of the row, guaranteeing that the UPDATE is not trouncing someone else's update they made while I had the record in "edit mode". I thus totally avoid such headaches as you are faced with presently.

By forcing changes back into the table on each field, once you move off of the field, the AfterUpdate fires, forces all of the current values back into the table... and suddenly it is "too late" for business rule validation.

 

[doco]

I have forced changes back into the table where I have editable forms bound to tables. On each editable field control I have:

Private Sub addpartrecordflg_AfterUpdate()

  'This will force the UI change back into the table
  If Me.Dirty Then
    Me.Dirty = False
  End If

End Sub

It seems like you want to manipulate many fields of the record - sometimes reading from table fields, some times from variables, etc... databases typically are record level based, not each individual field of one record. They, after all, are not spreadsheets! I prefer to code that I check out a record (SELECT), bring it into an unbound edit form, then when I wish to check back in the current values, I run through validation to insure all values are correct - if not then stop and flag the bad field(s) in error via a red background - and then once properly validated transport the field values back into VBA variables to go back to the database table via a SQL UPDATE statement. I perform my UPDATE based on both the record's unique ID field and the LastSaveTimestamp of the row, guaranteeing that the UPDATE is not trouncing someone else's update they made while I had the record in "edit mode". I thus totally avoid such headaches as you are faced with presently.

By forcing changes back into the table on each field, once you move off of the field, the AfterUpdate fires, forces all of the current values back into the table... and suddenly it is "too late" for business rule validation.

What you say is quite true. I have painted myself into a corner by trying to use a single form to accomplish three different tasks.: Read Only from one instance, Edit (as result of filter) from another, Add New from yet another.


Going to have to pull my horns in and redesign...

 

[David R]

All three of those functions usually work quite fine from a bound form. Are you having to manually/programatically change the recordsource for each function?

 

[doco]

All three of those functions usually work quite fine from a bound form. Are you having to manually/programatically change the recordsource for each function?

No. I had some code that would perform various functions based on whichever the user would choose. I would use the tag property and openargs property as flags. depending on the flag Read, Edit, Add would be determined. Along with controls being locked/unlocked. etc.

I'm going to take the unbound form approach - I didn't think of it originally.