Using a lookup field in a form?

[tomadom]

I have a table 'Table1' where 'Field 1' represents ID_Numbers. I wanted to see member_names in that column for reference so I did a lookup on that table using 'Table2'.

'Field1' in 'Table1' now displays member_names instead of ID_Numbers.

I now want the looked up values displayed in 'Field1' in 'Table1' to populate a combobox in a userform. So when I select the member_name in that combobox on my userform it selects the right record.

The crux of this question is... that I want to see the "looked up" values in the combobox. Up until now, all I see in the combobox on my userform is the ID_Numbers.

I have tried to change the Control Source and Row Source in the useform.combobox properties but it keeps giving me the old value 'ID_Numbers' before the value was looked up.

How do I make the "looked up" appear in the combobox?

 

[jdraw]

As I read your post, it seems this is related
http://www.access-programmers.co.uk/forums/showthread.php?t=285117

Perhaps you should post a copy of the database.

There are examples in the links I provided, and even more here

 

[MarkK]

You need to set the ColumnCount and ColumnWidths properties of the combo. Find them on the Format tab of the combo in design view. Hide the bound column by setting its width to zero, and show the other columns.

 

[tomadom]

As I read your post, it seems this is related

The question you have referenced is about a lookup table. As I've tried to make clear, the lookup table part has been acheived. Now I'm trying to relate it to a form.

 

[tomadom]

You need to set the ColumnCount and ColumnWidths properties of the combo. Find them on the Format tab of the combo in design view. Hide the bound column by setting its width to zero, and show the other columns.

It sounds like you are referring to the lookup table. I'm talking about trying to get this "looked up" value into a combobox on a userform. My combobox on my userform is still showing the value which was originally there before I did the lookup.

Hence this part of my question..

I now want the looked up values displayed in 'Field1' in 'Table1' to populate a combobox in a userform

 

[MarkK]

It sounds like you are referring to the lookup table.

No, I am talking about a control called an Access.ComboBox, and how to use it on an Access.Form object. This is not the same as a UserForm. A UserForm is provided by VBA and is not available in Access.

If you are using an Access.ComboBox on an Access.Form, then the properties you need to be concerned with are RowSource (and maybe RowSourceType), ColumnCount and ColumnWidths. If the control is bound, then you also need to be concerned with the BoundColumn and ControlSource properties.

Hope this helps,

 

[Galaxiom]

It sounds like you are referring to the lookup table.

Hardly, since tables have neither a ColumnCount nor a ColumnWidths Property. Why not try actually following the instructions Mark gave you?

If you can't work this out then use the form wizard based on the table with the lookup field (not the lookup table) and it will create the combo with the right settings for you.

You can use the clipboard to copy and paste the combo to your original form.

 

[tomadom]

Ok, I've used the wizard and I can now see the "looked up" values in my combobox.
This leads me to a second question.

Because I now have the looked up values in the combobox, the 'Row Source' property refers to Table2 for it's values.

How do I make this combobox which is using the "looked up" values, look up values in my Table1. I want to select new records using the combobox which is selecting new values based on "looked up" values??

 

[tomadom]

There are examples in the links I provided, and even more

Thanks for the link. Will be very useful going forward.

 

[Galaxiom]

Because I now have the looked up values in the combobox, the 'Row Source' property refers to Table2 for it's values.

How do I make this combobox which is using the "looked up" values, look up values in my Table1.

The looked up values are not in Table1. They are being displayed in Table1 by looking them up in Table2. The actual values in the field are still the numeric IDs.

I want to select new records using the combobox which is selecting new values based on "looked up" values??

The combo allows you select using the looked up values but it will record the ID field from the RowSource. The RowSource field used for the stored value is designated by the BoundColumn property of the combo.

Many developers strongly advise not using lookups on the fields in the tables because they lead to confusion about what is actually stored there.

 

[tomadom]

The combo allows you select using the looked up values but it will record the ID field from the RowSource. The RowSource field used for the stored value is designated by the BoundColumn property of the combo.

Yes, agreed. But is there a way to change the value in a combobox and have it related to the ID number it is representing so that a change in the combobox results in a change in the record? Is there a way of changing the properties of the combobox to do this or do I have to write a macro to do this?

 

[MarkK]

a change in the combobox results in a change in the record

Then this is a called a 'bound' control, which is to say that when it changes, the underlying data will be changed. In this case, check out the ControlSource property, which determines what field in the RecordSource of the form the control is 'bound' to.

 

[tomadom]

Then this is a called a 'bound' control, which is to say that when it changes, the underlying data will be changed. In this case, check out the ControlSource property, which determines what field in the RecordSource of the form the control is 'bound' to.

Honestly man.. you really haven't read the question have you

Quiet clearly it's a bound control. That's the easy part! But now that this control displays a "looked up" value, and when that "looked up" value is changed in the combobox, how can I get that change in the "looked up" value translate to a change in the record for my original table?

original table = The table containing the looked up value

 

[MarkK]

I like this place. I come here to help people because I find the material interesting, and I get a kick out of sharing that interest.

 

[arnelgp]

your combo should be:

rowsource: select field1, theNameField FROM table1
bound column:1
column count: 2
column widths: 0";1"

 

[Uncle Gizmo]

I think it worth mentioning that it's not particularly easy to read the questions posted by opies in the forum. I'm not saying there's a problem with your question at all I don't know I haven't read your question! I'm just pointing out from my experience in helping people in this forum is that it takes a long time to develop the skill of Reading a question and cutting through to what the actual problem is. This is caused by several factors the first and main one being that the OP does not understand the vocabulary and your first job in reading the question is to turn the question as written into something that means something in MS Access. The other problem is that what the Opie considers a perfectly logical and straightforward way of going about something is not necessarily the best way of going about it in MS Access, this causes two problems because the first issue is trying to understand what the OP wants? Then trying to get them to understand that they are going about what they consider a perfectly logical process the wrong way! I saw arnelgp post and from that as my guide I decided that this web page

https://sites.google.com/site/msaccess457966vmfjg/form-controls/combo-boxes/combo-box-videos

on my website might be might prove some help to you. But don't forget I haven't read your question!

 

[tomadom]

I like this place. I come here to help people because I find the material interesting, and I get a kick out of sharing that interest.

Ok, I appologize. Was just losing my marbles over this small problem.
I really do appreciate the help

 

[tomadom]

I think it worth mentioning that it's not particularly easy to read the questions posted by opies in the forum. I'm not saying there's a problem with your question at all I don't know I haven't read your question! I'm just pointing out from my experience in helping people in this forum is that it takes a long time to develop the skill of Reading a question and cutting through to what the actual problem is. This is caused by several factors the first and main one being that the OP does not understand the vocabulary and your first job in reading the question is to turn the question as written into something that means something in MS Access. The other problem is that what the Opie considers a perfectly logical and straightforward way of going about something is not necessarily the best way of going about it in MS Access, this causes two problems because the first issue is trying to understand what the OP wants? Then trying to get them to understand that they are going about what they consider a perfectly logical process the wrong way! I saw arnelgp post and from that as my guide I decided that this web page

https://sites.google.com/site/msaccess457966vmfjg/form-controls/combo-boxes/combo-box-videos

on my website might be might prove some help to you. But don't forget I haven't read your question!

Thanks very much. These are great.