General function to write a log record

[Gkirkup]

I need to add a log file to an application. From many places in the application, I would like to call a function like this:

Log(Log_number, location, employee, "This is the text of the log")

Usually when adding a record to a table like this I would use an append query, and pick up the field values from controls on the form in use.
But in this case, I will be calling the function from many places, so cannot use controls to get the data.
How would I write a general purpose 'add log to table' function?

Robert

 

[SimonB1978]

You could use something like this in a code module:

Public Sub AddLog(lngLogNumber, strlocation, stremployee, strlogtext)

    Dim strSQL As String
    
    strSQL = "INSERT INTO tblLog(LogNumber, Location, Employee, LogText) " & _
                "VALUES('" & intlognumber & ",'" & strlocation & "','" & stremployee & "','" & strlogtext & "')"
                
    CurrentDb.Execute strSQL, dbFailOnError
    
End Sub

Obviously, you will have to set your table and columns name appropriately.

You can then call this function from anywhere in your application.

HTH

Simon B.

 

[Gkirkup]

Yes, that's just what I need, something like that. Thanks so much.

Robert

 

[Gkirkup]

Simon: I am getting somewhere. I wrote it as a function, I hope that's OK. Now I am getting a syntax error, missing operator, on -

CurrentDb.Execute strSQL, dbFailOnError

Any ideas?

Robert

 

[Gkirkup]

Here is the function:

Function AddLog(lntLogNumber As Long, strlocation As String, stremployee As String, strlogtext As String) As Long


Dim strSQL As String


strSQL = "INSERT INTO SSLOG(LOGNUM, LOGLOCN, LOGEMP, LOGTEXT) " & _
"VALUES('" & intlognumber & ",'" & strlocation & "','" & stremployee & "','" & strlogtext & "')"


CurrentDb.Execute strSQL, dbFailOnError


AddLog = intlognumber

End Function

 

[Gkirkup]

Can anyone spot the missing operator in that function? I am not familiar with CurrentDB.Execute.

Robert

 

[SimonB1978]

2 things:

- remove the ' after VALUES(
- you declared lntLogNumber but you use intlognumber in your function

 

[Gkirkup]

I am getting a syntax error on INSERT INTO. I removed the single quotes as I didn't know why they were there. What am I missing?

strSQL = "INSERT INTO SSLOG(LOGNUM, LOGLOCN, LOGEMP, LOGTEXT) " & _
"VALUES(" & intlognumber & "," & strlocation & "," & stremployee & "," & strlogtext & ")"


CurrentDb.Execute strSQL, dbFailOnError

Thanks!
Robert

 

[JANR]

Delimiters for SQL are:
Numbers -> " & MyNumber & "
Strings -> '" & MyString & "'
Dates -> #" & MyDate & "#

Note that for dates Jet/Ace expects dates to be US-Dates mm/dd/yyyy, so if your regional setting is not that then you must format the date BEFORE you sent it to JET/Ace

Dates Non US -> " & Format(MyDate, "\#mm\/dd\/yyyy\#") & "

JR

 

[Gkirkup]

I am still getting a syntax error when strSQL is used in the followingh function. I have looked at it for a long time but cannot see it. Can anyone see where I am going wrong?
Robert

Function AddLog(lntLogNumber As Long, strlocation As String, stremployee As String, strlogtext As String) As Long

Dim strSQL As String


strSQL = "INSERT INTO tblLog(LogNumber, Location, Employee, LogText) " & _
"VALUES(" & intlognumber & ",'" & strlocation & "','" & stremployee & "','" & strlogtext & "')"


CurrentDb.Execute strSQL, dbFailOnError



AddLog = intlognumber

End Function

 

[SimonB1978]

I am still getting a syntax error when strSQL is used in the followingh function. I have looked at it for a long time but cannot see it. Can anyone see where I am going wrong?
Robert

Function AddLog(lntLogNumber As Long, strlocation As String, stremployee As String, strlogtext As String) As Long

Dim strSQL As String


strSQL = "INSERT INTO tblLog(LogNumber, Location, Employee, LogText) " & _
"VALUES(" & intlognumber & ",'" & strlocation & "','" & stremployee & "','" & strlogtext & "')"


CurrentDb.Execute strSQL, dbFailOnError



AddLog = intlognumber

End Function

Your variables name don't match. See in RED. You should add Option Explicit at the beginning of your code, it would catch those things.

 

[Gkirkup]

Thank you. So capitalization matters in SQL? I didn't know that.

Robert

 

[Gkirkup]

I am still getting a syntax error. I can't see it!

Function AddLog(intlognumber As Long, strlocation As String, stremployee As String, strlogtext As String) As Long

Dim strSQL As String


strSQL = "INSERT INTO tblLog(LogNumber, Location, Employee, LogText) " & _
"VALUES(" & intlognumber & ",'" & strlocation & "','" & stremployee & "','" & strlogtext & "')"


CurrentDb.Execute strSQL, dbFailOnError


AddLog = intlognumber

End Function

 

[Gkirkup]

OK, got it! My log file field names were incorrect.
Thanks!
Robert