Text Box values based on Combo Box value

[Sandy]

On 'frmAllRepairs' I have two combo boxes - the first 'cboRepairCategory'
determines the list for the second:- 'cboRepairType' using the following
code:

Private Sub cboRepairCategory_AfterUpdate()
On Error Resume Next
Select Case cboRepairCategory.Value
Case "Grips"
cboRepairType.RowSource = "tblGrips"
Case "Loft and Lie"
cboRepairType.RowSource = "tblLoftAndLie"
Case "ReGlue"
cboRepairType.RowSource = "tblReGlue"
Case "Shafts"
cboRepairType.RowSource = "tblShafts"
End Select
End Sub

A selection is then made in the second combo box from say 'tblGrips'.

'tblGrips' has three fields
1) Repair Type (Text) - which supplies the values to the combo box
2) Unit Cost to Buy (Currency) - which I would like to appear automatically
in text box 'txtBuyPrice'
3)Unit Cost to Sell (Currency) - which I would like to appear automatically
in text box 'txtSellPrice'

Any ideas?
Sandy

 

[Pat Hartman]

Rather than maintaining separate tables, you will find it easier if you
merge the detail items into a single table. You would then use a query with
selection criteria to select just one group at a time. The criteria would
reference the first combo. So -
In the first combo's AfterUpdate event you would have:

me.cboRepairType.Requery

In the query for the RowSource of the repair type combo -

Select ....
From tblRepairType
Where Category = Forms!yourform!cboRepairCategory;

You will also need the requery in the form's current event.

Me.cboRepairType.Requery

Using a single table for the type values will allow you to enforce
referential integrity which is a good thing.