Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


MS Access Form/ComboBox VBA Addt'l SQL for AfterUpdate Event

Posted on 2006-05-15
Medium Priority
Last Modified: 2008-03-03
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
    DoCmd.SetWarnings False
    DoCmd.RunSQL strCompDate
    DoCmd.SetWarnings True

End If

End Sub

Any help would be appreciated!

Question by:bkapla1
  • 3
  • 2
LVL 34

Expert Comment

ID: 16681485
First things first, is "Completed_Date" actaully a text field?

If it is a DATE/ DATETIME field, then you really should be using:

    strCompDate = "Update tbl_DT_DocTrack " & _
                " set Completed_Date = #" & Format(Now(), "YYYY-MM-DD") & "# " & _
                " Where Doc_ID = " & Doc_ID


Author Comment

ID: 16681512
Well, it's a date field from the table, but the table is actually a MS SQL Server table, so the format I used with the single quotes vs. the # sign works just fine.  If it was an Access table, then yes, I would have used the # sign.


Author Comment

ID: 16681641
Ok, I answered this question myself.

I changed the VBA to the Form AfterUpdate event vs. the Status Field AfterUpdate event.  I also added a requery to this form event.

The code is now working successfully.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 34

Expert Comment

ID: 16681931
Please post a Q in CS stating you solved your own Q and requesting a PAQ.

Mods: No objections


Author Comment

ID: 16681952
Thanks Dave... already done!

Accepted Solution

GranMod earned 0 total points
ID: 16682113
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question