none
Microsoft Access VBA Question RRS feed

  • Question

  • I'm relatively new to coding VBA in Access.    I want to verify who clicked a button which runs a macro that updates the database.   It would update a table called AuditLog with an On Click Event.    The data I want to update in the table are a user ID field, a date field and then add static text in that record called "negative balance".     Note -- there is no form for the user to fill out.  My code below is an attempt to figure out how to insert the 3 fields into the table, but I'm running into issues with the me.modified_by and me.modified_on as I believe the "me." will relate to a form   Can you help coach me on what I should correct? 

    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('" & Me.modified_by & "', '" & Me.modified_on & "', '" & NegativeBalance & "');"
    DoCmd.RunSQL sql

    End Sub

    Wednesday, September 20, 2017 7:46 PM

All replies

  • Hi,

    I notice that the issue is related to the VBA codes in Access. To better resolve your issue, I would move the thread to Access for developers forum for further troubleshooting.

    Thanks for your understanding :)

    Best Regards,
    Winnie Liang


    Please remember to mark the replies as answers if they help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 21, 2017 5:24 AM
  • Hi Pumpk1ns,

    I try to check your code and find that there are some errors in that.

    As you said there is no form to fill out and you are just executing the query from code.

    but in your code you are referring to the control by "me.". which causing the error.

    also there are mistakes in query. you did not add the string properly in variable.

    below is the corrected code.

    Private Sub Command0_Click()
    Dim sql, modified_by As String
    Dim modified_on As Date
    
    modified_by = Environ("USERNAME")
     modified_on = Format(Now(), "yyyy-MM-dd hh:mm:ss")
     sql = "Insert into AuditLog (modified_by,modified_on,Balance) Values('" & modified_by & "','" & modified_on & "','NegativeBalance');"
     Debug.Print (sql)
     DoCmd.RunSQL sql
    
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 21, 2017 8:43 AM
    Moderator
  • Hi Pumpk1ns,

    is your issue solved?

    I can see that after creating this thread, you did not done any follow up.

    if your issue is solved then try to share the solution and mark it as an answer.

    it will help other community members in future, who will have same kind of issue.

    if your issue is still exist then I suggest you to refer my last suggestion can solve your issue.

    if then also you have any further question regarding the same issue then let us know about that. we will try to provide further suggestions.

    try to update the status of this thread.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 12, 2017 6:21 AM
    Moderator