Showing value of other table

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

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
 
Consider using a Control Source of =DLookup(...). Details of DLookup are in
Help.

Larry Linson
Microsoft Office Access MVP
 
Lars Brownies said:
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)?
Click to expand...


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.
 
Thanks Dirk, Larry,
Somehow I thought that a join query would cause the query to become
read-only. I checked but it works.

Lars

Dirk Goldgar said:
Lars Brownies said:
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)?
Click to expand...


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)
Click to expand...
 
Lars Brownies said:
Thanks Dirk, Larry,
Somehow I thought that a join query would cause the query to become
read-only. I checked but it works.
Click to expand...


Some queries with complex joins are read-only, but simple queries usually
aren't.
 
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
 
Back
Top