A
Adam
AllenBrown or Group:
I've been working through this but cannot get the text items in my
list passed as a string to my query or report. When I select something
from the list, I get a parameter prompt for, "CustomerName", and when
I leave it blank I get, "ODBC error 3146 Call Failed". Although
everything works when I get rid of multi-select in the list. I've
followed examples from several sites including Allenbrown but seem to
be overlooking something. Below is the on click event for my command
button if anyone has any suggestions: Thank you for any assistance you
can provide.
'Form: [New Form]
'List Box: lstCustName
'Date Field: CLOSE_OUT_DATE
'Table: tblCustNames
'Field: CustomerName
'Start Date: txtStartDate
'End Date: txtEndDate
'Report: rptCostMile
rivate Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptCostMile"
strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
'strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CustomerName] IN (" & Left$(strWhere2, lngLen) &
")"
End If
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
'Debug.Print strWhere3
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub
I've been working through this but cannot get the text items in my
list passed as a string to my query or report. When I select something
from the list, I get a parameter prompt for, "CustomerName", and when
I leave it blank I get, "ODBC error 3146 Call Failed". Although
everything works when I get rid of multi-select in the list. I've
followed examples from several sites including Allenbrown but seem to
be overlooking something. Below is the on click event for my command
button if anyone has any suggestions: Thank you for any assistance you
can provide.
'Form: [New Form]
'List Box: lstCustName
'Date Field: CLOSE_OUT_DATE
'Table: tblCustNames
'Field: CustomerName
'Start Date: txtStartDate
'End Date: txtEndDate
'Report: rptCostMile
rivate Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptCostMile"
strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
'strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CustomerName] IN (" & Left$(strWhere2, lngLen) &
")"
End If
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
'Debug.Print strWhere3
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub