473,322 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Creating Audit trail via INSERT statement.

XP/2003

Hello, I need to track any change made to a form by inserting it into a separate table. I found a website (http://www.vb123.com/toolshed/00_access/auditrecords.htm) from another user on here with help on the subject, but when I try to use the code shown I get problems. Basically I want whenever a change is made to my "Customers" table through a form the record being changed will be added to a separate table with the same fieldnames and data named "AuditCustomers". Here is the code I have now:


Private Sub Form_AfterUpdate()
Dim db As Database

Set db = CurrentDb
db.Execute "INSERT INTO [AuditCustomers] " _
& " SELECT * FROM [Customers] WHERE " _
& " [Customers].[CustomerID]=" & Me![CustomerID] & ";"
Set db = Nothing
End Sub


[CustomerID] is the primary key for the "Customers" table, and "AuditCustomers" has no primary key because users may be able to make multiple changes to the same record, which should be shown as multiple records. I'm pretty sure the problem is with the '& " [Customers].[CustomerID]=" & Me![CustomerID] & ";"' line since if I remove it will insert the entire table into the "AuditCustomers" table just fine, so I just need to specify the record being changed.

The error I get with the code shown is "Run Time-error '3061' Too few parameters. Expected 1" I'm not sure if this is just some simple syntax error or I have made some fundamental database error, so any help would be greatly appreciated.
Aug 30 '10 #1

✓ answered by Delerna

Is CustomerID a text field?
if so then

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Dim db As Database
  3.  
  4. Set db = CurrentDb
  5. db.Execute "INSERT INTO [AuditCustomers] " _
  6. & " SELECT * FROM [Customers] WHERE " _
  7. & " [Customers].[CustomerID]='" & Me![CustomerID] & "';"
  8. Set db = Nothing
  9. End Sub
  10.  
Should fix the issue


If not then
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Dim db As Database,strsql as string
  3.  
  4. Set db = CurrentDb
  5. strsql="INSERT INTO [AuditCustomers] " _
  6. & " SELECT * FROM [Customers] WHERE " _
  7. & " [Customers].[CustomerID]=" & Me![CustomerID] & ";"
  8. msgbox strsql
  9.  
  10. 'db.Execute strsql
  11. Set db = Nothing
  12. End Sub
  13.  
might give you a hint as to why it is not working

3 1762
Delerna
1,134 Expert 1GB
Is CustomerID a text field?
if so then

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Dim db As Database
  3.  
  4. Set db = CurrentDb
  5. db.Execute "INSERT INTO [AuditCustomers] " _
  6. & " SELECT * FROM [Customers] WHERE " _
  7. & " [Customers].[CustomerID]='" & Me![CustomerID] & "';"
  8. Set db = Nothing
  9. End Sub
  10.  
Should fix the issue


If not then
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Dim db As Database,strsql as string
  3.  
  4. Set db = CurrentDb
  5. strsql="INSERT INTO [AuditCustomers] " _
  6. & " SELECT * FROM [Customers] WHERE " _
  7. & " [Customers].[CustomerID]=" & Me![CustomerID] & ";"
  8. msgbox strsql
  9.  
  10. 'db.Execute strsql
  11. Set db = Nothing
  12. End Sub
  13.  
might give you a hint as to why it is not working
Aug 31 '10 #2
Oh my gosh wow how stupid of me. Yes it was a text field and yes it needed quotes. I've been stuck on that for the better part of 3 days and couldn't figure it out. Thank you so so much!
Aug 31 '10 #3
Delerna
1,134 Expert 1GB
Youre welcome, its all about reading the error message and understanding what it is telling you.

Without the quotes the query engine thought you meant the field with the name of whatever the contens of Me![CustomerID] was.

Of course there is no field by that name so the error
Too few parameters. Expected 1
was returned.

This error in this context always means something along the lines of my solution is required.
It gets easier to decipher error messages over time ;)
Aug 31 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Me | last post by:
Hi... A much lamented question, I guess.. I'm trying to create a simple audit trail. log the changes to an SQL 2000 table, so that they are written into a mirror table. The entire record, only...
3
by: Me | last post by:
Hi... A much lamented question, I guess.. I'm trying to create a simple audit trail. log the changes to an SQL 2000 table, so that they are written into a mirror table. The entire record, only...
2
by: Zlatko Matić | last post by:
I tried to implement triggers for filling audit-trail table on this way. Everything works fine as long as I don't update the primary key field value. When I try to update PK value, an error...
3
by: Zlatko Matić | last post by:
Hello. I tried to implement audit trail, by making an audit trail table with the following fileds: TableName,FieldName,OldValue,NewValue,UpdateDate,type,UserName. Triggers on each table were...
5
by: alanknipmeyer | last post by:
Dear Sirs, Having previously used other SQL implementations I have grown used to having an audit trail to find when problems occured, the problem I am increasingly finding with Access is that...
6
by: Parag | last post by:
Hello, I have been assigned the task to design the audit trail for the ASP.NET web application. I don't know what the best practices for such audit trails are. Our application one dedicated user...
3
by: hary08 | last post by:
im doing a database for Hospital Admission, I have a log in form which prompt user for a password. The source of log in is to look for the values in my Table tblEmployees and match user name and...
2
by: rockdc1981 | last post by:
I dont it is possible to put this codes together.. I want to prompt the user to save the record and at the same time have a log of audit trail. the codes work out fine separately. Code for Audit...
7
by: cdmonroe | last post by:
I'm implementing someone else's code for creating an audit trail for data edits. I REALLY need this or something similar to track changes made to my the data in my forms. The first time I put it...
6
by: babamc4 | last post by:
I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen Browne's Audit Trail code (thanks to Allen Browne)...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.