Insert string to table with sql statement

[Cereldine]

Hi, im building myself a error handler and part of it is to record the error details in a logging table. I have a function that flags up error number, description and location in a message box. How do i take these strings and long ints and record them into a table. Below is a snippet of code ive been trying, the problem is in my sql statement, any help appreciated!

ErrorLog = True

strMsg = strMsg & "There has been an error in the application." & vbCrLf & vbCrLf ''vbCRLF (Visual Basic Carriage Return Line Feed) is the VB shortcut for Chr(13) & Chr(10), ;
strMsg = strMsg & "Error Number: " & lngErrNum & vbCrLf
strMsg = strMsg & "Error Description: " & strErrDesc & vbCrLf
strMsg = strMsg & "Error Location: " & strWhereFrom & vbCrLf

strMsg = strMsg & "This error has been inputted into the error handling log table" & vbCrLf & vbCrLf
strMsg = strMsg & "Any further problems contact on.your.own@xxx.gov"

MsgBox strMsg, vbInformation, "Error Log."

sql = "INSERT INTO Error_handling_table (Error_Number,Error_Description,Error_Location ) VALUES (7,strErrDesc,1) "
DoCmd.RunSQL (sql)

 

[Jack Skeleton]

Why don't you try with a Recordset?
For instance:

Dim rsttmp as dao.recordset
Set rsttmp = currentdb.openrecordset([ Error_handling_table], dbopentable])
with rsttmp
      .add
      .fields("Error_Number").value = lngErrNum
      .fields("Error_Description").value = strErrDesc
      .fields("Error_Location).value = strWhereFrom
      .update
end with
rsttmp.close

I think I've written it correctly, but I've done it "on the fly" so maybe I've made any little mistake (anyway I don't have time to check it now).

A tip: If you want to put together two or three "Visual Basic Carriage Return Line Feed" ( WOW, I'm astonished, this vocabulary is just too much for me ) a shorter way is writting "String(3, 13)" (in this example 13 means the character and 3 the number of repetitions). So you can substitute
"& vbCrLf & vbCrLf"
for
"& string(2, 13)"
what's even shorter...

 

[workmad3]

I think the following should work

sql = "INSERT INTO Error_handling_table (Error_Number,Error_Description,Error_Location ) VALUES (7, '" & strErrDesc & "',1) "

hope it helps

 

[Cereldine]

i have entered the code you have suggested and it brings up an error on this line

Set rsttmp = CurrentDb.OpenRecordset([Error_handling_table], dbOpenTable)

The error says external name not defined?

How can i get around this?

 

[richary]

Replace the brackets aound Error_handling_table with quotes.

 

[Cereldine]

Now trying both sql and record set methods

placing quotes around error table has corrected that error but i now have one on the
.add

the error states "method or data memeber not found"

 

[Cereldine]

With regards to the sql method i have written this


sql = "INSERT INTO Error_handling_table (Error_Number,Error_Description,Error_Location ) VALUES (lngErrNum, '" & strErrDesc & "', '" & strWhereFrom & "') "
DoCmd.RunSQL (sql)

The code compiles fine, it brings up a syntax error when it runs on the error_description which is strange because the error_location enters the table fine! any ideas!!

 

[richary]

replace ".add" with ".AddNew"

 

[Cereldine]

yeah that recordset method works, cheers for your help

 

[workmad3]

sql = "INSERT INTO Error_handling_table (Error_Number,Error_Description,Error_Location ) VALUES (" & lngErrNum & ", '" & strErrDesc & "', " & strWhereFrom & ") "
DoCmd.RunSQL (sql)

This might work for the sql method now as well.