Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Stupid Combo Box Question

    I took a crash course in Access XP, and must have missed a lesson.

    I have a form with two combo boxes on it. cmbSpecies pulls from tblSpecies which lists species of animals (Dog, Cat, Horse, Reptile...)

    The next combo is cmbBreed. I want it to pull from either tblDogBreed, tblCatBreed, or whichever one is relevant to the selection in cmbSpecies.

    I know this has to be simple, and I feel stupid for asking, but any assistance is greatly appreciated.

    dave

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have a structure issue.

    You only need two tables, tblSpecies and tblBreed.

    Dog, cat, horse etc can be obtained through the relationship between species and breed. Not only is this method cleaner and more effective, but it solves this "table switching" issue you're looking at right now.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2004
    Posts
    2
    ok, I have one table now with all of the Breeds in it. The columns are ID species and breed. It is called tblBreed. The other table is tblSpecies, and it has ID and Species. Species in both tables is related.

    I still can not figure out how to have the second combo box (cmbBreed) show only the breeds related to the choice picked using cmbSpecies.

    dave

  4. #4
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    You can set up the relationship between the two tables were the SpeciesID is the primary key in the TblSpecies and SpeciesID is the foreign key in the TblBreeds. You can easily do this using the Relationships button on your main menu bar.

    Then write a query to
    SELECT TblBreeds!BreedsField
    FROM TblBreeds
    WHERE forms!YourForm!SpeciesComboBox = BreedsField

    Then use the query to populate the second combo box.
    Hmmm..I haven't tried it, but I believe that you will have to run the query on the OnChange of the first ComboBox.

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    almost there put Adrianna's SQL but id switch the WHERE field to be

    WHERE TblBreeds!BreedsField = forms!YourForm!cmbSpecies
    into the Row Source of your breeds combobox
    and in the cmbSpecies_AfterUpdate event of your put in the code

    cmbBreed.Requery

    hth
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •