Lookup field woes

[BruceM]

I have inherited a database with a table (tblFeedback) that contained a
lookup field called [Status]. I think I have changed it to a regular text
field (by clicking the Lookup tab and changing it from combo box to text
box), but when I try to reference it on a report that is bound to
tblFeedback (by setting the Control Source of an unbound text box to
=[Status], I get #Error. It only happens with that field. I need to do
some concatenation in that text box, so I need that method of referencing
the field rather than binding the text box to[Status]. If I bind a text box
(txtStatus) to [Status] it works, and I can get around the #Error problem by
referencing the control (txtStatus) rather than the field, but I wonder if
there is a way I can purge the Lookup Field remnants (if that is what's
going on) from the database.

 

[John Vinson]

I think I have changed it to a regular text
field (by clicking the Lookup tab and changing it from combo box to text
box), but when I try to reference it on a report that is bound to
tblFeedback (by setting the Control Source of an unbound text box to
=[Status], I get #Error.

My guess is that the table does not contain a field named Status at
all, but rather a StatusID (a link to the numeric primary key of the
Status table). Try basing your report, not on the table itself, but on
a query joining the table to the Status table.

John W. Vinson[MVP]

 

[BruceM]

Hi John,

Thanks for replying. Status is a text field in the table. I could use the
Status field as the control source for a text box, but I could not use
=[Status] as the control source for an unbound text box. I think the
problem was that I had a text box named Status (thanks to Access's automatic
naming) in a forgotten corner of the form. Also, I imported everything into
a new blank database. I'm not sure if straightening out the naming of the
new database did the trick, but I think if I had waited until morning and
looked at it again I would have found the answer.

I think I have changed it to a regular text
field (by clicking the Lookup tab and changing it from combo box to text
box), but when I try to reference it on a report that is bound to
tblFeedback (by setting the Control Source of an unbound text box to
=[Status], I get #Error.

My guess is that the table does not contain a field named Status at
all, but rather a StatusID (a link to the numeric primary key of the
Status table). Try basing your report, not on the table itself, but on
a query joining the table to the Status table.

John W. Vinson[MVP]

 

[John Vinson]

Hi John,

Thanks for replying. Status is a text field in the table. I could use the
Status field as the control source for a text box, but I could not use
=[Status] as the control source for an unbound text box. I think the
problem was that I had a text box named Status (thanks to Access's automatic
naming) in a forgotten corner of the form. Also, I imported everything into
a new blank database. I'm not sure if straightening out the naming of the
new database did the trick, but I think if I had waited until morning and
looked at it again I would have found the answer.

The other thing you should do is use Tools... Options and *uncheck*
Name Autocorrect. It's the source of all sorts of wierd errors like
this.

I agree, Microsoft's convention of naming controls the same as the
name of the bound field is annoying!

John W. Vinson[MVP]