Get ComboBox ID value from table where set to display NAME

[cloverdog]

Please could someone tell me if it is possible to access the value of the bound column of a combobox in a table where its columnwidth is set to 0. I need to be able to display NAME in table view for the user but I need to be able to get the ID value from a regular query.

I have 2 tables The first is tblClient the second is tblHK. I have added a field to tblClient called ‘AssignedHK’ and used the lookup tab in table design view to set ‘Display Control’ to Combo Box. The row source is “SELECT tblHK.ID, tblHK.Name FROM tblHK”;

The comboBox field works fine I can click on the AssignedHK and see HKname.

My problem is then designing a query that lists the ID values. I can’t see how to address the bound column value (ID).

Any help appreciated even if only to say can’t be done.

 

[Lilliabeth]

If I were you, I would stop trying to use the lookup list feature in a table, as it is the creation of the Evil One. It seems you are experiencing one reason it's called that.

http://www.mvps.org/access/tencommandments.htm

In fact, if I were you, I wouldn't invite my users to mess around in tables at all, ever. It takes moments to create a form, and you can do all kinds of cool automated things and enforce business rules there. I don't understand why anyone does data entry in a table.

--Lilliabeth

 

[cloverdog]

Thank you Lillabeth.

I have used this feature before but only for limited working use. I wanted a quick easy method of adding a simple feature to meet an immediate need where time was short. Having found I couldn't make it do what I wanted I just had to know if it was possible or not.

Following your reply I gave up Sunday to modify tables, forms and queries to meet the need and include som of that other functionality that would be nice if there was time.

Thank you again for your reply I will not stray from the path of righteousness and fall into tempation again.

Hopefully.

CloverDog

 

[traingamer]

That's terrible advice, Lilliabeth.

To reference the the hidden value/column in your combobox, use this structure (usually in the AfterUpdate Event):

YourComboBoxName.Column(0)

You can reference any number of hidden (or non-hidden) columns in your combobox. The combobox columns are zero-based; that's why for your example you'd reference column zero.


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

(The reason I say that that advice was terrible is because that is not a LOOKUP.)

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[dhookom]

Traingamer,
I read "bound column of a combobox in a table" as a lookup field which I agree is a bad feature.

There should be no issue referencing the BOUND column of the combo box even if the bound column value is not displayed.

Duane
Hook'D on Access
MS Access MVP

 

[Lilliabeth]

Thank you Duane.

traingamer,
Please explain why you think it was terrible advice, and why you contend this is not a lookup. But this is a friendly forum, so I hope you can find a way to disagree without being so harsh.

He is describing a lookup in a table. He might be calling it a combo box, but he's certainly got a lookup list in a table.

Did you perhaps fail to carefully read the question before you used the phrase "terrible advice"?

--Lilliabeth

 

[cloverdog]

Dear all

Actually Lilliabeth was right I was using a 'lookup field' although I didn't know it was called that before this post.

I used the term combo box because that was the control type I selected on the ‘Lookup’ tab at the bottom in table design view to set it up.

Bearing in mind this field would not, in most cases have a value in it and that customers would be at the many side of the relationship this method seemed to be quick and easy.

Has anyone prescribed an alternative method.

Thank you all for your replies.

CloverDog

 

[Lilliabeth]

Thank you CloverDog.
Mr "Terrible Advice" traingamer sure is mighty quiet.

--Lilliabeth

 

[dhookom]

CloverDog,
IMO, all user interaction with your application should be through forms. You can use combo boxes on forms for value selections.

Duane
Hook'D on Access
MS Access MVP

 

[traingamer]

I stand corrected. I missed the lookup tab part of the original post.

I thought forms when I saw combobox and should have have noticed table lookup.

Lilliabeth, I don't visit these forums as often as I once did, so you can interpret my mighty quiet however you like.


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Lilliabeth]

Of course I'll accept such a warm and gracious apology as that one. Like they say, "I stand corrected" are the most powerful words in the English language.

--Lilliabeth