I am really very new to VBA and I have been trying to insert the value from a numeric and date variable into a table using VBA. Here is the code that I have been using:
DoCmd.RunSQL "INSERT INTO tbl_results ( [results_student_id], [results_test_date], [results_score]) VALUES (' "& st_student_id &" ', ' "& St_test_id &" ', ' "& St_score & " ')"
Where st_student_id and st_score are numeric and st_test_id is a date...
Create and test this parameter query in the Access query designer.
INSERT INTO tbl_results
(results_student_id, results_test_date, results_score)
VALUES
([pId], [pDate], [pScore])
When you run the query, Access will prompt you to supply values for the three parameters: pId; pDate; and pScore.
After you have it working in the query designer, save it as qryAddResults. Then your VBA code can use that named query, supply the parameter values, and execute it.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryAddResults")
qdf.Parameters("pId") = st_student_id
qdf.Parameters("pDate") = St_test_id
qdf.Parameters("pScore") = St_score
qdf.Execute dbFailOnError
Here is a suggestion to see what your SQL really looks like. Run the following, then post the SQL you have:
Dim strSQL as String
strSQL = "INSERT INTO tbl_results ( [results_student_id], [results_test_date], [results_score])
VALUES (" & 1 & " , " & 2 & " , #" & Format(St_score, "dd\/mm\/yyyy") & "#)"
Debug.Print strSQL
DoCmd.RunSQL strSQL
