Combo box displays Customer ID number instead of name..????

  • Thread starter Thread starter John Bildy
  • Start date Start date
J

John Bildy

I have a simple Orders database.

The "New Orders" form allows new customers to be added directly onto the
form.

I also have a lookup combobox (to choose an existing customer) based on a
query:

SELECT DISTINCTROW [SurName] & " , " & [FirstName] & " , " & [Address] &
" , " & [City] & " , " & [PhoneHme] & " , " & [PhoneWrk] AS CustomerName
FROM Customers
ORDER BY [SurName] & " , " & [FirstName];

This combobox will not display customer names without an error message
saying "value isn't valid." May have text in a number field etc. The box
instead just displays the CustID number.

How can I get the combobox to display customer names (details) as per my
query?

If I set bound column to 0 instead of 1, names will show up fine, but they
are out of sync with customer details on the order form.

Thanks for any help.
 
It seems as if the combobox is bound to the customer Id number. If that is what
you need then you need to include that number in the underlying row source query.

SELECT DistinctRow CustomerID, SurName ...

Then set the combobox's column width to zero for the first column (enter 0 in
the column widths property). This will allow you show CustomerName in the
combobox and store customerId as the value.
 
How can I get the combobox to display customer names (details) as per my
query?
Click to expand...

Use TWO columns in the query, the ID and your concatenated name
expression. Use the ID as the Bound Column (because the field in your
form's table contains an ID number, not a name); but set the Column
Widths property of the combo box so that the width of the ID column is
zero (to suppress its display). The combo box wizard will do this for
you automatically if you use the query as the source of the combo.
 
Yep. Thanks John.
--
John B

"John Vinson" >
Use TWO columns in the query, the ID and your concatenated name
expression. Use the ID as the Bound Column (because the field in your
form's table contains an ID number, not a name); but set the Column
Widths property of the combo box so that the width of the ID column is
zero (to suppress its display). The combo box wizard will do this for
you automatically if you use the query as the source of the combo.
Click to expand...
 
Yep. Thanks John.
--
John B

John Spencer (MVP) said:
It seems as if the combobox is bound to the customer Id number. If that is what
you need then you need to include that number in the underlying row source query.

SELECT DistinctRow CustomerID, SurName ...

Then set the combobox's column width to zero for the first column (enter 0 in
the column widths property). This will allow you show CustomerName in the
combobox and store customerId as the value.

John said:
I have a simple Orders database.

The "New Orders" form allows new customers to be added directly onto the
form.

I also have a lookup combobox (to choose an existing customer) based on a
query:

SELECT DISTINCTROW [SurName] & " , " & [FirstName] & " , " & [Address] &
" , " & [City] & " , " & [PhoneHme] & " , " & [PhoneWrk] AS CustomerName
FROM Customers
ORDER BY [SurName] & " , " & [FirstName];

This combobox will not display customer names without an error message
saying "value isn't valid." May have text in a number field etc. The box
instead just displays the CustID number.

How can I get the combobox to display customer names (details) as per my
query?

If I set bound column to 0 instead of 1, names will show up fine, but they
are out of sync with customer details on the order form.

Thanks for any help.
Click to expand...
Click to expand...
 
Back
Top