Combo Box troubleshooting

[BobJ]

hi,

I previously created an order form which contained a combo box that when a "customer" was selected their address details would be automatically appear in textboxes on the form, it also cloned the customers address details into a series of Shipping address information textboxes with the following code:

Private Sub ClientID_AfterUpdate()
' Update ShipTo controls based on value selected in CustomerID combo box.
Me!ShipName = Me![ClientID].Column(1)
Me!ShipAddress = Me!Address
Me!ShipCity = Me!City
Me!ShipRegion = Me!Region
Me!ShipPostalCode = Me!PostCode
Me!ShipCountry = Me!Country
End Sub

However Ive now redesigned this system so it only displayed customer address details as the shipping address is rarely the same as the clients address details.
i also i created a new table for Courier details and a new combo box for it that works in the same way as the new version of the customer combo box.

How the problem is that both these combo boxes display a list of the customers however i cant click on any of them and im not sure what the problem is..

now my control source for the customers combo box is my ClientID which is an autonumber.
the row sourceis : SELECT DISTINCT Customers.ClientID, Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;

the same goes for the courier combo box but replace couriers with customers :>

im not sure why i cant select a customer or courier from the combo boxes.. you guys have any ideas? i c&ped the new one, but ive tried deleting and recreating them no joy.

 

[Dennisk]

the DISTINCT keywork may be the culprit. I customer table should only record one instance of each customer so the Distinct keyword is redundant. However if you have more than one instance of a the same customer in the table then the table is not correctly normalised.

 

[BobJ]

ah ok, yeah theres only 1 record of each customer, i deleted the distinct but i still cant select a customer.. hmmm intruiging. you gotta love/hate these niggling problems!

 

[neileg]

Just a thought. You say that the combo is bound to ClientID. That is the ClientID in your orders table, isn't it?

 

[BobJ]

yea, its bound to the original ClientID found in the Orders table atm through a query that incorporates the customer, courier and orders tables together.

I just removed the couriers table from the query and the customers combo box works , but it means cant get any data from the couriers table..

why would the query not work with the couriers table added?