Issue with Cascading ComboBox

I congratulate you on your decision to remove the lookups from your tables.  These were designed to help newbies but really do nothing but create more confusion in the long run.

What you need to do is change the BoundColumn back to 1 (the numeric ID value), and then change the column count and column width properties of the combo to indicate 2 columns and that the column widths are like 0"' 1.5"

HTH
Dale

I have tried that again and my result is only showing the ID#'s in the Table and Query :( do you have any suggestions to assist me in viewing the text instead of ID's?

If you run the RowSource query directly, do you see the ID as the first column and the text you want to display as the second column?

Your column widths should be something like ---    0", 2"
Bound column should be 1
Column count should be 2

Hi Pat! I have tried that but it still only shows the ID numbers. The text showed fine before I deleted the lookups from the table. I created new fields set as text for System, Component, Problem and Repair in the Table and used the original cascading combo boxes in the form. Ever since it has shown the ID numbers instead.

Michael,  I'm not sure what "I tried that" means.  is "it" the combo or the query?  I gave you a specific request.  Did you run the RowSource query outside of the combo?  Is the second column what you want to see?  Obviously if the RowSource does not return the correct columns, the combo will not work correctly.

You are making us guess that you implemented the instructions correctly.

Yes the second column is what i want to see. In the properties of the form I have set the column count to 2 and the width to 0,2. The bound column is also set to 1. With these settings the form shows the text as needed but in the table and query it is only displaying the ID number not the text. The table and query was showing text before I removed the lookup from the table. My apologies if I’m not understanding about running the RowSource query outside of the combo or explaining myself correctly.

