I needed to have a listbox populated by locations which are stored in
tblLocations.
However I wanted an "All locations" entry to be at the top of the listbox.
This is not in the tblLocations.
The resulting list will be used to provide a query parameter and the user
can choose all locations (the default) or a single, specific location.
In the open event of the form containing the listbox I wrote the following
code to achieve this. Could this have been done better? I did this quite
quickly and it works but I'm not sure whether I'm following best practice.
Any pointers appreciated since my gut feeling is that people might find
themselves having to create something similar every so often.
ta
Martin
<start of code>
Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryAssetLocations", dbOpenDynaset) 'get list of
unique locations
'count number of locations
rs.MoveLast
Dim LocationCount As Integer
LocationCount = rs.RecordCount
LocationCount = LocationCount + 1 'needs increasing as we need an extra item
to store "All locations"
'create dynamic array and redim
Dim locArray() As String
ReDim locArray(1 To LocationCount)
locArray(1) = "All locations"
rs.MoveFirst 'now start by grabbing locations from the beginning
Dim i As Integer 'counter
i = 2 'start with 2nd item in array as first is now taken
'load array with rest of location values.
Do While Not rs.EOF
locArray(i) = rs.Fields(0)
i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'Put array values into listbox using a value list for the rowsource
'first declare recordsource type
Me.lstAssetLocations.RowSourceType = "Value List"
'now build up string from array
Dim assetString As String 'this will hold the list of locations
For i = 1 To LocationCount
assetString = assetString & locArray(i) & ";"
Next
'now assign list of locations as the rowsource
Me.lstAssetLocations.RowSource = assetString
'ensure select first item, "All locations" is already selected when form
opens.
Me.lstAssetLocations = Me.lstAssetLocations.ItemData(0)
End Sub
<end of code>