none
Print selected records report in one page. RRS feed

  • Question

  • I've designed an Access database for printing ID card for employee. I'm getting output all records by report view at a time. But I need to print those ID card which will be selected by me. It can be by Date wise, ID Number wise or Name wise, but always it won't be same sequence. Suppose I need to print ID Number 77092,77089,65897 & 59865 in a page.

    How can it be print properly?

    please suggest me.

    Thanks

    Tuesday, September 27, 2016 4:41 AM

Answers

  • I would add a yes/no-field "IsSelected" to the table with the ID Numbers. Then by selecting an ID you would set the field to yes. In the report you filter for IsSelected = True. After the printing, you reset the IsSelected field to no for all records.

    Another idea is to have a separate table tblToPrint with single field EmployeeID. When you select an ID to print, you add this ID to tblToPrint. In the report, you make an inner join on tblToPrint with the actual record source. After printing, you delete all records from tblToPrint.

    Matthias Kläy, Kläy Computing AG

    Tuesday, September 27, 2016 9:45 AM
  • Hi, How do you plan on selecting the ID numbers? If you like, you can use a multi-select list box and then use code to construct the WhereCondition argument of the OpenReport method to print only the selected ID numbers. Just a thought...
    Tuesday, September 27, 2016 2:02 PM
  • Take a look at MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the first button (top left) on the opening form opens a dialogue form in which a report can be filtered on any number of the items in a multiselect list box, using the following code:

        Dim varItem As Variant
        Dim strEmployeeIDList As String
        Dim strCriteria As String
        Dim ctrl As Control
        
        Set ctrl = Me.lstEmployees
        
        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strEmployeeIDList = strEmployeeIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strEmployeeIDList = Mid(strEmployeeIDList, 2)
            
            strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"
            
            DoCmd.OpenReport "rptEmployees", _
                View:=acViewPreview, _
                WhereCondition:=strCriteria
        Else
            MsgBox "No employees selected", vbInformation, "Warning"
        End If

    You could build a dialogue form with multiple list boxes like this and accompanying buttons to filter your report on different parameters.  Note that in each case, the list box's hidden bound column should be the primary key of the table in question (EmployeeID in my example) with the values to be selected in a second visible column by which the list is ordered. This achieved by setting the control's ColumnCount property to 2 and its ColumnWidths property to zero.  In your case setting its MultiSelect property to Simple, as in my demo, would probably be more appropriate than setting it to Extended.  

    With a list of dates, setting the MultiSelect property to Extended might be more suitable, however, as this would enable you to select a range of dates by shift-clicking the final date in the range.  Alternatively you could adopt the solution illustrated elsewhere in the demo, of entering start and end dates for a range in text boxes, each with their format property set to Short Date to force the date picker to show when the text box receives focus.

    Ken Sheridan, Stafford, England

    Wednesday, September 28, 2016 4:34 PM

All replies

  • I would add a yes/no-field "IsSelected" to the table with the ID Numbers. Then by selecting an ID you would set the field to yes. In the report you filter for IsSelected = True. After the printing, you reset the IsSelected field to no for all records.

    Another idea is to have a separate table tblToPrint with single field EmployeeID. When you select an ID to print, you add this ID to tblToPrint. In the report, you make an inner join on tblToPrint with the actual record source. After printing, you delete all records from tblToPrint.

    Matthias Kläy, Kläy Computing AG

    Tuesday, September 27, 2016 9:45 AM
  • Hi, How do you plan on selecting the ID numbers? If you like, you can use a multi-select list box and then use code to construct the WhereCondition argument of the OpenReport method to print only the selected ID numbers. Just a thought...
    Tuesday, September 27, 2016 2:02 PM
  • Take a look at MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the first button (top left) on the opening form opens a dialogue form in which a report can be filtered on any number of the items in a multiselect list box, using the following code:

        Dim varItem As Variant
        Dim strEmployeeIDList As String
        Dim strCriteria As String
        Dim ctrl As Control
        
        Set ctrl = Me.lstEmployees
        
        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strEmployeeIDList = strEmployeeIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strEmployeeIDList = Mid(strEmployeeIDList, 2)
            
            strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"
            
            DoCmd.OpenReport "rptEmployees", _
                View:=acViewPreview, _
                WhereCondition:=strCriteria
        Else
            MsgBox "No employees selected", vbInformation, "Warning"
        End If

    You could build a dialogue form with multiple list boxes like this and accompanying buttons to filter your report on different parameters.  Note that in each case, the list box's hidden bound column should be the primary key of the table in question (EmployeeID in my example) with the values to be selected in a second visible column by which the list is ordered. This achieved by setting the control's ColumnCount property to 2 and its ColumnWidths property to zero.  In your case setting its MultiSelect property to Simple, as in my demo, would probably be more appropriate than setting it to Extended.  

    With a list of dates, setting the MultiSelect property to Extended might be more suitable, however, as this would enable you to select a range of dates by shift-clicking the final date in the range.  Alternatively you could adopt the solution illustrated elsewhere in the demo, of entering start and end dates for a range in text boxes, each with their format property set to Short Date to force the date picker to show when the text box receives focus.

    Ken Sheridan, Stafford, England

    Wednesday, September 28, 2016 4:34 PM