Lookups are doing "silently" what you should have...but because they don't provide straightforward info on what you actually seeing its at the end a bit confusing..
So replace your table with a query and do the "lookup" by yourself...so you have the table (i guess its a maintenance table) and on the Query Designer drag along the Bus table  so you will have 2 tables on the upper part of the QD.
Link the BusID from the Maintenance table to the BusID from the Bus table,
Select the fields you need (don't forget to include the IDs as these are the "keys") and the descriptions/name
So if you have a Bus with ID 3 which corresponds to BusXYZ you will select
BUSID        BusName
Then use this query as the datasource for the "subform".
If its too complicated upload a sample copy

It sounds like there is confusion about what is wanting to be displayed and what is being recorded as the value in the table field.
Remember, the lookups (comboboxes) in the TABLE field have been removed.  What these lookups did was allow the ID# to be the value in the field, but the corresponding "lookup" text value located in the table that had the text that corresponded to that ID# to be displayed (seen) in the table field.

The "lookups" were comboboxes IN the table field.  The comboboxes on the form perform the same function here.  The comboboxes are "bound" to the underlying ID# field, which is column #1 and has the 0 width, and the text is column 2, which has the width that allows the text to be seen.  

It seems the confusion is occurring when we switch the "bound" column  to the text column.  The text value is NOT the value in the table, so the combo doesn't find it in the controlsource field (which should be the bound column) and therefore displays nothing.  You either have to have the ID# in the table field or the text value, but you can't have both.  

You can't get the text to display in the TABLE field that has the ID# WITHOUT the lookup control on the TABLE field.  In a query, you link the table ID# field to the ID# field in the table with the text and put the text field in the query.  On a form, you have the combobox bound to the ID# field and the rowsource has 2 columns with ID#, Text, and the 1st column set at 0 width so the text column shows in the combobox.

Otherwise, it sounds like the author is wanting to display the text in the TABLE field WITHOUT a lookup control on the table.  Is that right?

Micheal,

Lets backup, you said: "The text showed fine before I deleted the lookups from the table. I created new fields set as text for System, Component, Problem and Repair in the Table and used the original cascading combo boxes in the form"

 Does that mean you no longer have the original numeric field which holds the ID value in your table?  When you removed the lookup, you should keep the ID fields as those are the values you want to store.  You don't want to store the text values as those take up extra space in your database.

The controls on your form should be bound to the ID values in the table, and to display the text you should use a query that looks something like:

SELECT ID, Description from yourTable ORDER BY Description.

Then you set the bound column to 1 (the ID column) and the column widths to 0", 2" (or something like that ).

If you simply run the query above (changing the names as appropriate), does it return the ID values and the text?  If so, reconfiguring the combos to bind them to the various ID values and changing the column widths should resolve your issues.

Dale

Here's a tip:  There is a difference between the underlying "value" of a field or control, and the visible "displayed" value (control.value and control.text properties).  We are use to thinking of them as the same, and a lot of times they are, but doing so can lead to confusion on issues like this one.

In Excel, have you ever placed your cursor in a cell that displayed "$1,234.56" and seen 1234.56 in the formula bar? - same thing!

Looks like what you are doing is fine, but there may be a little confusion.

The combo represents a field in the current table, that is a foreign key to another table.
Example is a parentID in Children table, that references the parentID in Parents table.

The combo shows parentID as column 1, and ParentName as column 2.
Column 1 is hidden, since its width is 0. The second column shows the parent names.

When you select a parent name, access saves the parentID in table Children

So if parentID is a numeric, then the ParentID field in Children table is numeric.

Otherwise, it sounds like the author is wanting to display the text in the TABLE field WITHOUT a lookup control on the table.  Is that right?

Yes Mark you are exactly correct in what I am trying to do. Maybe i'm just putting a band aid on my real goal. Let me try to explain my ultimate goal, perahaps there is another solution.

I am trying to link tables from 2 separate databases into a 1 new database. When I linked the 2 tables into the new database (that were using look-ups in their tables) the text values of the data showed up fine in each imported linked table, but once I performed UNION ALL in the new database the text values from the linked tables changed to the ID numbers instead of the text values in the UNION ALL Query. I am ultimately trying to perform a UNION ALL that displays the Text Values of the fields not the ID Numbers.

When I searched for a solution I read that I needed to remove the lookups from the original tables and use them only in the form. But by doing this as I have only moved my ID display issue from my new linked database to the original databases.

I simply want to query or merge 2 separate (identically structured) tables from 2 separate databases into 1 and be able to view the inputted data as entered (viewed) in the form.

What am I missing? Their has to be a way to accomplish this. It seems as if I am always stretching the limits of MS Access it just seems like is would be a fairly common task, but I find so little information out there on a solution.

something from the story...lets make it simple.
At first link the tables from DbA and DbB...so your DbC has 2 linked tables..i assume that they have equal number of Required fields...e.g field1,field2,field3 on TableA...field1,field2,field3 on TableB
now go to the query designer and pick ONE of the tables..select the fields you want....Is it Ok?...work until is Ok..when you are Ok switch to SQL view.select with the mouse and
copy the Sql..care fully not to delete it ...click with the mouse to deselect it...append the 'Union All",  paste the copied text so it would be
Select field1,field2 fromTableA Union All select field1,field2 field3 from TableA...rename the 2nd tableA to TableB save it and you should be good to go

something from the story...lets make it simple.
At first link the tables from DbA and DbB...so your DbC has 2 linked tables..i assume that they have equal number of Required fields...e.g field1,field2,field3 on TableA...field1,field2,field3 on TableB now go to the query designer and pick ONE of the tables..select the fields you want....Is it Ok?...work until is Ok..when you are Ok switch to SQL view.select with the mouse and copy the Sql..care fully not to delete it ...click with the mouse to deselect it...append the 'Union All",  paste the copied text so it would be Select field1,field2 fromTableA Union All select field1,field2 field3 from TableA...rename the 2nd tableA to TableB save it and you should be good to go

John thank you. I will try this in just a moment.

John,
While I was following your instructions I realized that these steps are exactly what I have originally attempted. I am attaching photos of my step results:

Linked Table DbA: Good so Far

Linked Table DbB: Good so Far

SQL Code for UNION ALL Query:

SELECT MaintTIBMF.[Bus#], MaintTIBMF.Date, MaintTIBMF.System, MaintTIBMF.Component, MaintTIBMF.Problem, MaintTIBMF.Repair, MaintTIBMF.Notes, MaintTIBMF.Tech1, MaintTIBMF.Tech2, MaintTIBMF.Tech3, MaintTIBMF.Tech4, MaintTIBMF.RepairType, MaintTIBMF.NewRepairTime, MaintTIBMF.HardCard, MaintTIBMF.MemoryClear, MaintTIBMF.FBOut, MaintTIBMF.FBIn, MaintTIBMF.TROut, MaintTIBMF.TRIn, MaintTIBMF.BVOut, MaintTIBMF.BVIn, MaintTIBMF.BTOut, MaintTIBMF.BTIn, MaintTIBMF.CVOut, MaintTIBMF.CVIn, MaintTIBMF.LBOut, MaintTIBMF.LBIn, MaintTIBMF.MiscOut, MaintTIBMF.MiscIn, MaintTIBMF.ValidatorOut, MaintTIBMF.ValidatorIn
FROM MaintTIBMF;

UNION ALL SELECT MaintTSMF.[Bus#], MaintTSMF.Date, MaintTSMF.System, MaintTSMF.Component, MaintTSMF.Problem, MaintTSMF.Repair, MaintTSMF.Notes, MaintTSMF.Tech1, MaintTSMF.Tech2, MaintTSMF.Tech3, MaintTSMF.Tech4, MaintTSMF.RepairType, MaintTSMF.NewRepairTime, MaintTSMF.HardCard, MaintTSMF.MemoryClear, MaintTSMF.FBOut, MaintTSMF.FBIn, MaintTSMF.TROut, MaintTSMF.TRIn, MaintTSMF.BVOut, MaintTSMF.BVIn, MaintTSMF.BTOut, MaintTSMF.BTIn, MaintTSMF.CVOut, MaintTSMF.CVIn, MaintTSMF.LBOut, MaintTSMF.LBIn, MaintTSMF.MiscOut, MaintTSMF.MiscIn, MaintTSMF.ValidatorOut, MaintTSMF.ValidatorIn
FROM MaintTSMF;

Select allOpen in new window

UNION ALL Results: Not Good

As you can see the UNION ALL changes the data of System, Component, Problem,  Repair and Tech1 to the ID Numbers in DbC.
NOTE: The linked Tables DO still have the look-ups in the original tables from DbA and DbB. (The look-ups have not been deleted from the tables in this attempt because when I do delete them in the original tables it begins to show the ID numbers in the Original DbA and DbB like it is doing in DbC.

delete ALL the lookups...this is the root of your issues...if you don't get the info from the 2 tables you need to link/import the rest of the tables and after you have created the UNION again head to Query Designer and make the joins between the main query (the union) and the rest of the tables..
I assume/guess that Tech1 represents your Technicians...so you should have a table named something like Technicians..so drag the Union query to the query Design editor and also drag the Technicians tables and perform the join between the Tech1 field from the UNION query to the key field on the technician table (e.g. TechnicianID) and select both the Tech1 field from the UNION and the name of the Technician from the related table

Then this brings me back to the beginning of this thread.. :( When I deleted my look-ups in (a copy of) DbA, My table in DbA started to display the ID numbers inside the Table (Just as DbC is doing after I did UNUION ALL) I can't get my table to display the Text instead of ID without having the look-ups inside the table.

Just make a test...delete the lookups from a table and try to produce the info using a query with the joined tables...hint...the lookups will guide you to which fileds to join against...

Ok This is where I'm getting lost with the Join and in which table. I'm going to post pics of my steps in removing the table and its results. perhaps then you'll see where I'm going off track. Give me one sec.

I have to admit that I didn't read all the comments but since you still don't have an answer, no one figured out the problem.  This is really not that difficult a problem.  Now that you answered my question which is WHAT DOES THE QUERY SHOW, I can tell you that the combo RowSource is selecting from the WRONG table.  Since I don't know anything about your schema, I'll give you a generic example.

tblState
StateID (autonumber PK)
StateName
StateAbbr

tblCity
CityID (autonumber PK)
CityName
StateID (FK to tblState)

tblCustomer
CustID (autonumber PK)
CustName
StateID
CityID

For the state combo, the RowSource selects from tblState
For the city combo, the RowSource selects from tblCity NOT from tblCustomer

Combos select from the table where the item is DEFINED, not from some table that uses the ID.  You were always using the wrong table.  You just didn't know it yet.

The problem with the Union query is just ONE of MANY examples of where you will have trouble with table level lookups.  Lookups should only be used on forms as combos or listboxes.  Once you start creating VBA and queries, you will see all the problems caused by table level lookups.  These were introduced to make it "easy" for  a novice to accomplish a lookup.  Novices don't write code and they don't understand joins or more advanced query types so it takes a while before they run into problems.

The solution in ALL cases is for queries that are not bound to forms (on the forms you would have combos or list boxes so there is no problem), you will need to include the definition table and join to it.  That lets you pick up the ID from the original table and the text value from the definition table.  Always use a Left join so that the query works even when the ID you are looking up is null.

Here are my Row Sources:

System:  SELECT SystemT.ID, SystemT.Systems FROM SystemT ORDER BY Systems;

Component:  SELECT [CompT].[ID], [CompT].[component] FROM CompT WHERE (((CompT.ComponentSystemID)=[Forms]![MaintF]![System])) ORDER BY [CompT].[component];

Problem: SELECT [ProblemT].[ID], ProblemT.Problem, ProblemT.ProblemSystemID FROM ProblemT WHERE (((ProblemT.ProblemSystemID)=[Forms]![MaintF]![System])) ORDER BY ProblemT.Problem;

Repair:  SELECT [ProblemT].[ID], ProblemT.Problem, ProblemT.ProblemSystemID FROM ProblemT WHERE (((ProblemT.ProblemSystemID)=[Forms]![MaintF]![System])) ORDER BY ProblemT.Problem;

You did say that Compt.Component does NOT contain the description you want to see.  If it doesn't, then were is that description kept?

SystemT
ID
Systems

CompT
ID
ComponentSystemID
Component

ProblemT
ID
ProblemSystemID
Problem

RepairT
ID
RepairSystemID
Repair

Here are the results from 4 tests (with description in the notes of each test)

I feel like I am speaking gibberish.  Your answers don't address my comment.  Let me try again using baby steps.

1. Copy the string you are using as the RowSource for the Component combo.
2. Paste that string into the QBE as a new query.
3. Open the form.
4. Pick a system
5. Run the query you have sitting in the QBE.
6  DOES THE SECOND COLUMN SHOW THE TEXT YOU ARE LOOKING FOR?????

If #6 shows the correct values, then the problem is with the settings on the combo.  The best bet at this point is to delete it and rebuild it using the wizard.  If #6 does not show the correct values, then what table contains them?

The query results you did post, although they have nothing to do with the discussion, do point out that you have a table definition problem.
System, Component, Problem, and Repair should be defined as numeric and they are obviously not.  That is also going to cause problems for you.

I'm sorry but I don't know all of the terminology. What is the definition QBE? Query by Example? (had to google it) Please keep in mind that I am self taught in Access due to job necessity and I'm doing my best to understand and follow along. I do admit that I will sometimes need baby steps so please bare with me as I ask these next few questions as we go forward:

I have copied the string for my Row Source of Component combo. Next need to create a new query design? and if so where do I paste it in this query? in the Field section? I am confused by "Paste that string into the QBE as a new query." Please be patient with me.

Can I message you my database for you to look at? I know it will have a lot rookie mistakes and I'm sure you have better things to do with your time but perhaps it will give you a better understanding of what I have done or NOT done.

I guess uploading a copy will help everyone

Thank you John, This issue is causing me quite a few road blocks for some upcoming tasks that I am working on. I messaged you a copy with login instructions.

QBE is the Access query designer.  If you choose New query from the ribbon, the QBE opens with a dialog box that asks you to choose tables or queries.  You can dismiss the dialog without choosing anything.  Then switch from design view to SQL view.  That is where you can paste the SQL string.

The reason I asked you to open the form and choose a system from the combo is because the query references the system combo.  If you don
't have the form open, the query will not work.

If you upload your database, please strip out any sensitive data - or obfuscate it.  Then compact the database before uploading it to reduce its size.

PS - I too am self taught.

Pat, ok thank you. That is what I concluded that you wanted of me. Earlier I posted the row source of the component combo box in the SQL. My result showed the ID and component fields with no data entered in any of the rows. I am not sure if I had the form open at the time but I am confident that I have not been closing it on tests. I will redo again tomorrow morning when I return to work. If you’re willing to take a look I can message you the compacted database tomorrow as well. I am currently still reading up on the cascading combo box displaying the values in the table and I am also seeing a pattern with the row source being my culprit. Just can’t get my head around it yet.

If you post the database here, I will look at it.  If you send it to only me (which violates EE policy by the way)  then you should expect to pay me because I will be working for you and not for the community.  Experts donate time to helping people on forums like this because answers here potentially help hundreds of people over many years, but they don't work for free if you need personalized attention.

Haven't had time to strip down the database yet, but i have made a little bit of progress. I removed ID from the Row Source for Component, Problem and Repair. This allowed my Cascading Combos Boxes to function and populate properly. The only remaining issue is for System.
System displays properly with text in the Form but still displays the ID# in the Table.
So my final result Looks like this:

Row Source:

SELECT SystemT.ID, SystemT.Systems FROM SystemT ORDER BY SystemT.[Systems]; 

Select allOpen in new window

SELECT [CompT].[component] FROM CompT WHERE (((CompT.ComponentSystemID)=[Forms]![MaintF]![System])) ORDER BY [CompT].[component]; 

Select allOpen in new window

SELECT ProblemT.Problem, ProblemT.ProblemSystemID FROM ProblemT WHERE (((ProblemT.ProblemSystemID)=[Forms]![MaintF]![System])) ORDER BY ProblemT.Problem; 

Select allOpen in new window

SELECT RepairT.Repair, RepairT.RepairSystemID FROM RepairT WHERE (((RepairT.RepairSystemID)=[Forms]![MaintF]![System])) ORDER BY RepairT.Repair; 

Select allOpen in new window

The solution is incorrect.  You still have the table level lookups or you have switched the foreign key fields from saving the ID to saving the text value which is VERY WRONG.  Using the table level lookups causes problems with queries and code which is why experts do not recommend them.  However, they are not wrong per se and if you understand the problems they cause, you can circumvent them.  Storing the text field rather than the PK as the FK is just plain wrong and you need to fix this.

Ok, I was able to strip down the database without any errors. Here is the compact version. Thank you for assisting me with this...
To login:
Open database password:  gfigfi

Login Screen:
User:   test
Password:  password
Maintenance-Log-Deleted-Lookups-TE.accdb

I'm not going to fix this because you will still have to understand what is wrong so you can fix it in the main database.
1. You've made the relationships harder to see than they should be by naming all primary keys "ID".  This is some affectation that is currently popular.  If you had defined all the relationships, I could use the relationships diagram to see how tables are related.  However, you haven't defined ANY relationships.  That means I have no column name clue to help me identify what your intended relationships are.  I'll make a couple of guesses.  Notice that I changed all the names to conform to a reasonable standard.  The "ID" is a suffix that identifies an autonumber PK.  The same name is used as the FK.  Some people perefer to suffix the FK so in the MaintT table, the FK's would be SystemID_FK, ComponentID_FK, RepairTypeID_FK - this is a reasonable extrapolation of naming standards and adds clarity to the design.
MaintT.SystemID --> SyystemT.SystemID
MaintT.ComponentID --> Compt.ComponentID
MaintT.RepairTypeID --> RepairTypeT.RepairTypeID

Do you see the pattern with the names?

1.  ID = ID.  Relationships are always on a numeric data field in one table to the PK of the lookup table.  At the moment, your names are a jumble and I strongly suggest changing them before moving on (also remove all the non alpha characters such as space and #).  But, if you are OK with making your life and that of anyone who takes over when you leave unnecessarily difficult,  use whatever names you want.  I can tell you from 50 years of experience, consistant naming standards will make your life much easier and prevent lots of stupid mistakes down the road.  One example of consistency is to never mix "Component" and "Comp" in the same app.  Either abbreviate or spell the word out.  
2.  All the foreign keys seem to be text rather than long integer.  This is going to prevent you from defining actual relationships.  This MUST be fixed.
3. You have repeating groups such as Tech1, tech2, tech3, tech4.  Whenever you find that you are repeating some part of a word, you probably have a repeating group and this indicates a 1-m relationship which MUST be stored in a separate table.  The FB, TR, BV, CV, LB also seem to be a repeating group.

If you take the time to fix all the names and data types, I will help you fix the app to make the combos work correctly.