Add code to handle All jobs FROm dropdown list if None selected-

B

babs

Below is the code I have for print previewing a report based on crieteria
selected. I have a drop down mulitiselect list box that is populated based
on a query with criteria-
Everything works great if one or many job # s from the list box are
selected. If NONE are selected I want only the jods from the list box to be
printed(previewed). The way it is now ALL jobs(not just the onesin the drop
down) are being printed. the Name of the list box on the form is lstcategory.

I really Need Help.

thanks - see below:
BArb

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "LaborReport2 BP"

'Loop through the ItemsSelected in the list box.
With Me.lstcategory

For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[JobNbr] IN (" & Left$(strWhere, lngLen) & ")" & " And
[checkDate] >= #" & Me.txtCheckStart & "# And [checkDate] <= #" &
Me.txtCheckEnd & "# AND [Policy Year] = " & Me.cmbPolicy
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Name: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If




'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
 
B

babs

I see the code but not sure how to add it to my existing code - what do I add
and how - an If lstcategory is Null
call selectall ?????

thanks soooo much for your help - do I also have to set back to clear all or
don't need to worry about that?

thanks again,
barb

Duane Hookom said:
I would allow the users to easily select all with Allen Browne's code at
http://www.allenbrowne.com/func-12.html.

--
Duane Hookom
Microsoft Access MVP


babs said:
Below is the code I have for print previewing a report based on crieteria
selected. I have a drop down mulitiselect list box that is populated based
on a query with criteria-
Everything works great if one or many job # s from the list box are
selected. If NONE are selected I want only the jods from the list box to be
printed(previewed). The way it is now ALL jobs(not just the onesin the drop
down) are being printed. the Name of the list box on the form is lstcategory.

I really Need Help.

thanks - see below:
BArb

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "LaborReport2 BP"

'Loop through the ItemsSelected in the list box.
With Me.lstcategory

For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[JobNbr] IN (" & Left$(strWhere, lngLen) & ")" & " And
[checkDate] >= #" & Me.txtCheckStart & "# And [checkDate] <= #" &
Me.txtCheckEnd & "# AND [Policy Year] = " & Me.cmbPolicy
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Name: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If




'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
 
D

Duane Hookom

I generally add a couple small command buttons near the list box. One button
calls the function to select all and the other to clear. Here is some actual
code

Private Sub cmdClearMachines_Click()
ClearList Me.lboMachine
End Sub



--
Duane Hookom
Microsoft Access MVP


babs said:
I see the code but not sure how to add it to my existing code - what do I add
and how - an If lstcategory is Null
call selectall ?????

thanks soooo much for your help - do I also have to set back to clear all or
don't need to worry about that?

thanks again,
barb

Duane Hookom said:
I would allow the users to easily select all with Allen Browne's code at
http://www.allenbrowne.com/func-12.html.

--
Duane Hookom
Microsoft Access MVP


babs said:
Below is the code I have for print previewing a report based on crieteria
selected. I have a drop down mulitiselect list box that is populated based
on a query with criteria-
Everything works great if one or many job # s from the list box are
selected. If NONE are selected I want only the jods from the list box to be
printed(previewed). The way it is now ALL jobs(not just the onesin the drop
down) are being printed. the Name of the list box on the form is lstcategory.

I really Need Help.

thanks - see below:
BArb

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "LaborReport2 BP"

'Loop through the ItemsSelected in the list box.
With Me.lstcategory

For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[JobNbr] IN (" & Left$(strWhere, lngLen) & ")" & " And
[checkDate] >= #" & Me.txtCheckStart & "# And [checkDate] <= #" &
Me.txtCheckEnd & "# AND [Policy Year] = " & Me.cmbPolicy
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Name: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If




'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

babs

It Works! thanks a million -
Wondering if there is any way that we can type in the list box and it will
scroll to the job #s so we Dobn't have to scroll and click on each one -
can't use combo box since need to select possibly multiple jobs- what am i
missing????

thanks again,
barb

Duane Hookom said:
I generally add a couple small command buttons near the list box. One button
calls the function to select all and the other to clear. Here is some actual
code

Private Sub cmdClearMachines_Click()
ClearList Me.lboMachine
End Sub



--
Duane Hookom
Microsoft Access MVP


babs said:
I see the code but not sure how to add it to my existing code - what do I add
and how - an If lstcategory is Null
call selectall ?????

thanks soooo much for your help - do I also have to set back to clear all or
don't need to worry about that?

thanks again,
barb

Duane Hookom said:
I would allow the users to easily select all with Allen Browne's code at
http://www.allenbrowne.com/func-12.html.

--
Duane Hookom
Microsoft Access MVP


:

Below is the code I have for print previewing a report based on crieteria
selected. I have a drop down mulitiselect list box that is populated based
on a query with criteria-
Everything works great if one or many job # s from the list box are
selected. If NONE are selected I want only the jods from the list box to be
printed(previewed). The way it is now ALL jobs(not just the onesin the drop
down) are being printed. the Name of the list box on the form is lstcategory.

I really Need Help.

thanks - see below:
BArb

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "LaborReport2 BP"

'Loop through the ItemsSelected in the list box.
With Me.lstcategory

For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[JobNbr] IN (" & Left$(strWhere, lngLen) & ")" & " And
[checkDate] >= #" & Me.txtCheckStart & "# And [checkDate] <= #" &
Me.txtCheckEnd & "# AND [Policy Year] = " & Me.cmbPolicy
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Name: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If




'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I would possibly attempt to somehow filter the Row Source of the listbox.

--
Duane Hookom
Microsoft Access MVP


babs said:
It Works! thanks a million -
Wondering if there is any way that we can type in the list box and it will
scroll to the job #s so we Dobn't have to scroll and click on each one -
can't use combo box since need to select possibly multiple jobs- what am i
missing????

thanks again,
barb

Duane Hookom said:
I generally add a couple small command buttons near the list box. One button
calls the function to select all and the other to clear. Here is some actual
code

Private Sub cmdClearMachines_Click()
ClearList Me.lboMachine
End Sub



--
Duane Hookom
Microsoft Access MVP


babs said:
I see the code but not sure how to add it to my existing code - what do I add
and how - an If lstcategory is Null
call selectall ?????

thanks soooo much for your help - do I also have to set back to clear all or
don't need to worry about that?

thanks again,
barb

:

I would allow the users to easily select all with Allen Browne's code at
http://www.allenbrowne.com/func-12.html.

--
Duane Hookom
Microsoft Access MVP


:

Below is the code I have for print previewing a report based on crieteria
selected. I have a drop down mulitiselect list box that is populated based
on a query with criteria-
Everything works great if one or many job # s from the list box are
selected. If NONE are selected I want only the jods from the list box to be
printed(previewed). The way it is now ALL jobs(not just the onesin the drop
down) are being printed. the Name of the list box on the form is lstcategory.

I really Need Help.

thanks - see below:
BArb

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "LaborReport2 BP"

'Loop through the ItemsSelected in the list box.
With Me.lstcategory

For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[JobNbr] IN (" & Left$(strWhere, lngLen) & ")" & " And
[checkDate] >= #" & Me.txtCheckStart & "# And [checkDate] <= #" &
Me.txtCheckEnd & "# AND [Policy Year] = " & Me.cmbPolicy
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Name: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If




'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Works, But Doesn't Filter 1
MultiListBox instead of Paramater query - Allen Browne code 7
Multiselect Listbox selections don't show up in report 9
Crosstab Query Building Report 2
Two multi-select list boxes 3
Error 13 - Type Mismatch 5
Two Multi-Select List Boxes as Filters 11
list box selection as filter 1

Top