Combo Box to Text Box? (1 Viewer)

[APJ]

I am trying to display a supplier catagory on an order entry form that automaticaly appears when you select the supllier. i.e. pick supplier "Sainsburys" and the catagory "A" appears in a text box labelled "Supplier Catagory" The Supplier name is taken from a table "Suppliers". this table has three fields "ID"(AutoNumber), "Supplier" (The name of the supplier) and "Catagory"(a single letter A - F. When the supplier name is selected from the combo box the result is saved in a Table "Purchase Orders" I do not need to save the supplier catagory in the "Purchase Orders" Table but I can do if needed. Any ideas as this is driving me round the twist. You would think that this is a simple request of access, wouldn't you?

 

[Pat Hartman]

Make sure your combobox is bound to the correct column. It should save the SupplierID in your order record even though it is displaying the supplier name. Add an unbound control to the form to display the supplier category. Then in the AfterUpdate event of the combobox put the following:
Me.txtSupplierCat = Me.cmbSupplier.Column(2)

The above assumes that the unbound textbox is named txtSupplierCat and the combobox is named cmbSupplier. It also assumes that the category is the third field in the combo's recordsource.

You will need the same line of code in the forms Current event so that category will show for existing records.

The other alternative is to use a join in the form's recordsource that will allow you to include the category in the form's recordsource. If you choose this method, make sure you set the category field to locked so that it is not updated by accident.