I have a form with a list box showing the user rows he/she can select to generate a report. The user selects multiple rows (or one row, or no row) and those choices are used in another query to base a report on.
In order to do this, the QBE criteria field has to call a function for as many times as I allow the user to make selections in that list box.
So the criteria field in the QBE for the report query looks like this:
List6Items(1)Or List6Items(2)Or List6Items(3)Or(Etc....)
However many times the criteria field makes the function call is the limit to the number of selections a user can make in the forms' list box (List6Items() is the function call - List6 just happens to be the name of the list box; it's not how many selections will be returned).
Now the module where the function code is and determines the value the function returns by looping through the ItemsSelected collection of the list box looks like this:
Option Compare Database
Option Explicit
Private frm As Form, ctl As Control
Private varItm As Variant
Private Count As Integer
-------------------------
Function List6Items(ByVal StringIDtoReturn As Integer)
'frm is the open form
Set frm = Forms![Batt Report Dialog Form]
'ctl is listbox6 on open form
Set ctl = frm!List6
If StringIDtoReturn > ctl.ItemsSelected.Count Then
List6Items = 0
GoTo Done
End If
Count = 1
'set the return value of this function to
'the bound column for each selection made by
'the user in the list box on the open form
'by looping through the collection
'"ItemsSelected", stopping the List6Items
'assignment when the loop iteration equals
'the value passed by the calling procedure...
For Each varItm In ctl.ItemsSelected
List6Items = ctl.ItemData(varItm)
If Count = StringIDtoReturn Then
Exit For
End If
Count = Count + 1
Next varItm
Done:
Set frm = Nothing
Set ctl = Nothing
End Function
--------------------
This works.
But isn't there an easier way of assigning the multiple selections a user made in a list box so's a query can "parse"?
Comments appreciated.
Doug.
In order to do this, the QBE criteria field has to call a function for as many times as I allow the user to make selections in that list box.
So the criteria field in the QBE for the report query looks like this:
List6Items(1)Or List6Items(2)Or List6Items(3)Or(Etc....)
However many times the criteria field makes the function call is the limit to the number of selections a user can make in the forms' list box (List6Items() is the function call - List6 just happens to be the name of the list box; it's not how many selections will be returned).
Now the module where the function code is and determines the value the function returns by looping through the ItemsSelected collection of the list box looks like this:
Option Compare Database
Option Explicit
Private frm As Form, ctl As Control
Private varItm As Variant
Private Count As Integer
-------------------------
Function List6Items(ByVal StringIDtoReturn As Integer)
'frm is the open form
Set frm = Forms![Batt Report Dialog Form]
'ctl is listbox6 on open form
Set ctl = frm!List6
If StringIDtoReturn > ctl.ItemsSelected.Count Then
List6Items = 0
GoTo Done
End If
Count = 1
'set the return value of this function to
'the bound column for each selection made by
'the user in the list box on the open form
'by looping through the collection
'"ItemsSelected", stopping the List6Items
'assignment when the loop iteration equals
'the value passed by the calling procedure...
For Each varItm In ctl.ItemsSelected
List6Items = ctl.ItemData(varItm)
If Count = StringIDtoReturn Then
Exit For
End If
Count = Count + 1
Next varItm
Done:
Set frm = Nothing
Set ctl = Nothing
End Function
--------------------
This works.
But isn't there an easier way of assigning the multiple selections a user made in a list box so's a query can "parse"?
Comments appreciated.
Doug.
