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

  • Thread starter Thread starter babs
  • Start date Start date
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top