Results 1 to 13 of 13
  1. #1
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23

    Pls need hlp with Report

    Hi, I have database with form and report. The form has list of individuals with rows of data. When the data is listed in the form, I can highlighte the rows I want to print to report. However, my issue is that there maybe several rows for one member and when I highlight only one row, all the rows for that member are listed in my report. This is the code that I have now. How can I revise the code to only send to report print the highlighted rows only even if there are other rows for the same member??? Any help with this would be very much appreciated. Thank you.

    Private Sub cmdOpenReport_Click()
    On Error GoTo Err_cmdOpenReport_Click

    Dim strWhere As String
    Dim ctl As Control1
    Dim varItem As Variant

    'make sure a selection has been made
    If Me.lstmember.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
    End If

    'add selected values to string
    Set ctl = Me.lstmember
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
    Next VarItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) -1)
    'open the report, restricted to the selected items


    DoCmd.OpenReport "rpt_fnl_cm_mbrs3", acPreview, , "member IN(" & strWhere & ")"

    Exit_cmdOpenReport_Click:
    Exit Sub

    Err_cmdOpenReport_Click:
    MsgBox Err.Description
    resume Exit_cmdOpenReport_Click

    End Sub

    Err_

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There would need to be another field you could use to identify the selected record. You'd use it either in addition to the member field you're using now, or instead of.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi, thank you so much for your quick response. Ok I found another field that I can add. How do I write that into the code? The filed name is "case_id"??? I need to print to report either rows with ,r,nember and case_id or just member. Can you please show me how to add this to the code above? thank you.
    Last edited by Nyrubi; 03-28-2017 at 09:46 PM.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Me.lstmember is selecting a member, you want it selecting your case_Id. If its broken down to have each case ID on that record it should work.

  5. #5
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    I am sorry but I can't seem to get this to work. Below is the code i have for a list box list box is referenced in the code (lstmember) that has several fields. I replaced the field to Case_ID so that only row highlighted would print to report instead of all the rows associated to the member. This is not working. when I test it I get the following error message. "Syntax error (missing operator) in query expression 'Case_ID In(,)'. Can someone help please? Thank you.

    Private Sub cmdOpenReport_Click()
    On Error GoTo Err_cmdOpenReport_Click

    Dim strWhere As String
    Dim ctl As Control1
    Dim varItem As Variant

    'make sure a selection has been made
    If Me.lstmember.ItemsSelected.Count = 0 Then


    MsgBox "Must select at least 1 employee"
    Exit Sub
    End If

    'add selected values to string
    Set ctl = Me.lstmember
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
    Next VarItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) -1)
    'open the report, restricted to the selected items
    DoCmd.OpenReport "rpt_fnl_cm_mbrs3", acPreview, , "member IN(" & strWhere & ")"

    Exit_cmdOpenReport_Click:
    Exit Sub

    Err_cmdOpenReport_Click:
    MsgBox Err.Description
    resume Exit_cmdOpenReport_Click

    End Sub

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Where is that error message coming from? Your code says "member IN".

  7. #7
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    oops I copied the code above but in my db I changed the member In to read "case_ID IN" and I am getting the error "Syntax error (missing operator) in query expression 'Case_ID In(,)'. I tested further and when I select a row without the case_id I get the message. How can I make it so that whether I choose a case id or a non case id row it will print to the report??? Thank you.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If there's no case ID, how is it to open the report filtered to case ID?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Can it have a combination of Case ID and Member or Member or Case ID. If it can how do I write that into code please? thank you.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure I understand. How about a clear description of what the listbox contains, and what you want to happen based on what may or may not be selected. Attaching the db here may clarify matters as well.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi,

    I can't put copy of db due to company privacy issues. I m going to try and describe what I have. I have form where the list member box pulls the data from query. In the list box, these the fields member, case id, gndr, type, date open, date close, # of days, diag, results, co name, co number.

    The list box will list members and cases for certain company. From the list I have to highlight either cases that have a case number and member number or just a case number or just a member number. Once they are highlighted, I click on a button and print view a report based only on the highlighted items. That is what the code above does but only if there is a member number or if I change the member number to case id. But I need to print view report with a combination of items. I hope you can help me. Thank you.

  12. #12
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi, just want to let you know that the way I figured this out. I created a new field and it is an "autonumber" field and I bound this field to the list box. Changed the code above to read DoCmd.OpenReport "rpt_fnl_cm_mbrs3", acPreview, , "autonumber IN(" & strWhere & ")". Allows me to highlight a field that has a member number and a case id or just case id to print view to report. So far I have tested and all is good. Hope this will help someone else.

    Thank you.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working. That's the type of thing I was suggesting in post 2.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Posting Permissions

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