Combo Box Displaying Wrong Column

[Danjo]

Hi All,

I'm Danjo, brand new here! User of Excel for several years and just started with access a couple of weeks ago - looks fun! I'm little short of time at the moment but i'll set up profile later.

Quick question - I have a combo box set for "Source" which looks up the Source Table. The Source Table has 3 Columns - Primary Key, Primary Source and Secondary Source.

I'd like to see Primary Source and Secondary Source in the dropdown list but see the secondary source one the selection has been made

This is the Lookup Table

This is the Combo Box in the Table/Form - Which looks perfect

The problem is that whichever column i bound, It always shows the Primary Source (Column 2).

Bind Column 1 - Shows Primary Source (I'd expect it to show the ID number?)

Bind Column 2 - Again, shows Primary Source

Bind Column 3 -

*Changed Bound Column to Column 4, Saved and it shows the correct entry BUT when i then change the entry, it goes blank (Presumably because there is no column 4)

Just to confirm - Changing the bound column number, then saving, then going into Datasheet View cause the name to appear - until I set an option.

Hopefully, i've explained myself clearly, really appreciate any help. If i can improve my post, please do let me know. I've attached a copy of the database with no confidential info. this almost seams like a bug - i just can't make sense of it.

Thanks Again,
Best Wishes
Dan

[/B]

 

[Danjo]

Woops - forgot to attach the file - attached now, thanks

 

[Pat Hartman]

There are a number of problems. I only fixed the specific one you asked about. But, the others also need to be fixed.
1. You are using table level lookups. These cause nothing but confusion so they should ALL be removed. I removed ONLY the Source one.
2. You are using "ID" as the name of the PKs. This is not slick or useful. Give the PK columns useful names. I changed the name in the Sources table to SourceID.
3. It wasn't clear what you wanted to see so I concatenated the two columns. The way that combos work is the value in the columns property defines what will be seen. So, you can make it 0;0;3 and that will show only the third column but you won't see the second column when you drop down the list. If the columns are 0;1;3, then you will see both the second and third columns when the combo drops down but when it is closed, it only shows the first visible column which is the second one. That is why I concatenated the two columns so that they always show. You can't show both when the combo is dropped down but only the third when it is closed.
3. All caps is really hard to read
4. Object names should NOT include embedded spaces or special characters. They are not valid in variable names so they cause nothing but problems in VBA and in queries. Also avoid like the plague using any reserved words, especially "Name" and "Date". Both will cause serious problems in code. Access warns you if you try to add columns with these names to tables but it doesn't stop you.

So - remove all the table level lookups and change the Foreign Key fields to default to null rather than 0 and to be long integers rather than text.

 

[oleronesoftwares]

Locate the bound column property, and bind it to the column you want to show

 

[theDBguy]

Hi. Welcome to AWF!

Please take a look at this website to help understand why you should avoid using Lookup fields at the table level.

The Access Web - The Evils of Lookup Fields in Tables

 

[oleronesoftwares]

Alternatively do the following.

1. In VBA code, under after update event of the combo box write the following code
Combo109.Value = Combo109.Column(1)

am assuming e.g combo box name is Combo109

 

[GPGeorge]

Locate the bound column property, and bind it to the column you want to show

Actually, the point of having a Primary Key field is to USE it in fields in related tables , not the value associated with that PK. So binding to the wrong field, i.e. the value field, is going to lead to other problems that needn't be incurred. Stick to the solution of concatenating the desired two columns for display.

 

[oleronesoftwares]

Actually, the point of having a Primary Key field is to USE it in fields in related tables , not the value associated with that PK. So binding to the wrong field, i.e. the value field, is going to lead to other problems that needn't be incurred. Stick to the solution of concatenating the desired two columns for display.

yes, but that does not mean the pkey should be visible in the form(housing the foreign table)

 

[Danjo]

Wow, awesome feedback, thank you! I'll take a look tomorrow and do a proper response - i'll mark as solved for now.

Thanks again - first proper DB so looking forward to trying out the suggestions.

Cheers

 

[Pat Hartman]

1. In VBA code, under after update event of the combo box write the following code
Combo109.Value = Combo109.Column(1)

This is the code that George is talking about. The bound column is what is stored in the .Value property. You are trying to replace it with the second column which is text and NOT the PK so it should NOT be placed in the bound column.

I guess you didn't read #3 because it explains how to control the SINGLE column of the RowSource that will be visible when the combo closes. If I can find a tutorial on how combo boxes work, I'll post it for you.

 

[Danjo]

There are a number of problems. I only fixed the specific one you asked about. But, the others also need to be fixed.
1. You are using table level lookups. These cause nothing but confusion so they should ALL be removed. I removed ONLY the Source one.
2. You are using "ID" as the name of the PKs. This is not slick or useful. Give the PK columns useful names. I changed the name in the Sources table to SourceID.
3. It wasn't clear what you wanted to see so I concatenated the two columns. The way that combos work is the value in the columns property defines what will be seen. So, you can make it 0;0;3 and that will show only the third column but you won't see the second column when you drop down the list. If the columns are 0;1;3, then you will see both the second and third columns when the combo drops down but when it is closed, it only shows the first visible column which is the second one. That is why I concatenated the two columns so that they always show. You can't show both when the combo is dropped down but only the third when it is closed.
3. All caps is really hard to read
4. Object names should NOT include embedded spaces or special characters. They are not valid in variable names so they cause nothing but problems in VBA and in queries. Also avoid like the plague using any reserved words, especially "Name" and "Date". Both will cause serious problems in code. Access warns you if you try to add columns with these names to tables but it doesn't stop you.

So - remove all the table level lookups and change the Foreign Key fields to default to null rather than 0 and to be long integers rather than text.

Hi Pat,

Thank you for your post. Really appreciate you helping with the first one but not doing all of it so i have to chance to see what you've done and incorporate in the others!

1.
a. Makes sense and agree completely. I need to be able to add or remove entries easily so putting the value list in Row Source won't help BUT i'm thinking that using a form and connecting that to "List Items Edit Form" might be a good compromise?
b. I can see you have used SQL expression to refer to the source table rather than a lookup.
I'm brand new to SQL but my interpretation is

So its ok to use a table to store the variables but instead of using lookup, use a SQL expression like the one above and always reference the primary key.

2. Makes sense, understood

3. Brilliant Advice - Combine the fields using the SQL expression (The Black section above). I get that you can set a field width to 0 to hide it.

3(!). My Bad Habit - I like to use all caps in headings but sometimes muddy up the in between!

4. All good habits I'll make a note to try and remember.

Thanks again Pat, this has been really useful and i'm going to try and apply to the rest of the table and use going forward. If you could correct anywhere i am wrong above and help with the "AS PandS FROM Sources" bit, i'd be grateful

Edit - found a sentence which was only half completed :eyeroll:

 

[Danjo]

Locate the bound column property, and bind it to the column you want to show

Did this but wasn't working for me - I'm going to follow the advice of others here and avoid look ups entirely and try to learn using SQL and primary/foreign keys. It's a lot harder to learn in the beginning but i think will pay off later on. cheers

Hi. Welcome to AWF!

Please take a look at this website to help understand why you should avoid using Lookup fields at the table level.

Yep. Some of the article didn't make a huge amount of sense to Mr Beginner here but i get the points made. Many thanks

This is the code that George is talking about. The bound column is what is stored in the .Value property. You are trying to replace it with the second column which is text and NOT the PK so it should NOT be placed in the bound column.

I guess you didn't read #3 because it explains how to control the SINGLE column of the RowSource that will be visible when the combo closes. If I can find a tutorial on how combo boxes work, I'll post it for you.

Makes perfect sense to me - include all the columns in the combo box but 0 the columns not to be visble.

 

[mike60smart]

Another solution would be Cascading Combobox's.
1st Combobox allows you to select either CIMS or HLP
2nd Combobox would only display those personnel associated with area selected.

 

[Danjo]

Another solution would be Cascading Combobox's.
1st Combobox allows you to select either CIMS or HLP
2nd Combobox would only display those personnel associated with area selected.

Fair point!

 

[Pat Hartman]

@oleronesoftwares is a little confused regarding how combo boxes work. Perhaps the confusion is caused by your use of lookup fields at the table level. These are discouraged for many reasons but the primary one is that they obfuscate the actual data contained in the column. Typically your FK when you make it a lookup will show CompanyName (for example) and that confuses people into thinking that that is what is stored and so the FK should be defined as text. However, that isn't how it works. The PK of the lookup table is what is stored so you are storing CompanyID and that is almost certainly a long integer. Since users NEVER get to see tables or queries, they always interact with forms and reports, using a lookup field at the table level is a crutch for you. You don't need it if you know how to do a query with a join. So, leave the lookups to forms where they belong.

The ControlSource of the combo is the field the control is bound to in the Form's RecordSource so if you use a combo on an updateable form, this is the field that will be saved to the table. It is rarely a text field unless your table uses a text field as its PK. Trying to put a text field into this property is what oleronesoftwars was recommending. If your FK had been correctly defined as a long integer as it should be, you would get a type mismatch error if you tried to do this.

The RowSource of the combo is usually bound to a table or query. Sometimes you can use a value list but don't ever do this if the list can be expanded or the values changed. Apparently even Gender is no longer a candidate for a value list I have a mini-app that I include in every application i build. It is used to manage lookup lists. It gives me a standard form and table to use to manage ALL simple lookup lists in a single place. That means I don't have to keep building new tables and creating new maintenance forms each time I find a new list that the application needs. They all get managed in one table on one form. PERIOD. Search for it or ask and I will post it again.

The Bound Column property of the combo refers to the column of the RowSource that will be saved in the ControlSource. Make sure they match in data type. Usually, you will choose the bound column as the first column of the RowSource but as you can see, that isn't required.

The Column Count property of the combo specifies how many columns the RowSource contains

The Column Widths property specifies which columns will be visible and how wide they will be. The numbers are entered in the order of the columns of the RowSource so most often, the first Column Width is 0. Any column with a width of 0 is hidden. All non-zero width columns are displayed when you drop down the list. HOWEVER, only the first non-zero width column is shown once the list is closed. Usually, this is sufficient. In your case, you seemed to want to show two columns so I created a calculated column and set that as the first display column. I also sorted on that to make the combo box work correctly. Type ahead won't work correctly unless the list is sorted by the first visible column.

Everything I just said about combos applies to listboxes. List boxes have additional useful attributes not discussed here.