Solved How to reference a non-bound column of combobox in the rowsource query for another combobox? (1 Viewer)

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
I've got a peculiar situation that for the life of me cannot figure out how to do properly.

The situation is as follows:
- I have two subforms that are bound to the same parent form.
- Subform A has three comboboxes that draw values from a types vocabulary. All three are bound to hidden column A (the ID), and display column B (the name of the type).
- Subform B has one combobox that has five columns as follows: Column A - hidden (the ID of Subform A, this is the bound column for the combobox too), Column B, C, D - showing (the types of the three comboboxes of Subform A), Column E - hidden (the Parent form ID used to filter the rowsource for the Subform B combobox).
- When referencing the columns B-D in the query for the rowsource of the Subform B combobox, it of course displays the IDs and not the name of the types.

Is there any way to reference the non-bound column of the comboboxes in Subform A in the query for the combobox in Subform B?

Thank you for any help. If I come up with a solution myself, I'll share asap. The accompanying screenshot may give a visual clue as to what I'm trying to do too.

PS: Per discussion below, I thought it would be prudent to also provide the underlying structure for clarity. Subform A (Social Uses) is Tbl_Dataunits_Social_Uses, and Subform B (Social Uses to Actors) is Tbl_Dataunits_Social_Uses_Actors. As I said Subform A draws from a vocabulary, and both are related to a parent form/table Dataunits. Any field with an ID in the name is a numbers field type.
 

Attachments

  • Screenshot.png
    Screenshot.png
    11.5 KB · Views: 15
  • Screenshot2.png
    Screenshot2.png
    23.9 KB · Views: 13
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
Hi. Check out the Column() property of the Combobox. For example, to display the third column of a multi-column combobox, you would use something like:

ComboboxName.Column(2)

The index is zero-based (2 refers to the 3rd column).

Hope that helps...
 
Last edited:

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
Hi. Check out the Column() property of the Combobox. For example, to display the third column of a multi-column combobox, you would use something like:

ComboboxName.Column(2)

The index is zero-based (2 refers to the 3rd column).

Hope that helps...
But the Column() property seems to work only when referencing a combobox in VBA/macros, it does not work in the underlying query for the rowsource for the second combobox. I've tried injecting the SQL code with VBA using the Column() property, but it doesn't work either.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
But the Column() property seems to work only when referencing a combobox in VBA/macros, it does not work in the underlying query for the rowsource for the second combobox. I've tried injecting the SQL code with VBA using the Column() property, but it doesn't work either.
That sounds like you're using Lookup Fields at the Table level. Is that true? If so, I would suggest you stop doing it. See this article for some reasons why.

 

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
That sounds like you're using Lookup Fields at the Table level. Is that true? If so, I would suggest you stop doing it. See this article for some reasons why.

Thank you for trying to help theDBguy.

I'm not using lookup fields at the table level, I avoid them in general. The underlying structure for the tables is provided in the screenshot. Subform A (Social Uses) is Tbl_Dataunits_Social_Uses, and Subform B (Social Uses to Actors) is Tbl_Dataunits_Social_Uses_Actors. As I said Subform A draws from a vocabulary, and both are related to a parent form/table Dataunits.

PS: Any field with an ID in the name is a numbers field type.
 

Attachments

  • Screenshot2.png
    Screenshot2.png
    23.9 KB · Views: 15

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
Thank you for trying to help theDBguy.

I'm not using lookup fields at the table level, I avoid them in general. The underlying structure for the tables is provided in the screenshot. Subform A (Social Uses) is Tbl_Dataunits_Social_Uses, and Subform B (Social Uses to Actors) is Tbl_Dataunits_Social_Uses_Actors. As I said Subform A draws from a vocabulary, and both are related to a parent form/table Dataunits.

PS: Any field with an ID in the name is a numbers field type.
Hi. That's good to know. So, why were you worried about using the Column() property in a query? Your original post shows an image of a Form. So, that means you would be using this solution/approach in a Form anyway, correct?

Besides, I'm pretty sure the Column() property works in a query too.
 

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
Hi. That's good to know. So, why were you worried about using the Column() property in a query? Your original post shows an image of a Form. So, that means you would be using this solution/approach in a Form anyway, correct?

Besides, I'm pretty sure the Column() property works in a query too.
This is what is under the rowsource for the Combobox in Subform B:
SQL:
SELECT Tbl_Dataunits_Social_Uses.ID, SocialUseID_Main AS [Main Type], SocialUseID_SubI AS [Subtype I], SocialUseID_SubII AS [Subtype II], Tbl_Dataunits_Social_Uses.DataunitID
FROM Tbl_Dataunits_Social_Uses
WHERE (((Tbl_Dataunits_Social_Uses.DataunitID)=[Forms]![Frm_Interviews].[Form]![Frm_Dataunits_Interviews].[Form]![Frm_Dataunits_Social_Uses].[Form]![DataunitID]));

Is this what you propose to use?
SQL:
SELECT Tbl_Dataunits_Social_Uses.ID, SocialUseID_Main.Column(1) AS [Main Type], SocialUseID_SubI.Column(1) AS [Subtype I], SocialUseID_SubII.Column(1) AS [Subtype II], Tbl_Dataunits_Social_Uses.DataunitID
FROM Tbl_Dataunits_Social_Uses
WHERE (((Tbl_Dataunits_Social_Uses.DataunitID)=[Forms]![Frm_Interviews].[Form]![Frm_Dataunits_Interviews].[Form]![Frm_Dataunits_Social_Uses].[Form]![DataunitID]));

I tried, but it doesn't work. Unless I'm doing something wrong. :unsure:
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
This is what is under the rowsource for the Combobox in Subform B:
SQL:
SELECT Tbl_Dataunits_Social_Uses.ID, SocialUseID_Main AS [Main Type], SocialUseID_SubI AS [Subtype I], SocialUseID_SubII AS [Subtype II], Tbl_Dataunits_Social_Uses.DataunitID
FROM Tbl_Dataunits_Social_Uses
WHERE (((Tbl_Dataunits_Social_Uses.DataunitID)=[Forms]![Frm_Interviews].[Form]![Frm_Dataunits_Interviews].[Form]![Frm_Dataunits_Social_Uses].[Form]![DataunitID]));

Is this what you propose to use?
SQL:
SELECT Tbl_Dataunits_Social_Uses.ID, SocialUseID_Main.Column(1) AS [Main Type], SocialUseID_SubI.Column(1) AS [Subtype I], SocialUseID_SubII.Column(1) AS [Subtype II], Tbl_Dataunits_Social_Uses.DataunitID
FROM Tbl_Dataunits_Social_Uses
WHERE (((Tbl_Dataunits_Social_Uses.DataunitID)=[Forms]![Frm_Interviews].[Form]![Frm_Dataunits_Interviews].[Form]![Frm_Dataunits_Social_Uses].[Form]![DataunitID]));

I tried, but it doesn't work. Unless I'm doing something wrong. :unsure:
No, I don't think that's what I meant. I thought you simply want to display the other columns from the first combobox. If so, you could use an unbound textbox using the Column() property. Otherwise, if you're talking about limiting the list of another combobox based on the first combobox, then you're talking about using a "cascading combobox." If so, there are several tutorials on how to do that.

Or, if you're trying to display the other columns from the first combobox in the second combobox dropdown, then you should be able to either join the tables in your row source or use DLookup().

If you can post a sample copy of your db, we should be able to quickly tell you which approach is the most appropriate for your goals.
 

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
No, I don't think that's what I meant. I thought you simply want to display the other columns from the first combobox. If so, you could use an unbound textbox using the Column() property. Otherwise, if you're talking about limiting the list of another combobox based on the first combobox, then you're talking about using a "cascading combobox." If so, there are several tutorials on how to do that.

Or, if you're trying to display the other columns from the first combobox in the second combobox dropdown, then you should be able to either join the tables in your row source or use DLookup().

If you can post a sample copy of your db, we should be able to quickly tell you which approach is the most appropriate for your goals.

I think a copy of the db would be the best way to move forward. I've trimmed all unrelated to the issue data, code, tables, forms, etc. for online use. On the form that will open, you will see the issue in the Analysis tab. The "Social Use" combobox of the "Social Uses to Actors Relationships" should be bound to the ID of the "Social Use" subform, but display the terms as seen in the "Social Use" subform, so as to make the system usable to a human user (one cannot remember all the IDs assigned to types). Thank you again for the eagerness to help! :)

With your consent, if a solution is found, I think it should be posted in the thread too, as many people might be searching for a similar solution to a similar issue.
 

Attachments

  • DB_Online_Help.accdb
    1.1 MB · Views: 14

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
Hi. Thanks for posting a sample db. I had to step out of the office, but someone else should be along to help soon.

Sent from phone...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
Hi. I'm back in the office and looking at your db now. I just wanted to let you know, this is what I was talking about earlier with regards to "lookup fields." You are definitely using them.

1620076452286.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
In any case, take a look at the attached modified version of your db. I only changed it enough to show the first column and hoping you would be able to do the rest. If not, please let us know.
 

Attachments

  • DB_Online_Help.zip
    207.7 KB · Views: 14

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
Hi. I'm back in the office and looking at your db now. I just wanted to let you know, this is what I was talking about earlier with regards to "lookup fields." You are definitely using them.

