Showing value of other table

[Lars Brownies]

With a combobox it's easy to invisibly hold an ID_number and actually show
the corresponding 'real' value that is stored in another table. In stead of
in a combobox I would like this functionality in a text box but there is no
rowsource property. Is this even possible with a textbox or should I use an
invisible combobox and fill the text box with =cmbTest.column(1)?

Thanks,

John

 

[Larry Linson]

Consider using a Control Source of =DLookup(...). Details of DLookup are in
Help.

Larry Linson
Microsoft Office Access MVP

 

[Dirk Goldgar]

With a combobox it's easy to invisibly hold an ID_number and actually show
the corresponding 'real' value that is stored in another table. In stead
of in a combobox I would like this functionality in a text box but there
is no rowsource property. Is this even possible with a textbox or should I
use an invisible combobox and fill the text box with =cmbTest.column(1)?

Normally, to do this you would modify the form's recordsource query to join
the other table and include the field you want to show from that table.
Then you'd just bind a text box to that field. That is by far the simplest
and most efficient way to do it.

If for some reason you can't do that, then you can have a text box whose
controlsource uses the DLookup function to lookup and display the field from
the other table. The controlsource expression would be something like this:

=DLoookup("FieldToShow", "OtherTable", "IDField=" & [IDFieldOnForm])

However, that is inefficient and tends to be slow.

 

[Lars Brownies]

Thanks Dirk, Larry,
Somehow I thought that a join query would cause the query to become
read-only. I checked but it works.

Lars

With a combobox it's easy to invisibly hold an ID_number and actually
show the corresponding 'real' value that is stored in another table. In
stead of in a combobox I would like this functionality in a text box but
there is no rowsource property. Is this even possible with a textbox or
should I use an invisible combobox and fill the text box with
=cmbTest.column(1)?

Normally, to do this you would modify the form's recordsource query to
join the other table and include the field you want to show from that
table. Then you'd just bind a text box to that field. That is by far the
simplest and most efficient way to do it.

If for some reason you can't do that, then you can have a text box whose
controlsource uses the DLookup function to lookup and display the field
from the other table. The controlsource expression would be something
like this:

=DLoookup("FieldToShow", "OtherTable", "IDField=" & [IDFieldOnForm])

However, that is inefficient and tends to be slow.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Dirk, Larry,
Somehow I thought that a join query would cause the query to become
read-only. I checked but it works.

Some queries with complex joins are read-only, but simple queries usually
aren't.

 

[Lars Brownies]

I see. Also: I now notice that when you base the form on a table it will
show you the defaultvalues before the record is actually made. When the form
is based on a query it will show the defaultvalues only after you start
entering data.

Lars