CODEWITHSUNDEEP
ms-accessvba
user3105414
user3105414

Reputation: 31

Creating a Change Log using VBA Insert Into

I am trying to create a change log anytime a field is changed. I want it to record the property, the manager, and the date it was changed. I looked up INSERT INTO and followed the directions and came up with this code, but I get an error.

Private Sub Manager_AfterUpdate()

INSERT INTO tbl_ManagerChangeLog (Property, NewMan, [Date Change])
    VALUES (Me.ID, Me.Manager, DATE())

End Sub

There error I get reads, "Compile Error: Expected: End of statement"

Any help is appreciated.

Here is final code that worked for me. Thank you for all of the help. Private Sub Manager_Change()

Dim dbs As Database, PropID As Integer, ManNam As String, ChangeDate As Date

    Set dbs = CurrentDb()
    PropID = Me.ID
    ManNam = Me.Manager
    ChangeDate = Date

    dbs.Execute " INSERT INTO tbl_ManagerChangeLog " & "(Property, NewMan, DateChange)VALUES " & "(" & PropID & ", " & ManNam & ", #" & ChangeDate & "#);"

End Sub

Upvotes: 0

Views: 186

Answers (2)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

You must run a SQL query using Execute funcion from currentDb object, and build the query values concatenating values:

Private Sub Manager_AfterUpdate()
   Dim sSQL as String
   sSQL = "INSERT INTO tbl_ManagerChangeLog (Property, NewMan, [Date Change]) " & _
          "VALUES (" & Me.ID & ",'" & Me.Manager "'," & _ 
                    "#" & format(DATE(),"YYYY-MM-DD HH:NN:SS") & "#)"

   currentDB.Execute sSQL
End Sub

Upvotes: 1

phicon
phicon

Reputation: 3617

Try the following VBA to run a sql query.

Private Sub Manager_AfterUpdate()

Dim strSql As String , Db As dao.Database 

strSql = "INSERT INTO tbl_ManagerChangeLog (Property, NewMan, [Date Change]) VALUES (Me.ID, Me.Manager, DATE());" 

'Use Current Database 
Set Db = CurrentDb() 

'Run the SQL Query 
Db.Execute strSql 

End Sub

Upvotes: 0

Related Questions