Solved Unbound Linked Combo Boxes Not Behaving (1 Viewer)

B99

Member
Local time
Today, 11:23
Joined
Apr 14, 2020
Messages
30
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?
 

bastanu

AWF VIP
Local time
Today, 08:23
Joined
Apr 13, 2010
Messages
1,401
In the current event of the form try this:

Me.cboSpecies=dlookup("SpeciesName","Species","SpeciesID=" & Me.SpeciesID)
Me.cboKingdom=dlookup("KingdomName","Kingdom","ID=" & dlookup("KingdomID","Species","SpeciesID=" & Me.SpeciesID)

Cheers,
Vlad
 

B99

Member
Local time
Today, 11:23
Joined
Apr 14, 2020
Messages
30
Hi Vlad - Thanks for the reply! I tried your approach but unfortunately couldn't get it to work. I made a couple of small changes and now I don't get any errors but I also don't have consistent results. Here's what I ended up with:

Me.cboSpecies=dlookup("[ID]","Species","ID=" & Me.SpeciesID)
Me.cboKingdom=dlookup("[ID]","Kingdom","ID=" & dlookup("KingdomID","Species","ID=" & Me.SpeciesID)

The combo boxes are bound to the ID fields (I probably should have stated that) so I think this is close. But here is the odd behavior:
If I close the form and open it, the Kingdom values show up correctly for all records, but the Species values show up for only 2 records.
If I add a new record, I can select the Kingdom and Species and the form correctly saves the Species ID to the new record in the Notes table. (Incidentally adding a new record gives me an error with the current code and I assume I will need to add a condition to check if it's a new record before executing the code.) Now, while I have the form open after adding a new record, I can scroll through the existing records and the Kingdoms are all correct, but the Species only shows up for 3 records: the same two from before and the one I just added.
If I close the form and go back in again, I'm back to only 2 records. The most recently added no longer shows a value for Species.

The inconsistency is what is baffling me and making it hard to know where to look. Any thoughts?
 

B99

Member
Local time
Today, 11:23
Joined
Apr 14, 2020
Messages
30
I was wrong about one thing - there is consistency in the Species values that are returned: they are all for the same Kingdom value. I thought they were different but they are not. Still digging...
 

bastanu

AWF VIP
Local time
Today, 08:23
Joined
Apr 13, 2010
Messages
1,401
I think you need to add a Me.cboSpecies.Requery and flip them around

Me.cboKingdom=dlookup("[ID]","Kingdom","ID=" & dlookup("KingdomID","Species","ID=" & Me.SpeciesID)
Me.cboSpecies.Requery 'to reset the row source
Me.cboSpecies=dlookup("[ID]","Species","ID=" & Me.SpeciesID)

Cheers,
Vlad
 
  • Like
Reactions: B99

B99

Member
Local time
Today, 11:23
Joined
Apr 14, 2020
Messages
30
That worked! I have a requery on the After Update event for Kingdom but incorrectly thought it would fire in this case. Now it is working exactly how I wanted and I learned something new.

Thank you for your help Vlad! Much appreciated!
 

bastanu

AWF VIP
Local time
Today, 08:23
Joined
Apr 13, 2010
Messages
1,401
You're very welcome, stay safe!
Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom