Combo box help!

V

VB Clueless

I have created a bird watching db. I have one table called
tblBirdSightings. I have a list table of all the Bird Families
(lstBirdFamilies). I have numerous list tables of the Bird Names that belong
to each bird family (example lstAlbatrosses, lstLoons).
What I'm trying to code is when you select the 'SightedBirdFamily' name from
the combo box then the correct list of birds in that family is listed in the
Sighted Bird Name combo box. If you select Loons then only the Loon bird
names would list in the 'SightedBirdName' combo box. There are 59 bird
families and 59 list tables.
I've tried a few of the other help questions but I'm lost can't get the
coding correct. I'm hoping there is a simple solution?

Thx,
VB Clueless
 
J

John Vinson

On Sat, 5 Feb 2005 13:41:02 -0800, "VB Clueless" <VB
I have created a bird watching db. I have one table called
tblBirdSightings. I have a list table of all the Bird Families
(lstBirdFamilies).
Click to expand...

That's fine...
I have numerous list tables of the Bird Names that belong
to each bird family (example lstAlbatrosses, lstLoons).
Click to expand...

That's NOT.

You should instead have *one single BirdNames* table, with a Family
field. Storing data (a family name for instance) in a Table Name is
*not* proper design; tablenames and fieldnames should refer to a
category of data, not data *values*.

You can run 59 Append queries to migrate your existing data into this
normalized table - or, probably easier, two or three append queries
based on UNION queries:

SELECT "Albatrosses", BirdName FROM lstAlbatrosses
UNION ALL
SELECT "Loons", BirdName FROM lstLoons
UNION ALL
SELECT "Dodos", BirdName FROM lstDodos
UNION ALL
<etc until you get the Query Too Complex error on saving the query>

Save this query and base an APpend query on it to populate BirdNames.
What I'm trying to code is when you select the 'SightedBirdFamily' name from
the combo box then the correct list of birds in that family is listed in the
Sighted Bird Name combo box. If you select Loons then only the Loon bird
names would list in the 'SightedBirdName' combo box. There are 59 bird
families and 59 list tables.
I've tried a few of the other help questions but I'm lost can't get the
coding correct. I'm hoping there is a simple solution?
Click to expand...

If you have a table BirdNames with fields Family and BirdName, you can
use a Query as the rowsource of the SightedBirdName combo box. Create
a new Query

SELECT FamilyName, BirdName FROM BirdNames
WHERE BirdNames.FamilyName = Forms!yourformname!cboFamilyName
ORDER BY BirdName;

where cboFamilyName is the name of the combo box.

You'll need one line of VBA code in cboFamilyName's AfterUpdate event:

Private Sub cboFamilyName_AfterUpdate()
Me!cboBirdName.Requery
End Sub

John W. Vinson[MVP]
 
Y

Youhooo

Thank you so much for your detailed instructions; I'm working on it!

Elena Storm
 
Top