New to VBA - I am working on a database to pull demographic information. This is what I have so far:
1. query to limit the selection of campuses (in other words, only include the campuses that are active)called qry_active_campus; this is the source for the list box
2. query that uses will use the information from the list box and a student_enroll table to give me student name, address, campus, grade level, etc. (query is called student_info)
3. query that uses the query from #2 to do an unmatched query against a list of students with information restrictions (query called student_info without matching restriction_code)
4. Form with two list boxes - one to choose one or more campuses and one to choose one or more grade levels (so they can choose High School #1, but only grades 10 and 11).
5. Code in the "OK" button on the form that reads like this:
Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("student_info")
For Each varItem In Me!cbocampus.ItemsSelected
strCriteria = strCriteria & ",'" & Me!cbocampus.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "Please select one or more campus codes" _
, vbExclamation, "No campuses were selected!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM student_info" & _
"WHERE student_info.campus_id IN (" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "student_info"
Set db = Nothing
Set qdf = Nothing
End Sub
I am getting a "circular reference caused by 'student_info'. I don't understand what's wrong! I did this from an online tutorial, and am trying to apply my names to this code. Somewhere I'm failing. Please let me know if I am not clear in my question.
1. query to limit the selection of campuses (in other words, only include the campuses that are active)called qry_active_campus; this is the source for the list box
2. query that uses will use the information from the list box and a student_enroll table to give me student name, address, campus, grade level, etc. (query is called student_info)
3. query that uses the query from #2 to do an unmatched query against a list of students with information restrictions (query called student_info without matching restriction_code)
4. Form with two list boxes - one to choose one or more campuses and one to choose one or more grade levels (so they can choose High School #1, but only grades 10 and 11).
5. Code in the "OK" button on the form that reads like this:
Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("student_info")
For Each varItem In Me!cbocampus.ItemsSelected
strCriteria = strCriteria & ",'" & Me!cbocampus.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "Please select one or more campus codes" _
, vbExclamation, "No campuses were selected!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM student_info" & _
"WHERE student_info.campus_id IN (" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "student_info"
Set db = Nothing
Set qdf = Nothing
End Sub
I am getting a "circular reference caused by 'student_info'. I don't understand what's wrong! I did this from an online tutorial, and am trying to apply my names to this code. Somewhere I'm failing. Please let me know if I am not clear in my question.
