Lookup data changing in table

[Hibba]

I have a form where I have a lookup field. It comes from a table with two fields, Name_ID and Name. The lookup is set to show me the name ID. How can I get the Name to be displayed on the table though? Thanks!

 

[Pat Hartman]

Change the RowSource of the combo to a query that selects the ID and name fields and orders on the name field. Change the column count to 2, the bound column to 1, and the column widths to 0,1

 

[Hibba]

Awesome, that works great. However, I did run into a problem. In my Name Table, with Name_Id and Name field, some IDs have the same Name. So when I try to choose them in the lookup field, I can only select the first ID. What's a fix?

Thanks!

 

[Pat Hartman]

The bound column needs to be unique. The bound column should be the ID, not the name.

 

[Hibba]

Ok, I'm not exactly sure how the bound column works, so I'm notn sure how to change it to fix my problem.

 

[mixedguy]

This is a little more complicated solution but it looks like you have a 1 to many search. You have a Name_ID in your combo box (assumption) and when they select their chose in the combo box you want all the records related to that chose to appear at the bottom. Right? If that is the case then you need to create a subform. Afterwards you need to link up the subform to the combo box by using the child/parent linkage.

 

[Hibba]

Not exactly. I have a Name_ID in my combo box and when they select their choice in the combo box I want the Name associated with the Name_ID to be placed in the table where my form data is going.

Basically, I do this because it is used as a realtime process. People come through a line, pick a choice, and I enter that choice. (3 letter code) Which relates to the full name of that choice. IE: UBB = Basketball (U stands for something only I need to know)

 

[Pat Hartman]

Is Name_ID unique?

 

[Hibba]

Yes, Name_ID is unique.

 

[Pat Hartman]

Then that should be the bound column and it doesn't matter if the text column contains duplicates.

 

[Hibba]

Ugh, I am not sure what I am doing wrong. I have a query that displays Name and then Name_ID. In my form, the combo box row source is set to that query. Bound Column is 2 and column width is 0,1. This gives me the Name_ID to be printed in the table.

 

[Pat Hartman]

Although you can bind other columns, occasionally there are problems. Try rearranging the order of the columns so you can bind the first one. Don't forget to fix the column widths so you hide the ID rather than the name.

BTW, Name is a poor choice for a name since it is the name of a VB/VBA property. If you need to use it in VB/VBA, you may run into trouble. Also while I'm at it, avoid function names such as Date, and special characters and embedded spaces.

 

[Hibba]

Ok, I just think it is not possible. It works correctly, but some of the name_ID fileds have the same Name field. In which case, it will not let you select the second field with the same value. This is the problem. If all Name fields are different, there would be no problem, but I need some of them to be the same.

Maybe a different approach?

 

[Pat Hartman]

I created an example for myself because I wasn't sure if there was problem. There isn't. Only the bound column needs to be unique. Try creating a query that selects the two columns and sorts on the text field. Then rebuild the combo from scratch with that query as the RowSource.

 

[Hibba]

I still get the same thing. Maybe my query is incorrect. I have the two fields Name_ID and Name in the query, sorted ascending. Anything I am missing? If not, then I get the same problem as before where it will only let me choose the first Name_ID if there are more than one that have the same Name.

 

[Pat Hartman]

"Name" is a poor choice as a column name since it is the name of a property. I suggest changing the name of the column to something else more descriptive such as TeamName or whatever makes sense. If you don't have autocorrect turned on, you'll need to change the query and also the report to use the new name.

 

[Hibba]

I actually don't use "Name" I just used that in the forum to make it clear. My field names are called Skill and Skill_ID.

 

[Pat Hartman]

Post your query and post pictures of the format and data tabs of the combo's properties so we can see the settings.