MS Access Form/ComboBox VBA Addt'l SQL for AfterUpdate Event
Posted on 2006-05-15
I continue to get a write conflict error when attempting to update a record, via SQL, on the AfterUpdate command for a combo box.
I have a form that is used to update records (a search form is used prior to the "results" form). This results form is setup as a Continuous Form as the search results may result in multiple records, thus allowing the user to update multiple records at one time on one form.
One of the fields, CmboStatus, is setup as a ComboBox. The ControlSource property is set to the Status_ID field for that record. This is setup as a combobox in order to limit the users choices for edits, thus the Row Source is SQL that pulls from the reference table for Status.
I've added some VBA to the AfterUpdate event, such that if the user changes the status for a record to certain ones, then SQL is run to also update a date field. However, when this occurs, I continue to get a This record has been changed by another user... Do you want to Drop Changes, etc.
I'm sure the order of events is playing a role as after clicking on Drop Changes, the SQL runs as the date field successfully updates, but the Status field reverts back to the original value prior to the change. I've tried a few variations to no avail.
Here's the VBA
Private Sub Cmbo_Status_AfterUpdate()
If Cmbo_Status = 7 Or Cmbo_Status = 2 Then
strCompDate = "Update tbl_DT_DocTrack " & _
" set Completed_Date = '" & Format(Now(), "MM/DD/YYYY") & "' " & _
" Where Doc_ID = " & Doc_ID
Any help would be appreciated!