Results 1 to 8 of 8

Print only selected records

  1. #1
    shod90 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    96

    Print only selected records


    Dear Gents ,
    I have a form that contains some IT Assets I need to print only selected items from all items and print them with their quantities .. I already tried to do it but i only succeeded in printing them all with their quantities , But now i need to print only selected items
    Here is the code an i hope to help me

    Code:
    Dim rs As DAO.RecordsetDim i As Long
    Dim task As String
    Set rs = Me.RecordsetClone
    Dim msg, Button, Title, Response
      msg = "Are you sure you want to print these items ? "
      Button = vbYesNo + vbDefaultButton2
      Title = "Easy cash V. 1.0"
      Response = MsgBox(msg, Button, Title)
      If Response = vbYes Then
    If Not (rs.BOF And rs.EOF) Then 'make sure we have records to loop through
        rs.MoveFirst 'make sure we're on the first record
        Do While Not rs.EOF 'keep looping until we reach the end of the recordset
            For i = 1 To rs!qty
                RedefRptSQL "Copy Of BarcodeMaker_4cmSize2", "SELECT Category.Category, Info.Name, * FROM Info, Category INNER JOIN Products ON Category.CatID = Products.Category WHERE (((Products.ItemID)='" & rs!ItemID & "') AND ((Products.[checked])=True));"
                DoCmd.OpenReport "Copy Of BarcodeMaker_4cmSize2", acViewNormal
            Next i
        rs.MoveNext 
        Loop
    End If
    
    
    rs.Close
    Set rs = Nothing
    Else
    End If
    here is the defentition of RedefRptSQL Function " I found this function on this forum btw "
    Code:
    On Error GoTo Error_Handler
        Dim Rpt     As Report
     
        DoCmd.OpenReport sRptName, acViewDesign, , , acHidden 'Open in design view so we can
                                                              'make our changes
        Set Rpt = Application.Reports(sRptName)
        Rpt.RecordSource = sSQL                               'Change the RecordSource
        DoCmd.Close acReport, sRptName, acSaveYes             'Save our changes
     
    Error_Handler_Exit:
        On Error Resume Next
        Set Rpt = Nothing
        Exit Function
     
    Error_Handler:
        MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
                "     Error Number: " & err.Number & vbCrLf & _
                "     Error Source: RedefRptSQL" & vbCrLf & _
                "     Error Description: " & err.description, _
                vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Function

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,883
    What determines whether a record is selected or not? If a listbox would be appropriate:

    http://www.baldyweb.com/multiselect.htm

    This would be a simpler way to restrict the report:

    http://www.baldyweb.com/wherecondition.htm
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    shod90 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    96
    Thanks for the fast response but i didn't understand what you meant by these links , I already opened them but that's not the approach of my post .. Please have a min. to review my post again .. I changed the sqlstatment but also with no hope .

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,883
    You didn't answer my question: what determines whether a record is selected?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    shod90 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    96
    I already edited my code to be as below , What determine me to be selected is a checkbox beside each item and if selected should be printed and if not skip this record.
    Code:
    Dim rs As DAO.RecordsetDim i As Long
    Dim task As String
    Set rs = Me.RecordsetClone
    Dim msg, Button, Title, Response
      msg = "Are you sure you want to print these items ? "
      Button = vbYesNo + vbDefaultButton2
      Title = "Easy cash V. 1.0"
      Response = MsgBox(msg, Button, Title)
      If Response = vbYes Then
    If Not (rs.BOF And rs.EOF) Then 'make sure we have records to loop through
        rs.MoveFirst 'make sure we're on the first record
        Do While Not rs.EOF 'keep looping until we reach the end of the recordset
            For i = 1 To rs!qty
                RedefRptSQL "Copy Of BarcodeMaker_4cmSize2", "SELECT Category.Category, Info.Name, * FROM Info, Category INNER JOIN Products ON Category.CatID = Products.Category WHERE (((Products.ItemID)='" & rs!ItemID & "') AND ((Products.[checked])=True));"
                DoCmd.OpenReport "Copy Of BarcodeMaker_4cmSize2", acViewNormal
            Next i
        rs.MoveNext 
        Loop
    End If
    
    
    rs.Close
    Set rs = Nothing
    Else
    End If

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,883
    A field in the data can be problematic in a multiuser environment. You can add a test before the For/Next loop:

    Code:
    If rs!Checked = True Then
            For i = 1 To rs!qty
                RedefRptSQL "Copy Of BarcodeMaker_4cmSize2", "SELECT Category.Category, Info.Name, * FROM Info, Category INNER JOIN Products ON Category.CatID = Products.Category WHERE (((Products.ItemID)='" & rs!ItemID & "') AND ((Products.[checked])=True));"
                DoCmd.OpenReport "Copy Of BarcodeMaker_4cmSize2", acViewNormal
             Next i
    End If
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    shod90 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    96
    OMG , You saved my life thanks alot !!!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,883
    Happy to help!

    I hope you don't have a multiuser environment in which more than one person will be doing this process.

    I also hope you don't have users with only the runtime version of Access. I don't think your function will work, as they wouldn't have design capability. Might not even work if you distribute an accde, but I'm not sure.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-29-2016, 02:12 AM
  2. How to print selected records of a Report
    By Fyeung in forum Reports
    Replies: 3
    Last Post: 02-16-2015, 06:43 PM
  3. How to Print Selected Record
    By indranx in forum Reports
    Replies: 2
    Last Post: 05-31-2011, 08:54 PM
  4. Replies: 2
    Last Post: 09-18-2010, 06:52 AM
  5. Replies: 7
    Last Post: 02-25-2010, 11:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums