#name? error

  • Thread starter Thread starter Roger De Jonckheere
  • Start date Start date
R

Roger De Jonckheere

Hi NG,

I'm sure this has been discussed here many times before, but I was to tired
(and a bit lazy) to search for the right thread and I must admit, that I am
newbie in access (vba) programing logic, so here's my situation:
I have a address table (table1) just containing the zip code but not the
location. I have a second table (table2) containing the zip and location.
I created a form to enter those addresses and just supplied an entry filed
for the zip code and a second textbox (ehich is locked) and should show the
appropriate location name... Guess what? I see nothing else than "#name?" in
that text box, no matter what I try to set as Control Source. What do I have
to put there that it dynamically changes the value (location name) when I
change the zip code in the form?

Please, someone enlighten me. Thank you.
Roger
 
The #Name? error could be caused by any of the following items:

(1) the field that your control's Control Source references is *not* in the
form's Record Source;

(2) you have a control named the same as the field that you're using and
that control is not bound to that field

(3) you have two fields in the form's Record Source with the same field name
so you need to disambiguate the field you want by prepending "tablename." to
the front of the field name.
 
"Ken Snell" wrote :
The #Name? error could be caused by any of the following items:
(1) the field that your control's Control Source references is *not* in the
form's Record Source;
(2) you have a control named the same as the field that you're using and
that control is not bound to that field
(3) you have two fields in the form's Record Source with the same field name
so you need to disambiguate the field you want by prepending "tablename." to
the front of the field name.
Click to expand...

the first (1) cause could be true:
I made the form using the wizard out of my address table and added the
localtion textbox manually in the design view. Therefore I don't think its
reference is there... (how do I add it?!)

my control source looks like:
=(SELECT [table2]![location] FROM [table2] WHERE [table2]![zip]=zip)

the name of the text box I am using is unique

Thanks a lot,
Roger
 
You cannot use an SQL statement (what your SELECT etc. statement is) in a
textbox's control source. Also, your syntax for the SQL (even if you could
use it) is not correct -- the ! operator is not used between a table name
and a field name -- instead you use a . operator.

What you want to use in the control source is a DLookup function, which
allows you to "look up" a value for a field in a table or query. Try this as
the control source of that textbox:

=DLookup("location", "table2", "zip=" & [zip])

The above assumes that the field named zip is a numeric field. If it's a
text field, then use this:

=DLookup("location", "table2", "zip='" & [zip] & "'")

I've delimited the "zip" value from the field in the form's record source
with ' characters.

--
Ken Snell
<MS ACCESS MVP>

Roger De Jonckheere said:
"Ken Snell" wrote :
The #Name? error could be caused by any of the following items:
(1) the field that your control's Control Source references is *not* in the
form's Record Source;
(2) you have a control named the same as the field that you're using and
that control is not bound to that field
(3) you have two fields in the form's Record Source with the same field name
so you need to disambiguate the field you want by prepending
Click to expand...
"tablename."
to
the front of the field name.
Click to expand...

the first (1) cause could be true:
I made the form using the wizard out of my address table and added the
localtion textbox manually in the design view. Therefore I don't think its
reference is there... (how do I add it?!)

my control source looks like:
=(SELECT [table2]![location] FROM [table2] WHERE [table2]![zip]=zip)

the name of the text box I am using is unique

Thanks a lot,
Roger
Click to expand...
 
=DLookup("location", "table2", "zip=" & [zip])

after figuring I need to use semicolons instead of comma's, this worked
perfectly... thanks a lot. I tried to use that Dlookup thingie earlier, but
couldn't find out the correct syntax... anyways, thanks again.

Roger
 
Back
Top