Results 1 to 3 of 3
  1. #1
    Pumpk1n is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    1

    Inserting records into a table with an "on click" event

    I have a navigation form which has buttons the end user clicks to update the database and run reports. I need to create an audit of who 'clicked' the button. I created an "on click" event for one of the buttons, I updated the properties of the navigation form to link to the auditlog table. Now, here's where I get stuck and I am new to this process of trying to insert records into a table using VBA. The Audit log table consists of 4 fields -- the key "EditRecordID" which is autonumbered, "Modified_On, Modified_by and FunctionPerformed.



    The form has the 3 fields "modified_on, Modified_by and FunctionPeformed. FunctionPerformed is a fixed text field "updatebalances".

    The code I created:
    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 & "', '" & updatebalances & "');"
    DoCmd.RunSQL strSql
    End Sub

    The error message: "run-time error '3129': Invalid SQL statement; expected 'delete', 'insert','procedure','select', or update

    I think I'm close.... Can I get some help? Thanks in advance!

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create the query with made-up values in query design. That will create the SQL that you need. Then copy and paste the SQL into your VBA module and change the made-up values to the form field names.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The SQL syntax wrong. Missing are the field names of where to put the values
    Should be
    Code:
    sql = "Insert into AuditLog (Modified_by, modified_on, FunctionPerformed) Values('" & Me.Modified_by & "', '"  & Me.Modified_on & "', 'updatebalances');"
    Since FunctionPerformed is a fixed value, you don't need to concatenate the word "updatebalances".

    Why is "modified_on" a TEXT type instead of a Date/Time type???

    "strLocation" is declared but not used???

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-23-2017, 07:06 PM
  2. Replies: 3
    Last Post: 02-06-2015, 03:22 PM
  3. Fill in data into table upon "on click"
    By teekc in forum Forms
    Replies: 4
    Last Post: 04-01-2014, 08:27 PM
  4. Replies: 11
    Last Post: 04-01-2014, 01:10 PM
  5. Replies: 21
    Last Post: 06-03-2009, 05:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums