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.