Combo Box with multiple table fields

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a combo box that selects 5 fields from a table, I
want it to put that data in a different table but it
errors out. The Row source expression seems to work, the
separate data fields show up in the combo box, other than
the phone numbers are not formated as they are in the
tables. It errors out when I try to select it.

The "Row Source" expression is, SELECT [AdjInfotbl].
[AdjName], [AdjInfotbl].[AdjPhone], [AdjInfotbl].
[Adjinfotbl].[AdjFax]; and so on.

The "Control Source" Expression is, =[ClaimEntrytbl]![Adj]
And[ClaimEntrytbl]![AdjPhone]And [ClaimEntrytbl]![AdjFax]
and so on.

The error I get is "Control can't be edited; It's bound to
unknown field '[ClaimEntrytbl]![Adj]And[ClaimEntrytbl]!
[AdjPhone]And [ClaimEntrytbl]![AdjFax] and so on.

Thanks for any help you can provide, Bob
 
I have a combo box that selects 5 fields from a table, I
want it to put that data in a different table but it
errors out. The Row source expression seems to work, the
separate data fields show up in the combo box, other than
the phone numbers are not formated as they are in the
tables. It errors out when I try to select it.

The "Row Source" expression is, SELECT [AdjInfotbl].
[AdjName], [AdjInfotbl].[AdjPhone], [AdjInfotbl].
[Adjinfotbl].[AdjFax]; and so on.

The "Control Source" Expression is, =[ClaimEntrytbl]![Adj]
And[ClaimEntrytbl]![AdjPhone]And [ClaimEntrytbl]![AdjFax]
and so on.

The error I get is "Control can't be edited; It's bound to
unknown field '[ClaimEntrytbl]![Adj]And[ClaimEntrytbl]!
[AdjPhone]And [ClaimEntrytbl]![AdjFax] and so on.
Click to expand...

The first question to ask is - why do you want to store this
information redundantly anyway!? Enter data ONCE, store it ONCE, and
then use a query to look it up!

That said, a Control Source must be a single field if you want to
store data into it. If (against my better judgement) you want to store
five fields, you'll need to choose one of them as the Control Source
of the combo, with a matching Bound Column property; and put four
other textboxes on the form (they can be invisible if you want) and
"push" values into them in the Combo's Afterupdate event:

Private Sub comboboxname_AfterUpdate()
Me!txtAdjPhone = comboboxname.Column(1)
Me!txtAdjFax = comboboxname.Column(2)

and so on. The Column() property is zero based so (1) is the SECOND
field in the rowsource query.
 
Back
Top