Adding new table fields to form gets #NAME? error

[tseaman]

I am using Access 2000 with linked tables to a SQL Server 2000 backend database. I have a form whose record source is based on a simple query on a single table. Due to design changes two fields needed to be added to the table and then to bound text boxes on the form.
I added the fields to the table through Enterprise Manager. Then I ran the Linked Table Manager in Access to update the table definition. Opening the table in Access showed the new fields.
I added the new fields to the record source query. Running the query also showed the new fields.
I added two text boxes to the form and bound them to the new fields, which were listed in the control source dropdown. However, when I run the form I get "#NAME?" error displayed in the text boxes.
If I create a new form, using the same query as the record source on my original form, and put text boxes on the form for the new fields, it works fine.
Is my original form corrupted somehow? Does anyone know how to fix this? Or do I have to start fresh and recreate the form?
I have read Microsoft's explanation of the error and tried all the ways it suggests to solve the problem, but I still get the error.

 

[tracy1234]

I added two text boxes to the form and bound them to the new fields, which were listed in the control source dropdown."

And if you use the field list icon to add the fields when you're in design mode do you get the same error?

-Tracy

 

[Rolliee]

You have not properly tied the textbox to the field value. Look for a mis spelling. You do not have to recreate the form. Try pulling the field onto the form in the design mode from the field list. If that works, you can see what is wrong.

rollie@bwsys.net

 

[tseaman]

I still get the error when I add the fields from the field list.

If I put "=[field name]" in the control source of the text box the #NAME? error goes away. But when I run the form and try to update the text box in VBA code fired by an event on another control I get the Access error dialog with the message:

Run-time error '-2147352567 (80020009)': You can't assign a value to this object.

And clicking on the Debug button takes me to the VBA code where I am updating the text box.

In case the form has been corrupted, I have tried exporting the form to a new database then reimporting it. I have also tried exporting the object via the application "saveastext" method then reimport it with "loadfromtext". Both of these methods failed to clean up the form so it would work.

Any other ideas?

 

[Rolliee]

How big is your mdb? You could compact/repair it to get it as small as possible and then ZIP the file and send to me and I will troubleshoot it for you if you wish.

rollie@bwsys.net

 

[tseaman]

Rolliee,

Thanks for the offer, but I'm not sure what you could do since the backend is a SQL server database. Wouldn't you need to link to the tables to test the form?

 

[Rolliee]

In the design mode, make change to the mdb and then make it back. Then do a compile and step thru the code to see where your error occurs. If you can reconstruct the error in a sample mdb with imported tables (with GET External Data), you can do what I suggested.

rollie@bwsys.net

 

[tseaman]

I found the problem. There was some code that replaced the default record source with another query. That query did not have the new fields, thus the failure. After updating that query the form works fine.

Thanks for your offer to help.