Results 1 to 4 of 4
  1. #1
    Sonu is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2019
    Posts
    35

    MS Access 2016 - List Box selection of a Form to display in Report Header

    Hello Guys,

    I need help in writing a control source of a report to display the list box selection from a MS Access Form.

    A form has a List Box (unbound) which contains 2 options (user can either select one or both). The Row Source of this List Box is a SQL query. Sample below:

    SELECT tblCompanies.CompanyID, tblCompanies.CompanyName
    FROM tblCompanies;

    This form contain a button that runs/opens a Report and I would like to display the List Box selection on top as Report Header. For this, I have created a text box and have put the following in the Control Source property of it:

    ="Company - " & [Forms]![frmApprovalReport]![lstCompany].[Column](1)

    When I open the form for the first time, make a selction, and hit the button, the report displays the selected option. But if I de-select it and choose the 2nd option, the report still shows the selection made in the fist attempt. I am able to succeed with this code, only if I close the form and open it back. But if I stay on the same form and try to play around with list selection, the report always displays the option selected in the first attempt.

    Can someone help in tweaking this code to display correct selection even if I stay on the form and choose my options?

    List Box code (VB):



    If lstCompany.ItemsSelected.Count > 0 Then
    For Each varItem In lstCompany.ItemsSelected
    strCompany = strCompany & "Company = " & Chr(34) & lstCompany.ItemData(varItem) & Chr(34) & " OR "
    Next
    strCompany = "(" & Left(strCompany, Len(strCompany) - 4) & ")"
    strWhere = strCompany
    End If

    Thank You.
    Attached Thumbnails Attached Thumbnails MS Access 2016 - Report Control Source.png  

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    You will need to RE-open the report as the report opens as a snapshot.

  3. #3
    Sonu is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2019
    Posts
    35
    Thank you, your point has been noted. But when I am selecting both the list box items, the report is only displaying one. How do I change my code to capture items based on selection either one or both?

  4. #4
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    You mean you are adding items. Have a look at...
    http://www.baldyweb.com/multiselect.htm

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

Similar Threads

  1. Replies: 5
    Last Post: 05-03-2018, 08:53 AM
  2. Replies: 4
    Last Post: 03-03-2018, 06:35 PM
  3. Replies: 1
    Last Post: 07-31-2016, 03:13 AM
  4. Replies: 4
    Last Post: 10-13-2014, 02:18 AM
  5. Replies: 3
    Last Post: 04-07-2010, 11:25 AM

Tags for this Thread

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