Full Version: Audit Status Change In A Subform
UtterAccess Forums > Microsoft® Access > Access Forms
Knuckles
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.
Knuckles
blobbles78
Funny, I did just this on something I was working on!
On the After Update, enter code like the following:
CODE
    CurrentDb.Execute "INSERT INTO AuditTableName (TaskID, Status, ChangedOn, ChangedBy) " & _
        "VALUES (" & Me.TaskID & ",'" & Me.Status & "',#" & Date() & "#,'" & Environ("Username") & "')"
    Me.SubFormName.Form.Requery

Obviously you may want to change TaskID and Status to whatever info you want to store...
Knuckles
B,
'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.
Knuckles
Knuckles
Pere,
took what you wrote and plugged it in. Worked like a charm! Thank you sir.
Knuckles
Knuckles
Pere,
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) & "')"
Forms!frmTask.frmTaskStatusChanges.Requery
Knuckles
Pere,
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) & "')"
Knuckles
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.
Knuckles
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.