unexpected end of statement Access 2013

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.

Answer 1

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.

Answer 2

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)

Answer 3

I am copying and pasting the code so will try to rewrite it just in case.

Answer 4

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.

Answer 5

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.

Answer 6

Do you want to use the function to perform an action or should it return a value?

---

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

Answer 7

It Logs user activity.

Answer 8

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/

Answer 9

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)

Answer 10

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?

Answer 11

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)

Answer 12

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.

Answer 13

That worked (Show System Objects), thank you.

Answer 14

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)

Answer 15

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 [e-mail].

Answer 16

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