I'm getting mad with myself because I'm sure I'm missing something simple. The problem is this:
I have a form (Spares Order) which has a has table as it's control source.
On the form is a Combo Box with 2 columns (Part Number) and (Part Description).
There are also 2 text boxes (part number) and (part description) on the form. Part number's control source is (Part Number) in the underlying table and updates when a selection is made from the combo box.
The text box (Part Description) has "=combobox.column(1)" as it's control source and thus displays the description.
Here's the problem; how do I store the description in the underlying table?
I have tried making my (part description) text box's control source a field in the underlying table and then using the onClick property of the combo box to update it using the expression builder [part description]=[combobox.column(1)] but no joy. In principle is this OK? What's wrong with the syntax?
Any suggestions as to how to make it would be appreciated as would an explanation of why my onclick soloution wont work.
You said you were using the onclick even to update the text box on your form. Are you sure this is the desired event? This event will occur when the user clicks the drop down box. Don't you want the assignment to occur after the user has selected a value, aka AfterUpdate?
Thanks to all who have replied to date, in answer to your questions:
The combo box is populated from a linked table which is part of a seperatate database. We have a number of different machine types and they all have individual databases to manage their spares. Not ideal I know it just evolved that way over the years, one consequence is that spares from differant databases don't have a unique identifier. There will be other combo boxes on the Form for each individual machine type.
If this was going to be a long term soloution I'd start from scratch and rationalise everything in the one database but it's just a stop gap.
As I said I would welcome suggestions for alternative soloutions but for the sake of my sanity I'd like to know why the problem as described doesn't work.
You are of course correct I hadn't got around to refining that bit yet I just wanted it to do SOMETHING.