Full Version: Audit Status Change In A Subform
UtterAccess Forums > Microsoft® Access > Access Forms
I am trying to display a subform that tracks every time a user changes the status (combo box) on the main form. I have a seperate table with 4 fields.
Task ID, Status,ChangedOn and ChangedBy
The TaskId is my link criteria.
I was not able to write a piece of code that would populate the table on the update event of the combo box. I also want to keep all the updates and not just display the last update.
On the after update of the combo box I want to send the updated status to the audit table. I will also capture the who and when automatically. Then I need the subform to requery to display it. I'm thinking it might be an uodate query.?
Any help would be appreciated.
Funny, I did just this on something I was working on!
On the After Update, enter code like the following:
    CurrentDb.Execute "INSERT INTO AuditTableName (TaskID, Status, ChangedOn, ChangedBy) " & _
        "VALUES (" & Me.TaskID & ",'" & Me.Status & "',#" & Date() & "#,'" & Environ("Username") & "')"

Obviously you may want to change TaskID and Status to whatever info you want to store...
'll give that a try. I wound up creating a couple of hidden fields on the form to capture the data after update then I put together an append query pulling from those fields. Of course I'd rather not start cluttering up my forms with hidden fields. Thanks for the help.
took what you wrote and plugged it in. Worked like a charm! Thank you sir.
Actually, made 1 change because I don't know what ENVIRON is or how to use it. I'm guessing it's a system thing.
CurrentDb.Execute "INSERT INTO tblTaskStatusChanges (TaskID, Status, TaskStatusChangedOn, TaskStatusChangedBy) " & _
"VALUES (" & Me.TaskID & ",'" & Me.cboTaskStatus & "',#" & Now & "#,'" & Forms!frmlogin!cboEmp.Column(0) & "')"
This worked on 1 form but not on another similar form. I created these 4 temp fields and can see that they populate on the after update event but neither one of these SQL statements will update the table. I'm not getting any error messages but the table is not being updated. Did I write this correctly?
Me.txtTempDate = Me.txtduedate
Me.txtTempOn = Now
Me.txtTempBy = Forms!frmlogin!cboEmp.Column(0)
Me.txtTempProjectID = Me.txtProjectID
DoCmd.RunSQL ("INSERT INTO tblProjectDueDateChanges ( ProjectID, Date, DateChangedOn, DateChangedBy )SELECT Forms!frmProject!projectID AS ProjectID, Forms!frmproject!txtDueDate AS Date, now() AS DateChangedOn, Forms!frmlogin!cboEmp.Column(0) AS DateChangedBy")
' CurrentDb.Execute "INSERT INTO tblProjectDueDateChanges (ProjectID, Date, DateChangedOn, DateChangedBy) " & _
' "VALUES (" & Me.txtProjectID & ",'" & Me.txtduedate & "',#" & Now & "#,'" & Forms!frmlogin!cboEmp.Column(0) & "')"
Hi Blobbles,
Just realized I kept replying to you but I had you confused with someone else. Anyway, Still trying to work my way through this. Will repost as "Append query won't append". Attaching a word doc just in case you're interested. Thanks.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.