#Name? Textbox Problem in Access 2007

[Don]

The datasource for a form is a query with 3 tables, one of which I just
added. On the form is a textbox for a field in the table I just added. The
textbox was added using the Field Chooser menu, so contains a field the form
presumably recognizes. When I view the form, the textbox displays "#Name?"
which supposedly means the texbox can't find the field. However, it was
selected using the Field Chooser when the form was in design mode. If the
source of the textbox is changed to one of the fields from one of the two
original tables in the query, no problem. However, all the fields from the
newly added third table yield "#Name?" The query itself runs fine drawing
data from all three tables. So it appears to be something in the textbox
control not recognizing the filed even though Field Chooser does.

Anyone else run into this? Any solutions?

Thanks!

Don

 

[Dirk Goldgar]

The datasource for a form is a query with 3 tables, one of which I just
added. On the form is a textbox for a field in the table I just added.
The textbox was added using the Field Chooser menu, so contains a field
the form presumably recognizes. When I view the form, the textbox
displays "#Name?" which supposedly means the texbox can't find the field.
However, it was selected using the Field Chooser when the form was in
design mode. If the source of the textbox is changed to one of the fields
from one of the two original tables in the query, no problem. However,
all the fields from the newly added third table yield "#Name?" The query
itself runs fine drawing data from all three tables. So it appears to be
something in the textbox control not recognizing the filed even though
Field Chooser does.

Anyone else run into this? Any solutions?

Please post:

1. The SQL of the form's Record Source query.

2. The Control Source of the text box.

3. The Name of the text box.

 

[Don]

Dirk,

1) Posting will have to wait until later as the databse is on my machine at
work.

2) The original source was OTFName. This is the actual filed name. In the
query, I changed it to "catfood" (catfood: OTFName) and made the
corresponding change in the textbox. No dice.

3) The textbox was renamed "txtATFName"

Thanks for posting!

Don

 

[Dirk Goldgar]

Dirk,

1) Posting will have to wait until later as the databse is on my machine
at work.

2) The original source was OTFName. This is the actual filed name. In
the query, I changed it to "catfood" (catfood: OTFName) and made the
corresponding change in the textbox. No dice.

3) The textbox was renamed "txtATFName"

Thanks for posting!

You're welcome. I'll need to see the SQL and have you confirm the names,
though. At the moment, I can't say what's wrong. We'll figure it out,
though.

 

[Don]

Dirk,

Here is the latest SQL using field renaming:

SELECT tblPowerPCInstructions.*, tblConditionCodeUse.*,
tblTestSetInformation.OTFName AS catfood
FROM (tblPowerPCInstructions LEFT JOIN tblConditionCodeUse ON
tblPowerInstructions.ID = tblConditionCodeUse.LinkID) LEFT JOIN
tblTestSetInformation ON tblPowerPCInstructions.ID =
tblTestSetInformation.InstructionID;

As I think you were implying, I have been burned in the past by the
control name being the same as the field name so I tried getting the
name as orthogonal to anything in the tables or forms as possible.
Obviously, for this case the control source is "catfood." No luck
with it working though.

The infuriating thing is that in design mode the field is recoginized
as it shows up in field chooser. The fact that the error message
(#Name?) is saying the form does not recognize the field is truly
bizzare!

Thanks!

Don

 

[Dirk Goldgar]

Dirk,

Here is the latest SQL using field renaming:

SELECT tblPowerPCInstructions.*, tblConditionCodeUse.*,
tblTestSetInformation.OTFName AS catfood
FROM (tblPowerPCInstructions LEFT JOIN tblConditionCodeUse ON
tblPowerInstructions.ID = tblConditionCodeUse.LinkID) LEFT JOIN
tblTestSetInformation ON tblPowerPCInstructions.ID =
tblTestSetInformation.InstructionID;

"Catfood", eh? One wonders, but does not ask.

So you're saying that the above SQL statement is the RecordSource of the
form, that you have a text box on that form named "txtATFName", and that the
ControlSource of txtATFName is "catfood". Correct?

Those facts being so, I don't see why you should be getting the #Name error
on this control, unless you also have some control whose name is "catfood".
I suppose it could be some sort of bug caused by Name AutoCorrect, so you
might try turning that off and see if the problem goes away.

As I think you were implying, I have been burned in the past by the
control name being the same as the field name

In fact, there is no inherent problem with a control name being the same as
the field name.

The infuriating thing is that in design mode the field is recoginized
as it shows up in field chooser. The fact that the error message
(#Name?) is saying the form does not recognize the field is truly
bizzare!

It seems so. If you'd like to send me a cut-down copy of your database,
containing only the elements necessary to demonstrate the problem, compacted
and then zipped to less than 1MB in size (preferably much smaller) -- I'll
have a look at it, time permitting. You can send it to the address derived
by removing NO SPAM and ".invalid" from the reply address of this message.
If that address isn't visible to you, you can get my address from my web
site, which is listed in my sig. Do *not* post my real address in the
newsgroup -- I don't want to be buried in spam and viruses.

 

[Don]

Dirk,

Responses are inlined.

Thanks!

Don

"Catfood", eh? One wonders, but does not ask.

You have to admit, the probability of a name collision is approaching
0.0 with that.

So you're saying that the above SQL statement is the RecordSource of the
form, that you have a text box on that form named "txtATFName", and that the
ControlSource of txtATFName is "catfood". Correct?

100% correct.

Those facts being so, I don't see why you should be getting the #Name error
on this control, unless you also have some control whose name is "catfood".
I suppose it could be some sort of bug caused by Name AutoCorrect, so you
might try turning that off and see if the problem goes away.

Well, after I changed Name AutoCorrect to OFF, I was rechecking
everything and found the form control source was a SQL statement that
corresponded to my two table version of the query and did not include
the third, new table. Now, I had checked that before and I would
swear up and down it was the query whose SQL is above. Changed the
source back to the query and viola, everything works! Well, another
lesson learned on things to check when errors show up.

In fact, there is no inherent problem with a control name being the same as
the field name.


It seems so. If you'd like to send me a cut-down copy of your database,
containing only the elements necessary to demonstrate the problem, compacted
and then zipped to less than 1MB in size (preferably much smaller) -- I'll
have a look at it, time permitting. You can send it to the address derived
by removing NO SPAM and ".invalid" from the reply address of this message.
If that address isn't visible to you, you can get my address from my web
site, which is listed in my sig. Do *not* post my real address in the
newsgroup -- I don't want to be buried in spam and viruses.

Thanks for the patience!

Don