Multi-Column Combo Box, How do I display both columns?

[hytina]

I am a amateur Access 2000 user so this may sound too basic for most of you but here goes..

I have a form that I need to add a combo box to that allows the user to select a customer by customer key (ID), it would also display the name of the customer associated to the customer key. I am having trouble getting both combo box columns to display on the form.

Does the second column which is the customer name need to have its own text or combo box? Then are the two combo boxes linked some how? Or do I do a Update to instruct the first combo box to put data into the second combo box? FYI-There are two tables, a Customer table and a Project database table involved with this form.

I have read so much information that I am now getting confused as you can tell. I do not know visual basic yet.

I am looking forward to hearing back, thanks in advance!!!

 

[FancyPrairie]

The Column Count property of the combobox indicates how many columns the Row Source represents. The Column Width property indicates how wide each column is to be (separated by semicolons). So if you want to hide a column, set the width to 0. However, in your case try setting the Column Width to 1";1"

 

[EPT]

I'm guessing here but I'm assuming the customerID is the PK for the table. When you create a combo box through the wizard, you have the option to show the PK column in the box BUT the default is to hide the PK. There is a box you can uncheck so the combo box shows both columns. Under what circumstances would you want to select a customer by the ID?
Ed

 

[hytina]

I had adjusted my column widths, and I can see both of the columns on the drop down, but once I select the item, only the first column which is the customer Id (& is also a PK in its table) is the only column that is visible on the form.

I did notice the option to hide the PK on the wizard prompts and chose to not hide the PK. That only helped on the drop down list/menu.

Even though my form is not an "order" form, it works the same. In a typical order form the user selects the customer and all of his address info drops onto the form. I only need the customer name to drop into the form once the customer id is selected.

Thanks for the quick reply FanciePrairie and EPT, it is much appreciated!

 

[hytina]

I finally figured this out. In the combo box row source I set up a query that selected the data I needed out of the customer table, set bound=1, column=2. Then I set up a text box with control source linked to the combo box and the column with customer name.
=[cbocustomerID].[Column](1)

Now the problem, The customer Id selected in the combo box will update the main table, but the text box will not. I need the customer ID and customer name entered into the table when the form is used.

Does anyone have any ideas on this subject? Thank you!

 

[FancyPrairie]

I'm kindof lost (end of the day Friday). But if I understand, you want to update a customer ID field and a customer name field in your table based on the item selected from the combobox. Try this: add 2 fields to your form and set their control source to the customer ID field from your table and customer name field from your table. Then hide both fields.

In the BeforeUpdate event of your form do this:

if (not isnull(cbocustomerid)) then
lngCustomerID = cbocustomerid.column(0)
strCustomerName = cbocustomerid.column(1)
end if

 

[hytina]

Thank you FancyPrairie for all your help.

This morning I took another approach and it worked!! I can't tell you how happy I am. Why I din't try it before I'll never know, but like I said I am AMATUER status.

I used the after update on the customer key(ID) combo box and typed in the following code and it worked!

Private Sub Customer_Key_AfterUpdate()
'Update Customer_Name controls based on value selected in Customer_Key combo box.
Me!Customer_Name = Me![Customer_Key].Column (1)
End Sub

Thanks again!