a2k - populating listbox using array - is there a better way?

Deano

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>

Nov 13 '05 #1

Allen Browne

Try setting the RowSource of the list box to a UNION query that adds the All
Locations as a literal. No code needed.

Something like this:
SELECT LocationID, LocationName FROM tblLocations
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName
ORDER BY LocationName;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Deano" <ma**********@gmail.com> wrote in message
news:41*********************@ptn-nntp-reader03.plus.net...

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>

Nov 13 '05 #2

Deano

Allen Browne wrote:

Try setting the RowSource of the list box to a UNION query that adds
the All Locations as a literal. No code needed.

Something like this:
SELECT LocationID, LocationName FROM tblLocations
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName
ORDER BY LocationName;

Cheers Allen that sounds alot better.

When I try this I get syntax problems and my SQL is a bit flaky so I find it
hard to troubleshoot. Are there any checks or things I should be doing when
assigning a union query like this?

Nov 13 '05 #3

Salad

Deano wrote:

Allen Browne wrote:

Try setting the RowSource of the list box to a UNION query that adds
the All Locations as a literal. No code needed.

Something like this:
SELECT LocationID, LocationName FROM tblLocations
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName
ORDER BY LocationName;

Cheers Allen that sounds alot better.

When I try this I get syntax problems and my SQL is a bit flaky so I find it
hard to troubleshoot. Are there any checks or things I should be doing when
assigning a union query like this?

Change the line
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName
to
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName FROM
tblLocations

IOW, add a FROM table part.

And substitute with your field/table names if the example provided by
Allen is different than your own table/field names.

Nov 13 '05 #4

Bas Cost Budde

Deano wrote:

When I try this I get syntax problems and my SQL is a bit flaky so I find it
hard to troubleshoot. Are there any checks or things I should be doing when
assigning a union query like this?

PMFJI

Syntax errors: you could post the erroneous statement, should become
obvious.

Apart from that, syntax error usually indicates missing comma, missing
space. I tend to get more specific errors when composing SQL statements,
is 'syntax' all you're beaten with?

Nov 13 '05 #5

Deano

Salad wrote:

Change the line
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName
to
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName FROM
tblLocations

IOW, add a FROM table part.

And substitute with your field/table names if the example provided by
Allen is different than your own table/field names.

Thanks very much Salad, that's the ticket. I feel silly for writing all
that code now, ah well, you know what they say about skinning felines...

Nov 13 '05 #6

Allen Browne

Quite correct, Deano. Although JET 4 allows the query:
SELECT 0 AS LocationID, "{All locations}" AS LocationName
it looks like it does not like it in a UNION query.

One work around would be to create a table with these fields:
ID Number Long Integer. Primary key.
IdName Text
Save it as (say) tblCombo.
Enter one record, where ID is zero and IdName is "{All")

Then try a UNION between this and the original table:
SELECT LocationID, LocationName FROM tblLocations
UNION SELECT ID, IdName FROM tblCombo
ORDER BY LocationName;

There's probably a simpler solution, but you can reuse the table with any
combos that have the same structure, and it has the advantage of making it
easy to add extra entries to any of these combos in the entire database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Deano" <ma**********@gmail.com> wrote in message
news:41***********************@ptn-nntp-reader04.plus.net...

Allen Browne wrote:

Try setting the RowSource of the list box to a UNION query that adds
the All Locations as a literal. No code needed.

Something like this:
SELECT LocationID, LocationName FROM tblLocations
UNION SELECT 0 AS LocationID, "{All locations}" AS LocationName
ORDER BY LocationName;

Cheers Allen that sounds alot better.

When I try this I get syntax problems and my SQL is a bit flaky so I find
it
hard to troubleshoot. Are there any checks or things I should be doing
when
assigning a union query like this?

Nov 13 '05 #7