Capture and update application user ID into modified_by_user

[KingOfNet]

I'm new to Access and forms. Have worked extensively in Oracle.

Have a navigation subform with Default view = Continuos Forms and using a Recordsource method to populate the values.
The Table has created_by and modified_by columns. I'm capturing the application user ID in a global variable and whenever a record is updated, the same should be captured in modified_by column.
the query I've tried is as



select f1,f2,f3,f4, 'uid' as Modified_By, now as Modified_Date FROM <table> where <conditions>;
say this query returns 3 rows. All the bound textboxes show the correct values
but I'm able to capture and update the required details only for the first record and not for 2nd and 3rd.

any suggestions?

[John_G]

Try using the Before_Update method at the form level. This will fire immediately before the current record is updated, and in it you can set Modified_By = uid and Modified_date = now()

[KingOfNet]

hi John,
Thanks for helping out. I tried using the solution provided by you, but the Private Sub Form_BeforeUpdate(Cancel As Integer) is called only for the first row Update button (and that too only for the first time after the form is loaded) and not for the rest. But then I called this this Form_BeforeUpdate from update_button click event. will change and test the code and will revert back

[KingOfNet]

it's giving me write conflict error . Cause this error itself, I've changed my "dbs.Execute StrSql" approach and have decided to go for a continuos form and recordsource method.

[KingOfNet]

Managed to solve the issue.