So the form isn't working quite like i want it to. I believe it's the WHERE clause in the strSQL thats causing the issue. The form allows me to select a hull and 1 more paramater whether its LeadDept, WS, SSphase, Or the Dates. However if I select say Hull, WS, and SSPhase it throws Syntax error Missing Operator.
The Debug.Print shows:
Code:
SELECT * FROM dbo_tblPrintCenter WHERE dbo_tblPrintCenter.hull IN ('4019') AND dbo_tblPrintCenter.WS IN ('680','700') dbo_tblPrintCenter.SSPhase IN ('005')
and as you can see there is no AND after the second parameter. If I do put the AND in there it will force the user to select the 3rd parameter. I only want hull to be the only required parameter and the rest are optional.
Code:
Private Sub btnSearch_Click()
'Apply Filter button
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteriaHull As String
Dim strCriteriaWS As String
Dim strCriteriaLeadDept As String
Dim strCriteriaPhase As String
Dim CalcSSBegin As Date
Dim CalcSSEnf As Date
Dim strSQL As String
Dim qryDef As QueryDef
Dim intCondition As Integer
Dim ctlB As Control
Dim ctlE As Control
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryform")
Set ctlB = Me.txtCalcSSBegin
Set ctlE = Me.txtCalcSSEnd
'get selections from multiselect listbox
'Hull ListBox
For Each varItem In Me!lstHull.ItemsSelected
strCriteriaHull = strCriteriaHull & ",'" & Me!lstHull.ItemData(varItem) & "'"
Next varItem
'get selections from multiselect listbox
'WS ListBox
For Each varItem In Me!lstWS.ItemsSelected
strCriteriaWS = strCriteriaWS & ",'" & Me!lstWS.ItemData(varItem) & "'"
Next varItem
'get selections from multiselect listbox
'LeadDept ListBox
For Each varItem In Me!lstLeadDept.ItemsSelected
strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!lstLeadDept.ItemData(varItem) & "'"
Next varItem
'get selections from multiselect listbox
'SS Phase ListBox
For Each varItem In Me!lstSSPhase.ItemsSelected
strCriteriaPhase = strCriteriaPhase & ",'" & Me!lstSSPhase.ItemData(varItem) & "'"
Next varItem
If Len(strCriteriaHull) > 0 Then
strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
End If
If Len(strCriteriaWS) > 0 Then
strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
End If
If Len(strCriteriaLeadDept) > 0 Then
strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
End If
If Len(strCriteriaPhase) > 0 Then
strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)
End If
'Check for a blank value
If Nz(ctlB, "") = "" And Nz(ctlE, "") = "" Then intCondition = 1 'both are blank
If Nz(ctlB, "") = "" And Not Nz(ctlE, "") = "" Then intCondition = 3 'one is blank the other not
If Not Nz(ctlB, "") = "" And Nz(ctlE, "") = "" Then intCondition = 3 'one is blank the other not
If intCondition = 3 Then
MsgBox "Both dates must be entered if one is entered."
Cancel = True
Exit Sub
End If
If Not Nz(ctlB, "") = "" And Not Nz(ctlE, "") = "" Then
If Not IsDate(ctlB) Or Not IsDate(ctlE) Then
MsgBox "Only valid dates can be entered into date fields."
Cancel = True
Exit Sub
End If
If ctlB > ctlE Then
MsgBox "Begin date cannot be greater than end date."
Cancel = True
Exit Sub
End If
intCondition = 2 'If nested IF's pass then dates are OK and both are provided
End If
strSQL = " SELECT * FROM dbo_tblPrintCenter WHERE "
If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
If Len(strCriteriaLeadDept) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") "
If Len(strCriteriaWS) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ") "
If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.SSPhase IN (" & strCriteriaPhase & ") "
If intCondition = 2 Then strSQL = strSQL & " AND dbo_tblPrintCenter.CalcSS BETWEEN #" & ctlB & "# And #" & ctlE & "#"
Debug.Print strSQL
DoCmd.SetWarnings False
Set qryDef = db.QueryDefs("qryform")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
'assign to form recordsource
Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL
'Set the object variables to Nothing to ensurethat these are cleared from the memory
Set db = Nothing
Set qdf = Nothing
'Requerys Form
Forms![Print Request Search Form]![dbo_tblPrintCenter subform].Form.Requery
End Sub