the easiest way: in the subform, bind fieldA to a combo box. base the
RowSource of the combo box on that "second table". include all three fields
in the RowSource in order - A, B, C. set the combo box properties as
ColumnCount: 3
ColumnWidths: 1"; 0"; 0"
BoundColumn: 1
you can adjust the width of the first column from 1 inch to wider or
narrower, as needed.
so the values of fieldB and fieldC are available in the combo box control,
even though you can't see them in the "droplist" in form view.
in the combo box's AfterUpdate event procedure, add the following code, as
If Not IsNull(Me!ComboBoxName) Then
Me!ControlB = Me!ComboBoxName.Column(1)
Me!ControlC = Me!ComboBoxName.Column(2)
Else
Me!ControlB = Null
Me!ControlC = Null
End If
note that combo box columns are zero-based, so the first column (reading
left-to-right) is (0), the second column is (1), etc. note also that
duplicating data in two different tables is a violation of normalization
rules; though it *is* appropriate to break the rule in *some* circumstances,
you should make sure that you have a good business reason for doing so.
btw, if all you want to do is SHOW the data from fieldB and fieldC in your
subform, but *not* save it into the underlying table, just do the following:
set up the combo box as described above. in ControlB on the subform, set the
ControlSource to
= [ComboBoxName].[Column](1)
and for ControlC, set the ControlSource to
= [ComboBoxName].[Column](2)
hth
Barbara Ann said:
Need two fields on a sub form to populate from another table when a third
field is entered. The code I have from Access 97 doesn't work in Access 2000.
Can someone help? The sub form has fields A, B, C, and others. When entereing
A would like fields B & C to populate from a second table that contains field
data for A, B, & C.
