Hi everyone, I am fairly new to VBA so bear with me. What I want to do is pull an item from a list box and convert that item into a String. As it sits I get a type mismatch error on the line
Code:
"Set Item=Me.listBox.ItemData(i)"
I tried casting item (Variant) into a String but got "object not found erorr".
Any help is appreciated here is the full sub routine.
Code:
Private Sub CalculateEnrollmentQuery()
Dim Qd As New QueryDef
Dim Qds As QueryDefs
Dim whereSQL As String
Dim cipSQL As String
Dim compSQL As String
Dim Item As Variant
Dim strTemp As String
//Calculate and adjust query according to List box contents
If Me.listBox.ListCount > 1 And Not IsNull(Item) Then
For i = 0 To Me.listBox.ListCount - 1
If i < Me.listBox.ListCount - 1 Then
Set Item = Me.listBox.ItemData(i)
cipSQL = Item
compSQL = "OR"
Else
compSQL = ";"
Exit For
End If
Next
End If
// set up where clause
whereSQL = "'" + cipSQL + "'" + compSQL
//create query
Set Qds = CurrentDb.QueryDefs
Qd.Name = "Calc_Enrollment"
Qd.SQL = "SELECT * FROM RAW_ENROLLMENT_DATA WHERE cip2000=" + whereSQL
Qds.Append Qd
Application.RefreshDatabaseWindow
Set Qd = Nothing
Set Qds = Nothing
//execute query
DoCmd.OpenQuery ("Calc_Enrollment")
End Sub