none
unexpected end of statement Access 2013 RRS feed

  • Question

  • I am trying to figure out what is wrong with this code;

    'Dimension a public variable at the top of the module.
      Public LogEvent As String
      'Next add this code in the module
      Public LogEvent As String
      Public Function LogEvt() Dim SQL As String
      SQL = "INSERT INTO UsysLog ( Event, EvTime ) SELECT '" & LogEvent & "' AS x1, #" & Now() & "# as x2;"
    End Function

    I am new to this level of programming so a bit lost, every time I try to use it I get the unexpected end of statement message. Can anyone help me figure out the problem as it is the only thing left on my project which has taken me weeks to construct so really want to finish it now and move on.

    Thank you all in advance.

    Saturday, February 24, 2018 9:50 PM

Answers

  • That is an amazingly badly written article, it is full of errors.

    The code module should look like this:

    Option Explicit
    
    Public LogEvent As String
    
    Public Function LogEvt()
        Dim SQL As String
        SQL = "INSERT INTO UsysLog (Event, EvTime) VALUES ('" & LogEvent & _
            "', #" & Format(Now, "mm/dd/yyyy hh:nn AM/PM") & "#)"
        CurrentDb.Execute SQL, dbFailOnError
    End Function

    The next step is writing code for the events that you want to track. For example, if you want to track a record being updated (saved) in a form, create an After Update event procedure for that form. For example:

    Private Sub Form_AfterUpdate()
        LogEvent = "Record updated in " & Me.Name
        LogEvt
    End Sub

    And if you want to track a report being opened, create an event procedure for the On Open or On Load event of that report. For example:

    Private Sub Report_Load()
        LogEvent = "Report opened: " & Me.Name
        LogEvt
    End Sub

    etc. etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Paul P Clement IV Monday, February 26, 2018 2:43 AM
    • Marked as answer by R.Nock Tuesday, February 27, 2018 9:02 PM
    Sunday, February 25, 2018 1:43 PM

All replies

  • Well, the only real error is, that you're declaring LogEvent twice. But this results in a different error message. So I guess, it' s about how you use this code. Do you copy'n'paste it from somewhere? Then there is a chance, that you copy some invisible Unicode/whitespace characters, which are not allowed in code.
    Saturday, February 24, 2018 10:22 PM
  • 1) You can't declare LogEvent twice in the same module.

    2) Do you really have

      Public Function LogEvt() Dim SQL As String

    It should be

    Public Function LogEvt()
        Dim SQL As String

    3) Since you want to insert fixed values into the table instead of records from another table, you should use the syntax

    INSERT INTO target (field1, field2) VALUES (value1, value2)

    4) You should format the value of Now using US settings:

    Format(Now, "mm/dd/yyyy hh:nn AM/PM")

    So the assignment of SQL becomes:

        SQL = "INSERT INTO UsysLog (Event, EvTime) VALUES ('" & LogEvent & _
           "', #" & Format(Now, "mm/dd/yyyy hh:nn AM/PM") & "#)"

    5) Your function as is won't do anything and won't return any value.

    If you want it to do something, you should add a statement, for example

        CurrentDb.Execute SQL, dbFailOnError

    If you want it to return a value, you should assign a value to the function name LogEvt:

        LogEvt = ...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, February 24, 2018 10:33 PM
  • I am copying and pasting the code so will try to rewrite it just in case.
    Sunday, February 25, 2018 12:19 PM
  • Thanks, I have now progressed a little the error message now reads "Compile Error: Invalid Outstide Procedure"  and highlights the code below

    "INSERT INTO UsysLog (Event, EvTime) VALUES ('"

    The code is part of a user log function that is supposed to track specific roles when other code is put on the chosen function in visual basic.

    Thanks for your help.


    • Edited by R.Nock Sunday, February 25, 2018 12:49 PM
    Sunday, February 25, 2018 12:46 PM
  • Unfortunately rewriting it didn't work but the suggestion below managed to get me past the first problem but now I am faced with a new one, I am beginning to realise how much I love working with code.
    Sunday, February 25, 2018 12:48 PM
  • Do you want to use the function to perform an action or should it return a value?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 25, 2018 1:04 PM
  • It Logs user activity.
    Sunday, February 25, 2018 1:24 PM
  • Here is the link to the page that I got the code from;

    https://www.business.com/articles/tracking-user-activity-in-your-access-databases/

    Sunday, February 25, 2018 1:27 PM
  • That is an amazingly badly written article, it is full of errors.

    The code module should look like this:

    Option Explicit
    
    Public LogEvent As String
    
    Public Function LogEvt()
        Dim SQL As String
        SQL = "INSERT INTO UsysLog (Event, EvTime) VALUES ('" & LogEvent & _
            "', #" & Format(Now, "mm/dd/yyyy hh:nn AM/PM") & "#)"
        CurrentDb.Execute SQL, dbFailOnError
    End Function

    The next step is writing code for the events that you want to track. For example, if you want to track a record being updated (saved) in a form, create an After Update event procedure for that form. For example:

    Private Sub Form_AfterUpdate()
        LogEvent = "Record updated in " & Me.Name
        LogEvt
    End Sub

    And if you want to track a report being opened, create an event procedure for the On Open or On Load event of that report. For example:

    Private Sub Report_Load()
        LogEvent = "Report opened: " & Me.Name
        LogEvt
    End Sub

    etc. etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Paul P Clement IV Monday, February 26, 2018 2:43 AM
    • Marked as answer by R.Nock Tuesday, February 27, 2018 9:02 PM
    Sunday, February 25, 2018 1:43 PM
  • It seems to have worked but cant find the table (despite selecting view hidden objects) any hints on how to see the table that the info is saved into so I can make sure its recording?
    Sunday, February 25, 2018 1:51 PM
  • Also tick the check box for 'Show System Objects'. Names beginning with MSys or USys are treated as system objects.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 25, 2018 2:46 PM
  • I will give that a go.

    The database was working perfectly but just opened it again after an hour or so of being closed and its showing a compile error: Variable not defined.

    It is highlighting "dbFailOnError"

    Oddly enough it isnt happening on every form that it is assigned to, the first 4 work fine.
    • Edited by R.Nock Sunday, February 25, 2018 5:58 PM
    Sunday, February 25, 2018 5:01 PM
  • That worked (Show System Objects), thank you.
    • Edited by R.Nock Sunday, February 25, 2018 5:29 PM
    Sunday, February 25, 2018 5:19 PM
  • It is highlighting "dbFailOnError"

    Make sure that you have ticked the check box for "Microsoft Office n.0 Access database engine Object Library" in Tools > References... in the Visual Basic Editor (the value of n depends on your version of Access)

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 25, 2018 8:49 PM
  • Hello R.Nock,

    I'm glad to hear that you have solved the issue. I suggest you mark helpful reply as answer to close this thread.

    If you have any other issue, please feel free to post threads to let us know.

    Thanks for understanding. 

    Best Regards,

    Terry



    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.

    Monday, February 26, 2018 6:46 AM
  • Hi Terry,

    Done, thank you all for the help, I will definately have more questions for other projects in the near future.

    Kind Regards

    Richard

    Tuesday, February 27, 2018 9:04 PM