If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add code to handle All jobs FROm dropdown list if None selected-
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 |
#2
|
|||
|
|||
Add code to handle All jobs FROm dropdown list if None selected-
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" wrote: 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 |
#3
|
|||
|
|||
Add code to handle All jobs FROm dropdown list if None selecte
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" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
Add code to handle All jobs FROm dropdown list if None selecte
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" wrote: 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" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
Add code to handle All jobs FROm dropdown list if None selecte
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
Add code to handle All jobs FROm dropdown list if None selecte
I would possibly attempt to somehow filter the Row Source of the listbox.
-- Duane Hookom Microsoft Access MVP "babs" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|