View attachment 91283
Left-over from experimentation. Sorry. The original just says Text Box at the top. I was toying with the idea that maybe if I set the field in the table with the lookup, it will carry the values over to the query.
 

Attachments

  • Screenshot3.png
    Screenshot3.png
    2.8 KB · Views: 15

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
Left-over from experimentation. Sorry. The original just says Text Box at the top. I was toying with the idea that maybe if I set the field in the table with the lookup, it will carry the values over to the query.
Okay, that's good to know. Have you had a chance to examine the file I posted earlier?
 

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
Okay, that's good to know. Have you had a chance to examine the file I posted earlier?
Just looked at it right now! Amazing and elegant solution that I had no idea you could actually do without causing system stress. Thank you for sharing. I was able to connect all three values and also changed the relationship to a Right join to avoid having values not showing up due to Nulls.

For anyone interested in this topic the SQL code is as follows:
SQL:
SELECT Tbl_Dataunits_Social_Uses.ID, Voc_Social_Uses.Social_Use_Name AS [Main Type], Voc_Social_Uses_1.Social_Use_Name AS [Subtype I], Voc_Social_Uses_2.Social_Use_Name AS [Subtype II], Tbl_Dataunits_Social_Uses.DataunitID
FROM Voc_Social_Uses AS Voc_Social_Uses_2 RIGHT JOIN (Voc_Social_Uses AS Voc_Social_Uses_1 RIGHT JOIN (Voc_Social_Uses RIGHT JOIN Tbl_Dataunits_Social_Uses ON Voc_Social_Uses.ID = Tbl_Dataunits_Social_Uses.SocialUseID_Main) ON Voc_Social_Uses_1.ID = Tbl_Dataunits_Social_Uses.SocialUseID_SubI) ON Voc_Social_Uses_2.ID = Tbl_Dataunits_Social_Uses.SocialUseID_SubII
WHERE (((Tbl_Dataunits_Social_Uses.DataunitID)=[Forms]![Frm_Interviews].[Form]![Frm_Dataunits_Interviews].[Form]![Frm_Dataunits_Social_Uses].[Form]![DataunitID]));

For a hands-on solution see the attached db by theDBguy in the comment above. Thank you again for sharing the knowledge! :D(y)👏
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
15,268
Just looked at it right now! Amazing and elegant solution that I had no idea you could actually do without causing system stress. Thank you for sharing. I was able to connect all three values and also changed the relationship to a Right join to avoid having values not showing up due to Nulls.

For anyone interested in this topic the SQL code is as follows:
SQL:
SELECT Tbl_Dataunits_Social_Uses.ID, Voc_Social_Uses.Social_Use_Name AS [Main Type], Voc_Social_Uses_1.Social_Use_Name AS [Subtype I], Voc_Social_Uses_2.Social_Use_Name AS [Subtype II], Tbl_Dataunits_Social_Uses.DataunitID
FROM Voc_Social_Uses AS Voc_Social_Uses_2 RIGHT JOIN (Voc_Social_Uses AS Voc_Social_Uses_1 RIGHT JOIN (Voc_Social_Uses RIGHT JOIN Tbl_Dataunits_Social_Uses ON Voc_Social_Uses.ID = Tbl_Dataunits_Social_Uses.SocialUseID_Main) ON Voc_Social_Uses_1.ID = Tbl_Dataunits_Social_Uses.SocialUseID_SubI) ON Voc_Social_Uses_2.ID = Tbl_Dataunits_Social_Uses.SocialUseID_SubII
WHERE (((Tbl_Dataunits_Social_Uses.DataunitID)=[Forms]![Frm_Interviews].[Form]![Frm_Dataunits_Interviews].[Form]![Frm_Dataunits_Social_Uses].[Form]![DataunitID]));

For a hands-on solution see the attached db by theDBguy in the comment above. Thank you again for sharing the knowledge! :D(y)👏
Hi. You're welcome. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:41
Joined
Feb 19, 2002
Messages
31,996
I can't tell what theDBguy changed since I only looked at his version.

Looks to me like the issues with the schema still exist. There is a repeating group in tbl_Dataunits_social_uses where you have THREE instances of the same piece of data. I'm not sure why three is the magical number but could there ever be 2 or 6? Whenever you have more than one of something, you have many and "many" requires a separate table so you need what we call a junction table between tbl_dataunits_social_uses and voc_social_uses. That will allow you to support 0 or thousands not just three. If you wanted to store the children of a person, would you add x columns to the person table? How would you know how many columns to add? What would happen if you didn't add enough? That is why we do not create tables like this (among other reasons).

