Hi All,
I redid my form because I couldn't figure out how to get the second list box into my code. Would someone be able to let me know if I am on the correct
path? I have two list boxes - One named Origin and the other Enab. Any help would be greatly appreciated
The error is get is:
Run-time error '3075':
Syntax error (missing operator)in query expression "Origin_ID In(,5,1) and Origin_ID IN(SELECT Orign_ID FROM tbl_Lesson_Learned WHERE Enab_Org_ID
IN (1))'.
Below is the code
Private Sub Command5_Click()
Dim varItem As Variant
Dim strOriginIDList As String
Dim strEnabIDList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.Origin
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strOriginIDList = strOriginIDList & "," & ctrl.ItemData(varItem)
Next varItem
' remove leading comma
strOriginDList = Mid(strOriginIDList, 2)
strCriteria = " And Origin_ID In(" & strOriginIDList & ")"
End If
Set ctrl = Me.Enab
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strEnabIDList = strEnabIDList & "," & ctrl.ItemData(varItem)
Next varItem
' remove leading comma
strEnabIDList = Mid(strEnabIDList, 2)
strCriteria = strCriteria & " And Origin_ID IN" & _
"(SELECT Origin_ID FROM tbl_Lesson_Learned " & _
"WHERE Enab_Org_ID IN (" & strEnabIDList & "))"
End If
If Len(strCriteria) > 0 Then
' remove leading ' and '
strCriteria = Mid(strCriteria, 6)
DoCmd.OpenReport "Report 01", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No employee(s) or project(s) selected.", vbExclamation, "Warning"
End If
End Sub
Private Sub cmdSelectAll_Click()
Dim n As Integer
With Me.Origin
For n = 0 To .ListCount - 1
.Selected(n) = True
Next n
End With
End Sub
Private Sub cmdSelectAllProjects_Click()
Dim n As Integer
With Me.Enab
For n = 0 To .ListCount - 1
.Selected(n) = True
Next n
End With
End Sub
Janet Bonelli
