Get two fields from another table after entering data matched to .

[Guest]

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.

 

[tina]

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

 

[Guest]

this is very helpful. Unfortunatley there is a realy business name to save
the data from Columns A, B, & C, into the second table for current visibility
and historical record. Can that be done with the combo box scenario? thanks
for all your help.
ba

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

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.

 

[tina]

yes. i posted two solutions, and that was the first one:

**************
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.
**************

do you have a specific question re this solution?

hth