CODEWITHSUNDEEP
sqlms-accessvbams-access-2013
user3657834
user3657834

Reputation: 259

How to use a INSERT INTO SELECT query in Access VBA?

I have one table called Student, which contains information on a student such as their name.
Another table called Exam which has a date the exam was taken and the name of a student as the primary key.
I have a form that can be used to select multiple students from a list box that will then be inserted into the Exam table on the date selected.

I believe my syntax is correct because if I use Access's query builder and copy/paste my SQL query and get rid of the form stuff it will work as expected.
The error I get when I try to run it from VBA is that Exam.Exam_Date is unknown and to check my spelling. I spell it how it is spelled in the table.

Is it possible to use an INSERT INTO SELECT query within VBA in Access?

Here is my code:

Private Sub add_Click()
    Dim Students As String
    Dim i As Integer
    Dim dbs As DAO.Database
    Dim SQL As String
    
    SQL = ""
    Students = "'"
    
    For i = 0 To Me.StudentListBox.ListCount - 1
        'check to see if students name is selected
        If Me.StudentListBox.Selected(i) = True Then
            'list student names in a string separated by commas
            Students = Students + CStr(Me.StudentListBox.ItemData(i)) & "','"
        End If
    Next
    
    If IsNull(Me.ExamDate) Then 'check if user entered an Exam date
        MsgBox "Please select a date for the Exam."
    ElseIf Students = "'" Then 'check if user selected ant Students
        MsgBox "Please select Students to add to an Exam."
    Else
        'remove trailing comma
        Students = Left(Students, Len(Students) - 2)
        
        'sql query to add list of Students to an Exam on specified date
        SQL = "INSERT INTO Exam (Exam.Exam_Date, Exam.Student_Name) SELECT '" & CDate(Me.ExamDate) & "', Students.Full_Name FROM Students WHERE Students.Full_Name IN (" & Students & ");"
        DoCmd.RunSQL SQL
    End If
End Sub

Upvotes: 1

Views: 11939

Answers (1)

Parfait
Parfait

Reputation: 107567

I understand you may have already found your issue but I do want to point out some other items.

It is a very interesting setup building the WHERE IN clause. Alternatively, you could have iterated an INSERT INTO ... VALUES inside the For/Next loop:

INSERT INTO Exam (Exam_date, Student_Name) 
VALUES(#" & Me.ExamDate & "#, '" & Me.StudentListBox.ItemData(i) & "')

Also, check your Exam_Date field. From your query it looks like you retain date as a string field but if it is a date/time field, VBA queries require # # instead of single quotes. Also, no need for conversion functions, CStr or CDate, if already formatted to these data types by the form.

Finally, for database design recommendation, you should use StudentID inside the Exam table instead of relating both tables by Full_Name: better indexing, primary/foreign key referential integrity, data storage efficiency. Plus, if names have quotes no need for escaping or misspellings and integer values is safer in managing data between tables (i.e., duplicates, lookup).

Upvotes: 1

Related Questions