B
Bob Bridges
I've been coding for 30 years but I've only 2 or 3 years in VBA and there are
surprising holes in my knowledge, so this may be a newbie question.
Here's how it works now: TableA has a unique key. Each record in TableA has
zero-to-many corresponding records in TableB; TableB.KeyA points back to
TableA. FormA displays data from TableA LEFT JOIN TableB, so some TableA
records appear in the form more than once. Only one TableB field is
displayed in the form; let's call it X.
I'd like to change FormA so each TableA record shows only once, a combo box
in the Detail section showing zero-to-many Xs from TableB. I've never done
it in Access before; I've no idea whether it's a recondite and difficult
procedure, as common as dirt, or simply impossible. Here's what I've tried:
1) With ComboBox.RowSourceType="Value List" and .RowSource="11;14;20", each
record's combo box has the three test values in it. Well so far.
2) .RowSourceType="Table/Query", .RowSource="SELECT X FROM TableB WHERE
TableB.KeyA=10; " puts the correct two values in every combo box in the
detail section; we're making progress.
3) .RowSource="SELECT X FROM TableB WHERE TableB.KeyA=Forms![FormA]!Key; "
populates FormA's first repeat first record's combo box with the correct
value for that record, the correct value being 1818. It also puts 1818 in
every other record's combo box. Not so good: I want each FormA combo box to
display the TableB.X values corresponding to their proper TableA record. But
when I manually and experimentally change the contents of one combo box in a
live FormA, all the other combo-box contents instantly change to match.
So how do I accomplish this? Is there any way at all to populate a combo
box in the detail section from a query and have it show different results for
each record?
surprising holes in my knowledge, so this may be a newbie question.
Here's how it works now: TableA has a unique key. Each record in TableA has
zero-to-many corresponding records in TableB; TableB.KeyA points back to
TableA. FormA displays data from TableA LEFT JOIN TableB, so some TableA
records appear in the form more than once. Only one TableB field is
displayed in the form; let's call it X.
I'd like to change FormA so each TableA record shows only once, a combo box
in the Detail section showing zero-to-many Xs from TableB. I've never done
it in Access before; I've no idea whether it's a recondite and difficult
procedure, as common as dirt, or simply impossible. Here's what I've tried:
1) With ComboBox.RowSourceType="Value List" and .RowSource="11;14;20", each
record's combo box has the three test values in it. Well so far.
2) .RowSourceType="Table/Query", .RowSource="SELECT X FROM TableB WHERE
TableB.KeyA=10; " puts the correct two values in every combo box in the
detail section; we're making progress.
3) .RowSource="SELECT X FROM TableB WHERE TableB.KeyA=Forms![FormA]!Key; "
populates FormA's first repeat first record's combo box with the correct
value for that record, the correct value being 1818. It also puts 1818 in
every other record's combo box. Not so good: I want each FormA combo box to
display the TableB.X values corresponding to their proper TableA record. But
when I manually and experimentally change the contents of one combo box in a
live FormA, all the other combo-box contents instantly change to match.
So how do I accomplish this? Is there any way at all to populate a combo
box in the detail section from a query and have it show different results for
each record?