Results 1 to 9 of 9
  1. #1
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35

    Listbox selections not showing in my report

    Hi all. I have created a report with a form that has a list box with a list of customers. I have the listbox set so that the user can select more than one customer name. However, when I select a few names, then view the report, I get no results. A copy of the database is attached (with dummy data). User needs to select multiple customers and the information for these customers needs to show in Master Customer Report

    Query name : Master Customer Report Query
    Form name: MasterCustomerSearch

    Have I mentioned that I love this Forum ?!
    Attached Files Attached Files

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    984
    The attached db doesn't have tables, queries, or reports.

  3. #3
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35
    The file is zipped. Has to be unzipped. There are definitely tables, etc in the file. I had to zip it, otherwise I could not attach

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,065
    I would walk through the listbox and concatenate any selected, then use that with an IN() clause.

    https://www.google.com/search?q=use+...client=gws-wiz
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    984
    Quote Originally Posted by DAWNY2007 View Post
    The file is zipped. Has to be unzipped. There are definitely tables, etc in the file. I had to zip it, otherwise I could not attach
    There is definitely not tables etc in the file I downloaded and unzipped... only 4 forms.

    Anyway, here is a starting point

    Code:
    Private Sub MasterCustomerReport_Click()
        Dim criteria As String
        Dim i As Integer
        
        'loop through listbox and build a filter string
        With Me.MasterCustomerSelect
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    criteria = criteria & """" & .ItemData(i) & """, "
                End If
            Next i
        End With
        
        'check the criteria we built
        If criteria <> "" Then
            'remove the trailing ', ' from the filter string
            criteria = Left(criteria, Len(criteria) - 2)
        Else
            'it appears nothing was selected
            MsgBox "Please select one or more customers"
            Exit Sub
        End If
        
        'Debug.Print criteria
        DoCmd.OpenReport "Master Customer Report", , , "[SOME FIELD NAME] IN (" & criteria & ")"
        
    End Sub
    [edit]
    ok nvm, they were hidden from navigation. My mistake

    Replace [SOME FIELD NAME] with [MASTER CUSTOMER] in my code above and remove the criteria from the base query.
    Code:
    DoCmd.OpenReport "Master Customer Report", , , "[SOME FIELD NAME] IN (" & criteria & ")"
    Should be
    Code:
    DoCmd.OpenReport "Master Customer Report", acViewPreview, , "[MASTER CUSTOMER] IN (" & criteria & ")"

  6. #6
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35
    Fantastic ! Thank you !

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    984
    No problem.

    I couple notes though: I would have expected the master customer list (table) to have a primary key field. I would then have expected the listbox to have 2 columns, one of which is a hidden and contains the keys. Finally I would have built the report criteria off of those keys rather than the strings. It's easier on the poor old computer to search for primary keys numbers than strings.

    It may not make any sort of practical difference in your particular circumstance.

    Also, spaces in object names like tables, reports, form controls, field names, etc. are a no-no. It's usually advised to stick with something like CamelCase or use underscores instead of spaces.

  8. #8
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    979
    Hi

    I hope that this database is not in operational use?

    Only 1 of your tables has an Autonumber Primary Key. It is recommended that all tables have an Autonumber Primary Key.

    None of your tables has Referential Integrity Enforced?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,653
    Never mind. For some reason I was looking at an out of date browser tab.

    Also, spaces in object names like tables, reports, form controls, field names, etc. are a no-no. It's usually advised to stick with something like CamelCase or use underscores instead of spaces.
    Add special characters to that.

    There are repeated fields as well (e.g. State in at least 3 tables, the worst of which would be turbine data table). That does not belong in there at all, for sure.
    My suggestion would be to stop and correct the many mistakes and oversights before going any further.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 12
    Last Post: 04-16-2018, 11:31 AM
  2. multi listbox selections to table
    By bigmac59 in forum Forms
    Replies: 1
    Last Post: 02-19-2016, 03:05 PM
  3. Replies: 2
    Last Post: 01-31-2016, 08:08 PM
  4. Listbox Selections to appear on Reports
    By Atoga in forum Reports
    Replies: 8
    Last Post: 04-30-2015, 10:55 PM
  5. projects database save listbox selections
    By taya621 in forum Access
    Replies: 33
    Last Post: 01-21-2011, 10:56 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
  •  
Other Forums: Microsoft Office Forums