Cascading Combo in Subform - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Cascading Combo in Subform

Chris Pardy

4 years ago

Hi. I have a table with a list of technicians, and a table for roles. In the technicians table I have a field where I can select multiple roles that that person has been trained on. this is stored in Roles.Value

Elsewhere I have form with a nested subform. from this subform I can select a Role from a combo box, and I want the technicians combo box to only return names that have been trained on the previously selected role. I've followed the nested combo box video. and the update found here https://599cd.com/blog/display-article.asp?ID=1749. I can get the combobox to return the names, but then its not soring the ID which is a one of 2 primary key fields in the table.

	Scott Axton @Reply 4 years ago
Rather than play 20 questions, it is often helpful if you post screenshots of your forms Combo Box , code behind it, etc in design mode that would help us visualize what you are doing. Image Uploads It sounds to me like you didn't construct the combo as you think you have. Specifically show us the Combo Box and the associated properties.

	Scott Axton @Reply 4 years ago

	Chris Pardy OP @Reply 4 years ago
Hi, ok I've made some progress. but still not quite there. I changed the way I was relating tblTechnicians and tblRoles by creating a join table and adding the roles via a subform. The form that my cascading boxes are on is sfrmTechs.

	Chris Pardy OP @Reply 4 years ago

	Chris Pardy OP @Reply 4 years ago

	Chris Pardy OP @Reply 4 years ago

	Chris Pardy OP @Reply 4 years ago

	Chris Pardy OP @Reply 4 years ago

	Chris Pardy OP @Reply 4 years ago

	Chris Pardy OP @Reply 4 years ago
I am getting the correct ID's in my combo box, but I need to return the FullName from tblTechnicians.. and the Dlookup Text box is not working.

	Kevin Robertson @Reply 4 years ago
Chris, Looking at your Relationships, why do two of your tables have two Primary Keys? What is FullName? Is it FirstName and LastName concatenated together? If so, why not do that in a Query instead of storing it in the table? Instead of Text Box to display FullName, why not display it in the TechCombo Combo Box?

Chris Pardy OP @Reply

4 years ago

Hi Kevin,

The tables with 2 primary keys, I was taught that in a join table that the the 2 keys should be set a the primary key so a record with the same 2 keys present can occur only once.

Yes Full Name is just a calculated field, I don't understand how I could have it appear in the combo box as the full name otherwise.

The text box with the dlookup is there because that is the direction I have from this video https://www.599cd.com/blog/display-article.asp?ID=1749

	Scott Axton @Reply 4 years ago
*"Yes Full Name is just a calculated field, I don't understand how I could have it appear in the combo box as the full name otherwise. "* I suggest you watch / re-watch: Relational Combo and Concatenation

	Chris Pardy OP @Reply 4 years ago
Hi Scott, Thanks for that.. I think in this case I cant use the query for the combo box because its on the continuous form? the join table that the form is based on only has the TechnicianID, and the RoleID stored is that why I cant get the name associated with the ID in the dlookup text box ?

	Chris Pardy OP @Reply 4 years ago
Hi Kevin... Is the method of have 2 Primary Keys in a join table not correct ? I only did it that way because the beginner class I took at Udemy said so.

	Alex Hedley @Reply 4 years ago
Access Tip: Composite Keys

	Chris Pardy OP @Reply 4 years ago
If i am understanding correctly, my 2 issues now are, what is the SQL syntax i need in the row source of TechCombo to Concatinate a name from the related TechniciansID table, and why am I getting the error in the DLookup text box ? Thanks again.

	Kevin Robertson @Reply 4 years ago
After you finish the Beginner Series I recommend the following to help with your issue. Access Expert 1 covers Relational Combo Boxes. Access Expert 3 covers SQL Basics.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View