Trying to sychronise two combo boxes on a form

[cheyanne]

I have a form called frmPetsNew which contains two combo boxes
cboSpecies and cboBreed. The former contains categories such as Dog, Cat, Rabbit, Birds, Reptiles etc,
whilst the latter contains breeds relating to the selected species e.g. If Rabbit is selected then cboBreed should
show Angorra and any other breeds of rabbit. If Cat is selected then cboBreed should show Siamese etc.

I have the cboSpecies combo box working using the following code on its RowSource

    Me.cboBreed.RowSource = "SELECT SpeciesRef FROM" & _
                               " tblBreeds WHERE SpeciesRef = " & _
                               Me.cboSpecies.Value & _
                               " ORDER BY SpeciesRef"
                            
    Me.cboBreed = Me.cboBreed.ItemData(0)

.


I understand that one needs to place some VBA code on the AfterUpdate event
of the cboSpecies object. The code currently reads as follows


When a Species is selected in cboSpecies, cboBreed is blank but seems to allow the depth of
the drop down list to relect the number of breeds for that species. For example
if you select Birds it shows space for the three bird breeds, whereas if you select
Dogs the cboBreed's drop down has a scroll bar to accomodate the many breeds of dogs
entered in that table (tblBreeds).

I have attached two screenshots showing the fields in both the tblSpecies and tblBreeds


tables.

Private Sub cboSpecies_AfterUpdate()
' Update the row source of the cboBreeds combo box
    ' when the user makes a selection in the cboSpecies
    ' combo box.
    
    Me.cboBreed.RowSource = "SELECT SpeciesRef FROM" & _
                               " tblBreeds WHERE SpeciesRef = " & _
                               Me.cboSpecies.Value & _
                               " ORDER BY SpeciesRef"
                            
    Me.cboBreed = Me.cboBreed.ItemData(0)
End Sub

.

I would appreciate a perusal of my code and any advise that can be offered to resolve this matter, would.
be greatly appreciated.

I did try placing a cboBreed.Requery command, on the forms current event, and also
on the cboSpecies LostFocus event but to no avail. I presume that the bound column on each
combo box should be set to 1, which is the numerical reference field for each combo.

But of course cboBreed needs to show the names of the breeds - relative to that number reference. I did actually
have the cboBreeds showing the numbers of the numerical references but no names were shown.

The latest position is that if you close the form with say Dogs selected in cboSpecies, then upon opening
the form it shows the names of all the breeds of dog contained in the table. However if one then selects say
Cats then the correct number of entries are shown but with just the reference for the Cats category (number 1) showing.
If you then return to the Dogs category, as your selection, it shows the number 2 for each of the names of the dog breeds stored.

Regards


Cheyanne

[orange]

There is a free video tutorial on Cascading combo boxes at
http://www.datapigtechnologies.com/f...combobox2.html

[R_Badger]

I think we need to know a bit more about your table structure. but in your rowsource SQL for cbobreed

Private Sub cboSpecies_AfterUpdate() ' Update the row source of the cboBreeds combo box ' when the user makes a selection in the cboSpecies ' combo box. Me.cboBreed.RowSource = "SELECT SpeciesRef FROM" & _ " tblBreeds WHERE SpeciesRef = " & _ Me.cboSpecies.Value & _ " ORDER BY SpeciesRef" Me.cboBreed = Me.cboBreed.ItemData(0) End Sub

You are only selecting the species reference, and not knowing where/how that translates to a name makes it a little hard to help much, but what you need to do is store the ref but show the name which is more than do-able.

You use a formatting trick to do it. set the columns to 2, and then the column width for the first (bound column for speciesref in this case) to 0, and the second to an appropriate width. it will then show you the name, and store the ID.

I hope that actually makes sense.

[cheyanne]

Hi

Many thanks for your prompt reply. (Sorry but I am not sure how to address you).

The situation is that I have two tables tblSpecies and tblBreeds. (I have attached their respective compostion as screenshots)

tblSpecies has an autonumber field (SpeciesRef) and a Description field (Species)

tblBreeds (plural) has a TypeRef field, which is the autonumber field, a BreedName field which stores the name of the breeds of the
various Species and finally a SpeciesRef which is identical to the the Species Ref field in tblSpecies and needs to be the linking.
field.

Currently the SpeciesRef field has 7 rows which identify categories such as Cat, Dog etc (see screenshot Species.JPG)
The tblBreeds table currently has 163 entries each of which is linked to one of the 7 rows found in tblSpecies. The Dogs
category has the vast majority of these records.

) he problem at the moment seems to suggest that cboBreeds is not refreshing or requerying (not sure what the difference is) when I select a new species.
If, for example, when I close the form the species field is set to Dog, then when I re-open the form, the species combo box is set to
Dogs and the Breed combo shows the full list of dogs and ONLY the dogs.

However if I then choose say Cats, I end up with a blank drop down list. If I then save and close the form and then re-open it again
the Species combo now shows Cats and the Breed combo, when I select it, shows the list of all the breeds of cats stored and again ONLY
the cats.

I hope this assists your observations.

Regards

Cheyanne

[orange]

Can you post the code behind you combo boxes---? The Event code.
Did you watch the video?

I'm working on something else-will look at your jpgs shortly.

[cheyanne]

Hi

Yes I did watch the video but I do not think that it replicates my situation.

The prblem seems to be that the second combo box cboBreed does not refresh or requery
when I select another category from the cboSpecies combo box - see previous replies and responses.

I waas not sure how to do what you wanted but I have tried. (See attachment) If this is not what you needed please
advise upon how I can achieve what you seek.

Regards

Cheyanne

[Epidural]

The VBA code the SpeciesRef combo box is not "Me.SpeciesRef.Value" it is simply Me!SpeciesRef or Forms![FormName]![ControlName] . That will return the value associated with the combo box's bound column. I suggest coding the SQL for the control source into the combo box, not in VBA, then having in your VBA:

Private Sub cboSpecies_AfterUpdate
  Me.cboBreeds.Requery
End Sub

I have several combo boxes set up this way.

As for what Badger was saying, you must ensure your species combobox SQL returns two values: the SpeciesID and the Species Name. One is for the database, one is for the user. Use the Bound Column property of the combo box to bind its stored value to the ID (the 1st column if you order it as such). Then set the width of the columns as "0cm;3cm" or something; "firstwidth;secondwidth". This hides the ugly ID from the user but not from the code. The code then uses the BOUND column (first, the ID) to use in the SQL (Forms.[YourFormName].[cboSpecies] returns the ID#). Use the WHERE clause to search for the species ID within the breeds table in your SQL. Should work. Your Control Source SQL would then look like

SELECT BreedID, BreedName FROM tblBreed WHERE SpeciesRef = Forms.[YourFormName].[cboSpecies]

Make sure your second combo box (the breeds box) is set up similarly; ensure the columns are not hidden and return the right values. It should be the exact same as the previous (ID first, bind first, hide first as 0cm)