Hi,

On an Access Form used to enter supplier invoices, when a SupplierID is selected in one textbox and 'focus' goes elsewhere I'd like default data from related fields in my tblSuppliers table to appear in corresponding textboxes.

So for instance, in tblSuppliers, the record for SupplierID 200 has a DefaultTaxCode of 5 and a DefaultProjectCode of 07.1.

On Lost Focus when a SupplierID has been selected (from a dropdown menu of valid SupplierIDs) I'd like to see the value 5 appear in the textbox 'txtTaxCode' and 07.1 appear in the textbox 'ProjectCode'. Of course the user has the option of leaving these as they are or changing them as necessary before submitting the record.

I know how to set the 'On Lost Focus' to trigger the code.

I know how, in the VBA Code window, to set the value of a textbox to something such as the value of another textbox or a default value.

I also know how, in the VBA Code window, to take the value of a textbox and to use DoCmd.RunSQL to Insert it into a database.

Is it just as simple to obtain the values of fields from a record in my tblSuppliers so they appear on the form?

Any assistance appreciated (IMG:style_emoticons/default/smile.gif)

Ap

I suggest that the Tax Codes themselves be stored in a table because they will be discrete. ie TaxCodeID, TaxCodeRate in a table called tblTaxCodes.
I suggest that the Default Tax CodeID be stored in a table with the suppliers info.
When you have changed the supplier ID you could do:
Me.TaxCodeID = DLookUp("TaxCodeID", "tblTaxCodes","SupplierID = " & Me.SupplierID)
You will need another control on the form to display the TaxCodeRate and you will need to add the tblTaxCodes to your underlying query. And you will want the control that displays TaxCodeID to be visible = false.

hope this helps

If you mean a combo box (you don't really select from a text box, but rather type an entry), you can refer to other columns in the combo box Row Source. For instance, you could make a query with the fields:

SupplierID
SupplierName
TaxCode
TaxCodeRate

Sort by supplier name. Use the query as the Row Source of the combo box. For the combo box (cboSupplier), Bound Column 1 (which is SupplierID), Column Count 4, Column Widths something like 0";1.5",0";0"

In this way you can select the SupplierName, but store SupplierID. To see the TaxCode, set the Control Source of a text box to:
=cboSupplier.Column(2)

For the TaxCodeRate, in another text box:
=cboSupplier.Column(3)

Column numbers in this situation start from 0, so Column(2) is the third column in the combo box Row Source. In the combo box Properties the column numbering starts from 1, so if Bound Column is 1 it is literally the first column. A bit confusing at first, but there it is.

Chris, Bruce,

Sorry for delay in response but I was busy putting your good work to work on Friday and I'm delighted to report thanks to your suggestions I got it to do what I wanted (IMG:style_emoticons/default/smile.gif)

First, Bruce, of course you're right it was a combo box I was referring to and your emphasis on this helped me understand conceptually what that process was doing. Then, as suggested, by picking up more data than I needed for that particular control then utilizing it in the others accomplished my end. The VLookUp approach I'll keep up my sleeve for another day (IMG:style_emoticons/default/smile.gif)

Chris's idea of using a separate tblTaxCodes of course makes sense in terms of following relational database rules. I'd been 'cheating' by using a value list but once I'd put them in a table of their own, by compiling all the relevant data in the query interrogated by the SupplierID combo box I had everything I needed.

Fantastic (IMG:style_emoticons/default/smile.gif)

Thanks again both,

Ap

We're both glad to help. Value lists are quite limited, as you have seen, so often a lookup table makes the best sense.

I will just mention that sometimes a subform for supplier information has advantages over using combo box columns. The combo box rather than the main form is the Link Master field; the Link Child field is the key field in the subform. This technique is especially helpful if you want to see, for instance, the entire supplier address with City, State, and Zip or Postal Code concatenated on one line.

Good luck with the project. (IMG:style_emoticons/default/thumbup.gif)