Hello, I have a problem with combo boxes that I am hoping someone can help point me in the right direction. My Access and VBA knowledge is limited but I am very familiar with the concepts of building applications on databases (mostly Oracle).
To keep it simple, I’ll use an analogy of an application to record notes about living things - animals, plants, etc. There is a form to select the classification from a list (I’ll keep it to 2 levels) and enter notes, so I have combo boxes for Kingdom (Animal, Plant, etc.) and Species (Tiger, Bottlenose Dolphin, Spanish Oak Tree, etc.) and a text box for Notes. Each of these fields resides on a different table with the structure and relationship below.
The Kingdom table is:
ID > autonumber, PK
KingdomName > Short Text
Species Table is:
ID > autonumber, PK
SpeciesName > Short Text
KingdomID > number, FK to Kingdom.ID
Notes table is:
ID > autonumber, PK
SpeciesID > number, FK to Species.ID
Notes > Short Text
I want the combo boxes linked for entering new notes so that when I choose ‘Animal’ from the Kingdom combo box, it filters the results in the Species combo box. Then I enter the note and save the record to the Notes table. Now when I scroll back and forth across records, I want the combo boxes to reflect the ‘upstream’ values for each Notes record.
I can get the combo boxes linked together with these properties:
Kingdom Combo Box:
Control Source=Null;
Row Source = SELECT Kingdom.ID, Kingdom.KingdomName FROM Kingdom
After Update Event= Macro to Requery the Family combo Box
Species Combo Box:
Control Source=Notes.SpeciesID;
Row Source= SELECT Species.ID, Species.SpeciesName, Species.KingdomID FROM Species WHERE Species.KingdomID=[Kingdom combo box value]
This works well for entering new records. I can select Kingdom “Animal”, then Species “Tiger” and enter notes about tigers, then save the record just fine. But when I scroll through records, the Kingdom combo box stays on the last value entered (since it is unbound) and the Species combo box will only show the correct value if the current Notes record happens to have a Species value that is valid for the Kingdom value. Otherwise the Species value is blank.
If I bind the Kingdom combo box, then I get errors because Access tries to insert/update records on the Kingdom table.
I thought I could make this work by leaving the Kingdom box unbound and by adding some VBA code to the On Current form event; something like this:
Private Sub Form_Current()
Me.cboKingdom.RowSource = "SELECT ID, KingdomName FROM Kingdom " & _
" WHERE Kingdom.ID = " & Me.cboSpecies
Me.cboKingdom = Me.cboKingdom.ItemData(0)
End Sub
This doesn’t work and I’m sure it’s because I have butchered the syntax (or just don’t know what I am doing). This code will populate the Kingdom box for *some* of the records (which are all different Kingdoms), but not all and I’m not sure why. And it blanks out the Species box for all records. Not to mention it breaks the linkage for data entry.
I feel like I am very close but just missing something small that I haven’t figured out yet. Again, the two part objective is to be able to enter new records and have the Species combo box dependent on the Kingdom combo box; but also be able to view existing records and have both combo boxes display the appropriate values.
Any ideas on what I am missing?
To keep it simple, I’ll use an analogy of an application to record notes about living things - animals, plants, etc. There is a form to select the classification from a list (I’ll keep it to 2 levels) and enter notes, so I have combo boxes for Kingdom (Animal, Plant, etc.) and Species (Tiger, Bottlenose Dolphin, Spanish Oak Tree, etc.) and a text box for Notes. Each of these fields resides on a different table with the structure and relationship below.
The Kingdom table is:
ID > autonumber, PK
KingdomName > Short Text
Species Table is:
ID > autonumber, PK
SpeciesName > Short Text
KingdomID > number, FK to Kingdom.ID
Notes table is:
ID > autonumber, PK
SpeciesID > number, FK to Species.ID
Notes > Short Text
I want the combo boxes linked for entering new notes so that when I choose ‘Animal’ from the Kingdom combo box, it filters the results in the Species combo box. Then I enter the note and save the record to the Notes table. Now when I scroll back and forth across records, I want the combo boxes to reflect the ‘upstream’ values for each Notes record.
I can get the combo boxes linked together with these properties:
Kingdom Combo Box:
Control Source=Null;
Row Source = SELECT Kingdom.ID, Kingdom.KingdomName FROM Kingdom
After Update Event= Macro to Requery the Family combo Box
Species Combo Box:
Control Source=Notes.SpeciesID;
Row Source= SELECT Species.ID, Species.SpeciesName, Species.KingdomID FROM Species WHERE Species.KingdomID=[Kingdom combo box value]
This works well for entering new records. I can select Kingdom “Animal”, then Species “Tiger” and enter notes about tigers, then save the record just fine. But when I scroll through records, the Kingdom combo box stays on the last value entered (since it is unbound) and the Species combo box will only show the correct value if the current Notes record happens to have a Species value that is valid for the Kingdom value. Otherwise the Species value is blank.
If I bind the Kingdom combo box, then I get errors because Access tries to insert/update records on the Kingdom table.
I thought I could make this work by leaving the Kingdom box unbound and by adding some VBA code to the On Current form event; something like this:
Private Sub Form_Current()
Me.cboKingdom.RowSource = "SELECT ID, KingdomName FROM Kingdom " & _
" WHERE Kingdom.ID = " & Me.cboSpecies
Me.cboKingdom = Me.cboKingdom.ItemData(0)
End Sub
This doesn’t work and I’m sure it’s because I have butchered the syntax (or just don’t know what I am doing). This code will populate the Kingdom box for *some* of the records (which are all different Kingdoms), but not all and I’m not sure why. And it blanks out the Species box for all records. Not to mention it breaks the linkage for data entry.
I feel like I am very close but just missing something small that I haven’t figured out yet. Again, the two part objective is to be able to enter new records and have the Species combo box dependent on the Kingdom combo box; but also be able to view existing records and have both combo boxes display the appropriate values.
Any ideas on what I am missing?