Synchronize Combo Boxes

[mindyB]

I am trying to synchronize two, possibly three combo boxes, but I apperntly
don't understand the VBA code given in the example on help.

I am working with plant names which consist of Genus, species, and possibly
subspecies. I want to choose a Genus, then have only appropriate species
names pop up in the second combo box. I have made a table of Genus, a table
of Species, and general data table which has the combination of names
together.

Example: Genus species (real plant names)
Quercus alba
Quercus macrocarpa
Quercus rubra
Abies alba
Salix alba
Abies concolor

As you can see, more than one genus may have the same (species) name, and
each Genus may have many species associated with it

How can I sync these? PLEASE HELP!

 

[Jeff Boyce]

If you have the entire scientific name (multiple names) run in together in a
single field, you're data is not particularly suited for "cascading
comboboxes" (check on-line for this expression). Besides, good database
design suggests "one fact, one field".

If you had a table that had three fields ([Genus], [Species], [SubSpecies],
you could easily use cascading comboboxes to pick a genus, then have the
second combobox filled with only those species that have the selected genus,
and so on...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dale Fye]

I recommend a single table (tbl_Plant_Types) with four fields

an ID field (autonumber)
Genus
Species
Sub-Species

I would use the ID field to store in your table of plants

The RowSource for your Genus combo (cbo_Genus) would look something like:

SELECT DISTINCT Genus
FROM tbl_Plant_Types

The RowSource for your Species combo (cbo_Species) would look like:

SELECT DISTINCT Species
FROM tbl_Plant_Types
WHERE [Genus] = form_YourFormName.cbo_Genus

And the query for the sub-species combo (cbo_SubSpecies) would look like:

SELECT DISTINCT Sub_Species
FROM tbl_Plant_Types
WHERE [Genus] = form_YourFormName.cbo_Genus
AND [Species] = form_YourFormName.cbo_Species

Then, the code in the AfterUpdate of cbo_Genus would look like:

Private Sub cbo_Genus_AfterUpdate

me.cbo_Species.Requery
me.cbo_Species = Null
me.cbo_Sub_Species.Requery

End Sub

The code for cbo_Species AfterUpdate event would look like:

Private sub cbo_Species_AfterUpdate

me.cbo_Sub_Species.requery
me.lbl_Sub_Species.visible = (me.cbo_Sub_Species.listcount > 0)
me.cbo_Sub_Species.visible = (me.cbo_Sub_Species.listcount > 0)

End Sub

 

[Steve]

Hi Mindy,

This is called "cascading comboboxes". You can find something here:
http://www.mvps.org/access/forms/frm0028.htm

Also Google for cascading comboboxes.

Steve
(e-mail address removed)