Combo Box

[PShiers]

Wonder if someone can confirm if the following is possible, and if yes then how to do it.
Backgroung: I've setup a table call 'Towns' to how hold list of all towns, and in the customer table the town in the customer address is a key e.g 20 which is e.g. 'Dover' in the Town table (20 = Dover). So I create a query that links key to the value so the what displays is 'Dover' - basic stuff.

Problem is that I what the Town field on the customer form to be a combo box, for two reasons:
1. If 'Dover' is wrong user can select from list of Towns and right Town - And want the key of the new town to save to Customer table
2. If setting up new customer can select town.

I'm OK with reason two, but how do I get the combo box that is populated from Table 'Towns' to display the 'Town' of an existing customer, so it can be change if needed.

Hope this make sense

Many thanks

 

[cheekybuddha]

Base your form just on the customer table (not a query).

Add your combobox - in the properties set Column Count = 2, and Column Widths = 0.

Set the ControlSource of the combo to the key field in the Customer table

Set the RowSource of the combobox to table Towns.

 

[Pat Hartman]

After adding the combo to the form and giving it a proper name rather than combo25:
On the Data Tab of the Properties dialog
1. RowSource = Select the TownID first and the the TownName second, order by TownName in the RowSource query
2. LimitToList = Yes
3. AllowValueListEdits = No

On the Format Tab of the Properties dialog
4. ColumnCount = 2
5. Column Width = 0;2

 

[cheekybuddha]

Re 5. if you just enter 0 then the second column will be as wide as the width of the combo.

If you specify 2 then the width of the dropdown list may not match that of the combo which can look a bit hinky. Test and see what suits you best.

 

[Pat Hartman]

Re 5. if you just enter 0 then the second column will be as wide as the width of the combo.

I never use tricks like this. You never know when they will stop working. "2" was arbitrary. Pick a number that works and you may also need to set the the width of the combo.

 

[PShiers]

Base your form just on the customer table (not a query).

Add your combobox - in the properties set Column Count = 2, and Column Widths = 0.

Set the ControlSource of the combo to the key field in the Customer table

Set the RowSource of the combobox to table Towns.

Excellent, Thanks for the help

 

[cheekybuddha]

Glad it worked for you.

(It did work for you, didn't it?)