Search code examples
vbams-accesscrudoltp

MS Access get row ID of Update or intercept Update

Short Version:

I have an access table that has a Yes/No column called [Accepted] that I want flipped to "No" anytime an update happens to that row. How can I do this? I've been working with Form_AfterUpdate in VBA but no luck so far.

Long Version:

I'm trying to get CRUD working with my OLTP back-end SQL SERVER using Access. I'm exploring the possibility of using Access as a simple front end for office work but want it to jive with our larger data operation. I've designed a Main Form with Edit, Cancel, Save, and Refresh buttons. In the body of that form I've embedded a Sub-form containing a datasheet. My Edit button unlocks the sheet in the Sub-form for insert and delete. This Sub-form is bound to a local access table which serves as a cash. The Refresh button rebases my Access table/cache with the OLTP server and the Save button writes any records in my Access table/cache marked as [Accepted]=No to the OLTP server and then refreshes.

Any suggestions on how I could better get CRUD working with OLTP and Access are appreciated as that is the full goal here. However, I think I'm close on my own if I can figure out how to intercept or get the record ID of updated rows.

Solution

  • Use the Form_BeforeUpdate event:

    Me!Accepted.Value = False