I have just started to do some coding with Access and trying to create a function that adds a row to a table but this would not work.
I have created a simple table (Table1) with two columns "FirstName" and "LastName" and a button that fires off the following code:
Private Sub Command0_Click()
AppendRow "Table1", "John", "Doe"
End Sub
Where AppendRow is:
Function AppendRow(toTableName As String, firstName As String, lastName As String) As Boolean
' Returns True on success, false otherwise
' USAGE: AppendRow "toTableName", "firstName", "lastName"
On Error GoTo errhandler
Dim strSql As String
'Create the SQL string
strSql = "INSERT INTO " & toTableName & " (FirstName, LastName) " & _
"VALUES ('" & firstName & "', '" & lastName & "');"
'Print the SQL so we can paste into the query build if there are errors
Debug.Print strSql
MsgBox strSql
'Run the SQL Query
CurrentDb.Execute strSql
'If no errors return true
AppendRow = True
ExitHere:
Exit Function
errhandler:
'There is an error return false
AppendRow = False
With Err
MsgBox "Error " & .Number & vbCrLf & .Description, vbOKOnly Or vbCritical, "AppendTable"
End With
Resume ExitHere
End Function
The SQL string looks like this
INSERT INTO Table1 (FirstName, LastName) VALUES ('John', 'Doe')
EDIT: Added missing quotes.
Best Answer
You reported that you are now quoting the text values you attempt to insert, but that you do not get the row inserted and apparently no error message. I don't understand why that is so, and offer this simple procedure simply to see whether you can get something to work.
I didn't include error handling because this is intended only for testing. I made it a subroutine instead of a function because I noticed you weren't using the function's return value in your code which called the function.
If this doesn't work for you, please tell us the error message you receive and which line in that procedure triggers the error.