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

 

[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

 

[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!