Access - Write to an audit log

[Pumpk1n]

I have a database called "Vendor" in which I need to track who has 'clicked' buttons to perform an event. The table is called "Audit Log" and I'm stuck on how to write the code to insert into a table. Here's what I have so far but I'm having issues on the "Insert" function. Can you help? I've "borrowed" the code and am not able to figure out how to troubleshoot. Thanks in advance!


Private Sub Command505_Click()
Dim strLocation As String
Dim db As DAO.Database
Dim sql As String
Set db = CurrentDb()
Me.Modified_by = Environ("USERNAME")
Me.Modified_on = Format(Now(), "yyyy-MM-dd hh:mm:ss")
sql = "Insert into AuditLog Values('" & Modified_by & "', '" & Modified_on & "', '" "NegativeBalance"');"
db.Execute sql
End Sub

 

[ranman256]

close:

Private Sub Command505_Click()
Dim sql As String


Me.Modified_by = Environ("USERNAME")
Me.Modified_on = Format(Now(), "yyyy-MM-dd hh:mm:ss")
sql = "Insert into AuditLog Values('" & Modified_by & "', #" & Modified_on & "#, '" "NegativeBalance"');"
docmd.RunSql sql
End Sub

 

[Pumpk1n]

I received an error "Compile error: Method or data member not found" and the debugger is stopping on the line of code where user name is defined "ME.Modified_by..." I added the dim statement back in for the Modified_by as string, but that didn't resolve it.

close:

Private Sub Command505_Click()
Dim sql As String


==>Me.Modified_by = Environ("USERNAME")
Me.Modified_on = Format(Now(), "yyyy-MM-dd hh:mm:ss")
sql = "Insert into AuditLog Values('" & Modified_by & "', #" & Modified_on & "#, '" "NegativeBalance"');"
docmd.RunSql sql
End Sub

 

[xenou]

Modified_on and Modified_by are supposed to be control names on your form, often bound to fields with the same names. You refer to these controls as object on the form with the syntax Me.TheControlName. If you borrowed the code, then maybe you didn't change the names properly.

I think what ranman did was just put the # marks around the date strings. You should also use Me.ControlName syntax there too, though:
insert into AuditLog Values('" & Me.Modified_by & "', #" & Me.Modified_on

 

[Pumpk1n]

Ah, I see. That makes sense. My catch is that I don't have a form -- I am monitoring who is clicking a button which updates database. Can I write to an audit log without a form?

Modified_on and Modified_by are supposed to be control names on your form, often bound to fields with the same names. You refer to these controls as object on the form with the syntax Me.TheControlName. If you borrowed the code, then maybe you didn't change the names properly.

I think what ranman did was just put the # marks around the date strings. You should also use Me.ControlName syntax there too, though:
insert into AuditLog Values('" & Me.Modified_by & "', #" & Me.Modified_on

 

[xenou]

Hi, where is the button if it is not on a form?

 

[Pumpk1n]

Yeah, I had a navigation form with buttons for users to update a database and run reports and wasn't thinking that through. I added the fields to the form and put them behind a label and added the auditlog to the data properties on that form. the error message I get now is a run-time error '3346' "number of query values and destination fields are not the same.

SQL Code is now:
Private Sub Command505_Click()
Dim sql As String
Dim strLocation As String
Me.Modified_by = Environ("USERNAME")
Me.Modified_on = Format(Now(), "yyyy-MM-dd hh:mm:ss")
sql = "Insert into AuditLog Values('" & Me.Modified_by & "', '" & Me.Modified_on & "', '" & NegativeBalance & "');"
DoCmd.RunSQL sql
End Sub

Hi, where is the button if it is not on a form?

 

[xenou]

If audit log has more columns and you aren't inserting values for all of the columns you need a column list:

Insert into AuditLog (Modified_by, Modified_on, NegativeBalance) 
values ('Kermit', #1/1/2017#, -100.00)

 

[Pumpk1n]

If audit log has more columns and you aren't inserting values for all of the columns you need a column list:

Insert into AuditLog (Modified_by, Modified_on, NegativeBalance) 
values ('Kermit', #1/1/2017#, -100.00)

This errors out on the sql line.
Private Sub Command505_Click()
Dim sql As String
Dim strLocation As String
Me.Modified_by = Environ("USERNAME")
Me.Modified_on = Format(Now(), "yyyy-MM-dd hh:mm:ss")
sql = Insert into AuditLog (modified_by, Modified_on) Values('" & Me.Modified_by & "', '" & Me.Modified_on & "', '" & NegativeBalance & "');"

DoCmd.RunSQL sql
End Sub

 

[xenou]

Hi,
1) what is the error message.
2) display your sql using a print statement so we can see the actual sql

sql = Insert into AuditLog (modified_by, Modified_on) Values('" & Me.Modified_by & "', '" & Me.Modified_on & "', '" & NegativeBalance & "');"
[COLOR="#B22222"]Debug.Print sql[/COLOR]

Note that from what I can see you have two column names in your column list but three values in your value list so that's a syntax error. If you are inserting three values you must have three columns listed.