Get value from table upon changing value in combo box.

[Tim]

I'm working on a form where a combo box gets its list of values from a
table. That same table also contains some "default notes" in other
fields. My task is simple, but I'm having a hard time working it out.
Once an option is selected in the combo box, I want to run (a macro?)
that will grab the appropriate default notes from the table, and load
them into the form in the various Notes fields.

I've got a query set up that'll narrow the table down to the option
selected in the combo box, but when I use the SetValue action in the
macro, I get an error saying that I'm trying to use a table that is
not available for automation. Right now my SetValue action is set up
like this:

Item = [Forms]![FormMain]![Notes1TXT]
Expression = [ComboTable]![DefaultNote1]

Questions:
1. Can tables be referenced at all in SetValue actions in Macros? That
seems to be the hang up?
2. Is there a better way to go about this entirely?

Thanks in advance. Please email response to ti**********@gmail.com.
Thanks! :-)

[Roger]

On Jun 6, 10:39*am, Tim <[e-mail]:

I'm working on a form where a combo box gets its list of values from a
table. That same table also contains some "default notes" in other
fields. My task is simple, but I'm having a hard time working it out.
Once an option is selected in the combo box, I want to run (a macro?)
that will grab the appropriate default notes from the table, and load
them into the form in the various Notes fields.

I've got a query set up that'll narrow the table down to the option
selected in the combo box, but when I use the SetValue action in the
macro, I get an error saying that I'm trying to use a table that is
not available for automation. Right now my SetValue action is set up
like this:

Item = [Forms]![FormMain]![Notes1TXT]
Expression = [ComboTable]![DefaultNote1]

Questions:
1. Can tables be referenced at all in SetValue actions in Macros? That
seems to be the hang up?
2. Is there a better way to go about this entirely?

Thanks in advance. Please email response to [e-mail].
Thanks! :-)

a combo box (cmbName) can have more than 2 fields
(I'm assuming you currently have 2)

if the current rowsource is "select id, name from table", change it to
"select id, name, notes from table"

under combo box properties, set columnCount to 3
and columnWidth to 0,1,0
(shows only name in box)

and the control source for notes1txt would be "=cmbName.column(2)"

now when you choose a new name from the combo box, the notes field is
updated accordingly