using first list box to populate choices in second list box

[Giz]

Hi,

I have a form and there are two list boxes. The first list box is populated
with site names, and the second list box I want to be populated with
nutrients that were sampled at the selected sites. Not all sites have the
same nutrients, and all the information is found originally in one table.
What I would like is to be able to select a site or sites, and then have the
second list box populated with the nutrients that were sampled at the chosen
site(s). Basically, how do I populate my second list box based on the
choice(s) made in the first list box? I have looked for a tutorial or help on
the subject, but no luck.

Thanx

 

['69 Camaro]

Hi, Giz.

If you're willing to settle for viewing the nutrients sampled from one site
at a time, then it will be pretty simple, as long as the table structure is
normalized. Whichever column the first list box is bound to will be used as
the criteria for the second list box display.

In this example, I'll call the table with the list of sites tblSites. It
has the following structure:

SID, AutoNumber, primary key
SiteName, Text

I'll call the table with the list of nutrients sampled at the various sites
tblNutrients. It has the following structure:

NID, AutoNumber, primary key
Nutrient, Text
SID, Long, foreign key to tblSites

I'll create a new query and name it qrySites:

SELECT *
FROM tblSites
ORDER BY SiteName;

The first list box I'll call lstSites and set the following properties:

Column Count: 2
Column Widths: 0";1"
Row Source Type: Table/Query
Row Source: qrySites
Bound Column: 1

The second list box I'll call lstNutrients and set the following properties:

Column Count: 2
Column Widths: 0";1"
Bound Column: 1

The first list box has the following code in the form's module:

Private Sub lstSites_AfterUpdate()

On Error GoTo ErrHandler

Me!lstNutrients.RowSource = "SELECT NID, Nutrient " & _
"FROM tblNutrients " & _
"WHERE (SID = " & Me!lstSites.Column(0) & ");"
Me!lstNutrients.Requery

Exit Sub

ErrHandler:

MsgBox "Error in lstSites_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where lstSites.Column(0) is the first column of the first list box
(the bound column).

Save and compile the code, then open the form in Form View. Select a site
name in the first list box and the nutrients sampled there will appear in the
second list box. Select another site name in the first list box and the list
of nutrients sampled there will appear in the second list box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Giz]

Hi Camaro,

I need to have the ability to select from more than one site at one time, so
this isn't exactly what I was looking for, but it did point me in the right
direction. What I did was basically create the row source "code" from the
selection(s) made in the listSites, then designated the row source for the
listNutrients through the code you passed on in your post. My finished code
looks something like this:

Private Sub listSite_Exit(Cancel As Integer)
On Error GoTo ErrHandler

Dim rowsrc As String, rowsrcsite As String
Dim i As Integer

'*** create the nutrient list based on selected sites
rowsrc = "SELECT DISTINCT SMP_ALL_Original.nutrient FROM SMP_ALL_Original
WHERE "

rowsrcsite = " Site2 IN( "
For i = 0 To listSite.ListCount - 1
If listSite.Selected(i) Then
rowsrcsite = rowsrcsite & "'" & listSite.Column(0, i) & "', "
End If
Next i
rowsrcsite = Left(rowsrcsite, Len(rowsrcsite) - 2) & ");"
rowsrc = rowsrc & rowsrcsite

Me!listCharacteristic.RowSource = rowsrc
Me!listCharacteristic.Requery

Exit Sub

ErrHandler:

MsgBox "Error does not work"
Err.Clear
End Sub

"SMP_All_original" is my table I am pulling info from. It seems to work. If
you see any faults and want to post them that would be great. Thanx alot
bitchin' Camaro.