Setvalue, Form, Marcro & Query Quest

[Tony]

Hi All,

I've got an issue which I'm assuming is simple to solve and hope you can
help.

I've got a database with two tables, Requests & CallerID. On a form
(AddRecord), I would like the user to be able to select a value from a combo
box (CallerIDNumber) that uses a field in Requests as its source. After a
value is selected, an unbound text control on the form should show data
(FXNumber) from CallerID. I've set up a query that gets the correct
information from CallerID and it works correctly when I run it. However, I
can't figure out how to get the value from IDNumbers into the control on the
form.

The following query returns the correct record from CallerID:

SELECT CallerID.CallerIDNumber, CallerID.FXNumber, CallerID.ContactName
FROM CallerID
WHERE (((CallerID.CallerIDNumber)=[Forms]![AddRecord]![txtCallerIDNumber]));

The CallerIDNumber on the form calls the GetCallerIDInfo macro that has this
info:

Action - SetValue
Item - [Forms]![AddRecord]![txtFXNumber]
Expression - [FXNumber]

However, when I run the form and select a CallerID number from the combo
box, I get the following error:

"The object doesn't contain the Automation object 'FXNumber'.

I suspect that I'm not addressing the query data correctly to get the data,
but am stumped. Any help is greatly appreciated.

Thanks & Ciao,

Tony

 

[Steve Schapel]

Tony,

There are 2 approaches to this situation... neither of which involve
the use of a SetValue macro

The simplest and most efficient way to do it is base your form on a
query that includes both tables, suitably joined on the CallerIDNumber
field from both. Include the FXNumber field from the CallerID table
in the query, and bind your form control to it, instead of using an
unbound textbox. Then, as soon as you select the CallerIDNumber from
the combobox, the corresponding FXNumber will be shown.

The other approach is to use an expression in the controlsource of
txtCallerIDNumber. This could either use a DLookup() function, or, if
you make your combobox multi-column and include the FXNumber in its
RowSource query, you can refer in the expression to the column, e.g.
=[CallerIDNumber].[Column](2)

- Steve Schapel, Microsoft Access MVP

 

[Tony]

Steve,

Went the first route & it works great! Thanks for the advice. One question
if you're still following the thread...

Is this the best way to do things because it's the cleanest way to
manipulate the data? I'll be putting this technique behind a lot more
things in the future and am curious.

Again, thanks a million & Ciao,

Tony

Tony,

There are 2 approaches to this situation... neither of which involve
the use of a SetValue macro

The simplest and most efficient way to do it is base your form on a
query that includes both tables, suitably joined on the CallerIDNumber
field from both. Include the FXNumber field from the CallerID table
in the query, and bind your form control to it, instead of using an
unbound textbox. Then, as soon as you select the CallerIDNumber from
the combobox, the corresponding FXNumber will be shown.

The other approach is to use an expression in the controlsource of
txtCallerIDNumber. This could either use a DLookup() function, or, if
you make your combobox multi-column and include the FXNumber in its
RowSource query, you can refer in the expression to the column, e.g.
=[CallerIDNumber].[Column](2)

- Steve Schapel, Microsoft Access MVP

Hi All,

I've got an issue which I'm assuming is simple to solve and hope you can
help.

I've got a database with two tables, Requests & CallerID. On a form
(AddRecord), I would like the user to be able to select a value from a combo
box (CallerIDNumber) that uses a field in Requests as its source. After a
value is selected, an unbound text control on the form should show data
(FXNumber) from CallerID. I've set up a query that gets the correct
information from CallerID and it works correctly when I run it. However, I
can't figure out how to get the value from IDNumbers into the control on the
form.

The following query returns the correct record from CallerID:

SELECT CallerID.CallerIDNumber, CallerID.FXNumber, CallerID.ContactName
FROM CallerID
WHERE (((CallerID.CallerIDNumber)=[Forms]![AddRecord]![txtCallerIDNumber]));

The CallerIDNumber on the form calls the GetCallerIDInfo macro that has this
info:

Action - SetValue
Item - [Forms]![AddRecord]![txtFXNumber]
Expression - [FXNumber]

However, when I run the form and select a CallerID number from the combo
box, I get the following error:

"The object doesn't contain the Automation object 'FXNumber'.

I suspect that I'm not addressing the query data correctly to get the data,
but am stumped. Any help is greatly appreciated.

Thanks & Ciao,

Tony