There also is a circular reference problem probably caused by including grandparent's foreign keys as well as a parent's in the same table. Specifically, DataunitID probably does not belong in three tables - tbl_dataunits_actorstbl_dataunits_social_users_Actors and in tblDataunits_social_uses but I can't tell by the column names or the data what the structure actually should be.

It is not wrong to put value lists in tables. It just makes the lists hard to manage. You, the programmer, become the keeper of the lists since you will have to be involved any time a list needs to change. This is something the user should be able to do for himself so I prefer to keep these lists in tables and give the user an interface that lets him manage the lists. The interface does not allow deletes but it does allow users to mark an item as inactive so it cannot be added to a new record but it could exist in old records. I've posted this many times. If you are interested PM me and I'll post it for you here.

And finally, naming all the primary key fields ID does nothing but obfuscate the relationships.
 

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
Hi. You're welcome. Good luck with your project.
Thank you! I've improved a bit further the optics by using an expression, as below:
SQL:
SELECT Tbl_Dataunits_Social_Uses.ID, Voc_Social_Uses.Social_Use_Name AS [Main Type], Voc_Social_Uses_1.Social_Use_Name AS [Subtype I], Voc_Social_Uses_2.Social_Use_Name AS [Subtype II], Tbl_Dataunits_Social_Uses.DataunitID, IIf(IsNull([Subtype I]),[Main Type],IIf(IsNull([Subtype II]),[Main Type] & " - " & [Subtype I],[Main Type] & " - " & [Subtype I] & " - " & [Subtype II])) AS [Social Use]
FROM Voc_Social_Uses AS Voc_Social_Uses_2 RIGHT JOIN (Voc_Social_Uses AS Voc_Social_Uses_1 RIGHT JOIN (Voc_Social_Uses RIGHT JOIN Tbl_Dataunits_Social_Uses ON Voc_Social_Uses.ID = Tbl_Dataunits_Social_Uses.SocialUseID_Main) ON Voc_Social_Uses_1.ID = Tbl_Dataunits_Social_Uses.SocialUseID_SubI) ON Voc_Social_Uses_2.ID = Tbl_Dataunits_Social_Uses.SocialUseID_SubII
WHERE (((Tbl_Dataunits_Social_Uses.DataunitID)=[Forms]![Frm_Interviews].[Form]![Frm_Dataunits_Interviews].[Form]![Frm_Dataunits_Social_Uses].[Form]![DataunitID]));
 

Harry Paraskeva

Registered User.
Local time
Today, 13:41
Joined
Sep 8, 2013
Messages
51
Looks to me like the issues with the schema still exist. There is a repeating group in tbl_Dataunits_social_uses where you have THREE instances of the same piece of data. I'm not sure why three is the magical number but could there ever be 2 or 6? Whenever you have more than one of something, you have many and "many" requires a separate table so you need what we call a junction table between tbl_dataunits_social_uses and voc_social_uses. That will allow you to support 0 or thousands not just three.

There also is a circular reference problem probably caused by including grandparent's foreign keys as well as a parent's in the same table.

This is something the user should be able to do for himself so I prefer to keep these lists in tables and give the user an interface that lets him manage the lists. The interface does not allow deletes but it does allow users to mark an item as inactive so it cannot be added to a new record but it could exist in old records.

And finally, naming all the primary key fields ID does nothing but obfuscate the relationships.
Thank you for the constructive criticism. A bit of details to address some of the concerns...

Three items was requested by the client and is not the same data, they just draw the data from the same table, but each one due to criteria in the underlying queries have unique items inside them. They actually represent categorical depth of the vocabulary (Type, Subtype I, Subtype II, each one is tied to the previous level only). I chose to flatten the parent-child-grandchild relationships in the vocabulary to avoid overloading system resources (I designed one in the past with four levels depth and it was managerial nightmare), but I understand what you say about data normalisation (I try usually to go for 3NF, but sometimes you have to bend some rules). Again, the structure is deliberate, is by design, and it works.

Also, the tbl_Dataunits_social_uses is a many-to-many table nested inside a many-to-many table. The structure provided regards only the problem at hand.

The circular reference is necessary in this case. It has to do with the way data had to be structured in order to maintain logical relationships with other parts of the database.

The vocabularies are fully editable and have their own interface with full controls (custom buttons for navigation and CRUD actions). I did not include the whole database, only what was necessary to resolve the specific issue.

Regarding the IDs naming, it is again deliberate to reduce clutter for the specific issue. The naming convention I use is [Table Name]_ID for the Primary Keys and [Table Name]_FK for the Foreign Keys.

I hope the above offer some clarity. It's not a perfect world, especially when you have to do with social sciences data.
 

Users who are viewing this thread

Top Bottom