Combo Box Column Selection

[Keith Hawes]

I am having a problem entering the 2nd column of a combo box.This is a lookup combo looking at a table called Accessories. The combo is looking up 4 columns in the table, the first column is called "Action" the 2nd column is called "Item". I click on the combo box in the form and decide on the action I want and click that row, it then enters the action I have clicked. But it is the "Item" column I want to enter on the form not the "Action" column. This only works if I make "Item" the first column which I do not want to do. I want to keep the combo columns in the order they are but enter the 2nd column not the first. I have tried making the bound column the 2nd column but it still enters the 1st column. I am sure access must be capable of doing this but I cannot work out how. It seems you can only enter whatever is in the 1st column. Hope you can help.

 

[Beetle]

What do your mean when you say you want to "enter the Item on the form"? Do you mean you want the Item value from the combo box to appear in another control (like a text box) on the form, or do you mean that you want the Item value to be stored in the field that the combo box is bound to?

 

[Keith Hawes]

Hi Sean thanks for the prompt reply. The form I am using is called "Accessories
Required" which enters information in the Accessories Required table. I have a text box in the form which is a combo box looking up columns in a table called accessories. I then select from the combo box the item from the accessories table I want entered into the text box. The item I want entered is in column 2 of the row I select. Does this explain the situation.

 

[John Big Booty]

Perhaps the discussion and sample here will help you.

 

[Beetle]

Looks like JBB forgot the URL, so I'm not sure what he was linking to, but if I understand you correctly, you want the Item value from the combo ox to display in a separate text box. To do this you can use the Column index of the combo box. The index is zero based, so the first column is Column(0), the second is Column(1), and so on. So if the Item is in the second column, then the Control Source of your text box would look like;

=[YourComboBox].Column(1)

replacing YourComboBox with the actual name of your combo of course.

 

[John Big Booty]

Try this link

 

[Keith Hawes]

Hi John thanks for the reply. I have looked at your example and it is doing the same as my database. You have 0 in the width of your first two columns. If you make them wider so you can read them (and the total width wider) you can read the first two columns of your combo box when you click it. But now it will not let you enter the data from your third column which is what you want entered. I want to be able to see the first two columns in the same order when I click on my drop down list but enter the data from the third column. I cannot work out how to do it. Hope you can help.

 

[Beetle]

If you want the value form the third column of the combo box to be stored in the field that the combo box is bound to, then set the Bound Column of the combo box to 3.

 

[Keith Hawes]

Thanks for the advice Sean. However it does not seem to work. Try doing it on the link database from John. First you have open the width of his first two columns which are set to (0). If you make them wider so you can read them (and the total width wider) you can read the first two columns of the combo box when you click it. Now set the Bound column to 3. It will still not let you enter the data from the third column which is what I want to enter. I want to be able to see the first two columns in the same order when I click on my drop down list but enter the data from the third column.

 

[Beetle]

OK, let's take John's sample db. The Row Source of the combo box in the form contains the following columns (all from TBL_PCode);

PCodeID (Autonumber)
PCode (Number)
Locality (Text)
State (Text)

Columns 1, 2 and 4 are hidden, so you only see the locality in the combo box. The Bound Column is 1 (PCodeID) and the Control Source (the field where the selected value is stored in the form's Record Source) is PCodeID from TBL_Records (TBL_Records is the Record Source of the form).

Now, if I decide I want to store the Locality value from the combo box in the the form's Record Source (TBL_Records), I can change the Bound Column to 3. However, the Control Source of the combo box is PCodeID. I can't store a text value in an Autonumber field now can I? No, so to do this I also need to change the Control Source of the combo box to a field that can accept a text data type. In this case, the only field in the forms Record Source that can accept this type of data is the Note field (which is a Memo data type). If I do that, then now when I make a selection in the combo box, it stores the Locality value from TBL_PCode in the Note field from TBL_Records. Whether or not it makes any sense to do this is open for debate, but it can be done.

 

[Keith Hawes]

Hi Sean. I have attached two very basic dbase's that explain my problem. In each one click on the combo box "Item".The one called forum 1 lets me put in the correct value which is "item" in the form. But the columns in the drop down box are not in the order I want people to view. I want the action column first. In Forum 2 the columns are in the order I want but it will only let me enter the "Action" value not the "Item" value. Can we leave the columns in this order but enter the value from the 2nd column. Both fields are text. Hope this explains the problem better Sean and I would be very pleased if you can solve it.

 

[Beetle]

OK, so this is not an issue of whether or not the correct value will get stored. If you set the bound column to 2 in the combo box, the Item will get stored in the table. The problem for you is that a combo box - in it's normal (unexpanded) state - will always (and only) display the first visible column, regardless of which column is bound.

The only way around this (sort of) would be by using a bit of trickery. I have slapped one of your sample db's back up here to demonstrate this. If you want to try to duplicate this, here's what you have to do;

• Change the Default View of the form to Continuous, and adjust the positioning of the controls to mimic what a datasheet would look like (if the datasheet look is what you're after).
  
• Add a text box control to the form (delete the text box label - you won't need it). Set the Control Source of the text box to Item, and set it's Locked property to Yes (you don't want users being able to edit this text box).
  
• Make the text box is the same height and width as the combo box, minus the width of the drop down button itself. Place the text box over the top of the combo box. You may want to go to the Arrange tab and click Bring To Front when you do this just to make sure it stays in front of the combo box. This will make it appear as if it's just one combo box still.
  
• In the Got Focus event of the text box, set focus to the combo box;

Private Sub txtItem_GotFocus()

    Me.cboItem.SetFocus

End Sub

I say this sort of works because it's not perfect. When the combo box has focus it's still going to display the Action column. It's only after you move off the combo box that it appears to display the Item column.

 

[Keith Hawes]

Hi Sean Thanks for all the help. What you demonstrated does work but as you said on enter it first appears you have entered the action column. This could be misleading for an operator who is not aware of this situation. I have decided to display the action column 2nd so that the Item value is entered properly. It appears to be the only way. I guess we can say the post is now resolved but I am not sure how to do that. Can you do it Sean or do I have to. Thanks again for the time and effort you have put in. Kind regards Keith