SQL statement to populate unbound textbox

  • Thread starter Thread starter Bill Foley
  • Start date Start date
B

Bill Foley

I have a table that I am using as a lookup to populate a table with info
related to a specific ID field. When I look at the form, however, I would
like the other fields to be shown. Since I'm not actually wasting space
with those field information, the text boxes are set as unbound boxes. I
want to use an SQL statement to populate those boxes based on the ID field
on the form. Obviously, I am having problem. Here is an overview of the
problem I am having (using Access XP). By the way, I haven't done much in
the way of SQL before

The table being used as a lookup is - tblData
Field being used as the lookup is DataID
The fields that I also want populated on my form are Field1, Field2, Field3

My pitiful SQL statement applied as the Control Source to each unbound text
box that isn't working:

Select tblData.Field1 From tblData Where tblData.Field1 = Me!DataID

When a record is opened on the form that has this information, I am getting
the "name" error in the box.

TIA!

Bill
 
This is actually already on a sub form. My table that I am using only has
that key field that was used to capture the correct record from my data
table. however, showing the number "5" on the form means nothing. This is
why I am trying to use an SQL statement to pull that field from that data
table that relates to the ID field on my subform. Since this is on a
subform, I tried specifying the actual form instead of "Me", but it still
isn't working.

I have tried other variations (guessing), with the same error result.

Bill
 
You can still use a sub form in a sub-form (so, this is not really at issue
here).

The only real issue is how many fields you need from that other table. If it
is only ONE field, then my suggestion to use a sub-form is over kill.

You have several choices:

1) base the form on a query, and in the query simply join in those
additional tables (be it one table, or perhaps you have 5, or 6 additional
tables for lookup values..simply drop in those extra tables..and the field
values you need to display). It sounds in your case, you are only trying to
lookup some values from ONE other table. A query works quite well in this
regards.

2) If it is only ONE field that needs to pluck out the value, then just
place a text box on the form, and use dlookup.

=dlookup("fieldName","tableName","id = 5")

or something likely like:

=dlookup("fieldName","tableName","id = " & me!PartId)

I usually find option #1 above the easiest. And, in fact, you can still use
my sub-form idea (and that sub-form idea is good if you have several field
values you need to display from that other table).
 
Back
Top