Change Log Table

[JeffreyCarlino]

I created a database to track projects for my company. I have a table called projects and I want to be able to create a change log so that any time any employee makes a change to the table with in the "Projectsform", it gets log into another table called "ProjectsChangeLog". Below is the code i wrote that I duplicated from Access learning zone. But the code doesn't work. please note that I created the project change log directly from the project table and the project ID to a regular number because you can't have more than one of the same auto generated ID in the table.

DoCmd.RunSQL "INSERT INTO ProjectsChangeLog SELECT * FROM ProjectsForm " & _
"WHERE ProjectID=" & ProjectID

I keep getting a runtime error 3075, syntax error (missing operator) and query expression 'ProjectID='

this is driving me crazy because the instructor wrote the same code and at work on their database. I'm not sure what the problem is. Anyone's help is much appreciated

 

[plog]

SQL and VBA are different langauages. You are trying to use VBA to write SQL and now there's problem with the SQL but your'e still trying to look at the VBA. You need to get to the SQL.

So, instead of writing the SQL inline with your .RunSQL call, write your SQL in a variable. Then spit out the contents of that variable so you can actually see what the SQL says. Right now you are looking at your SQL behind a frosted shower mirror and just hoping you can make out what is wrong with it. Actually find out what is the SQL that you are creating is.

 

[Pat Hartman]

The syntax may be incorrect. When you are building strings in VBA, your best option is to build the string into a variable. That way you can look at the string after it is built so you can see the error and even if you don't see the error, you can print the string to the debug window and then copy it into the QBE and run it and let Access give you a better error message.

Whoops, looks like plog beat me to it

 

[ebs17]

In addition, you should consider two main points:

1) ... "WHERE ProjectID=" & ProjectID
Depending on the data type of the table field, the value "from outside" must be specifically formatted in order to be accepted. So what data types are you using?

2) INSERT INTO ProjectsChangeLog SELECT * ...
That's very vague. An unknown number of fields in the source table is fired onto an unknown list of fields in the target table. In addition, it is expected that correct assignments are made in each case, which becomes problematic in the case of name inconsistencies.
It is professional to formulate exact assignments yourself and thus to explicitly list the fields used.

INSERT INTO
   ProjectsChangeLog (
      A,
      B,
      C
   )
SELECT
   AA,
   BB,
   CC
FROM
   TableX

 

[jdraw]

You may get some insight from this thread on Best Use of Macros. I have included a demo database on Data macros showing how it could be used as an Audit Table/Log.

 

[adhoustonj]

I use something like this

Private Sub
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()
strSQL = "INSERT INTO ProjectsChangeLog ( ID, EditDate, ProjectID, RecordID, [User], Field, BeforeValue, AfterValue ) " _
& "SELECT ProjectsForm.ID, ProjectsForm.EditDate, ProjectsForm.ProjectID, ProjectsForm.RecordID, ProjectsForm.User, ProjectsForm.Field, ProjectsForm.BeforeValue, ProjectsForm.AfterValue " _
& "FROM ProjectsForm " _
& "WHERE (((ProjectsForm.ProjectID)=" & ProjectID & "))"
db.Execute strSQL

Set db = Nothing
End Sub

But if you want to capture as form is changed then you can build a function and call it from your form After Update events.

If Nz(varBefore) <> Nz(varAfter) Then
                 'Build INSERT INTO statement.
                 strSQL = "INSERT INTO " _
                    & "ProjectChangeLog (EditDate, ProjectID, RecordID, User, Field, BeforeValue, AfterValue) " _
                    & "VALUES (Now(), '" & ProjectID & "', '" & recordid & "', '" & fosUserName() & "', '" & .name & "', '" & varBefore & "', '" & varAfter & "')"

Check if old value equals new value

Private Sub Cust_AfterUpdate()
Dim id As LongPtr
Dim oldv As String, newv As String

If Me.Cust.OldValue = 0 Then
    oldv = "no"
Else
    oldv = "yes"
End If
If Me.Cust = 0 Then
    newv = "no"
Else
newv = "yes"
End If

If oldv <> newv Then
    Call buaudit("ProjectsForm", Me.ProjectID, "Customer", oldv, newv)
End If