Control can't be edited; it's bound to unknown field <xxxx>

Hi,

I have an MS Access 2007 application running on my Vista Business PC which connects to a SQL Server 2000 database via ODBC.  On an Access sub-form I have two CheckBoxes connected to two fields through a select query.

The Sub-form was originally created with only one CheckBox and it worked fine.  I added a field to the database called Reimbursable_TF as NVARCHAR 5 with a default value of (0) - this is exactly the same as the first field I created connected to the first CheckBox except its name is different.

When I added the second CheckBox to the sub-form bound to the new field and tested the form I got the 'Control can't be edited; it's bound to unknown field <Reimbursable_TF>.  After trying a number of other things including Compact and Repair Database, in desparation I re-named the field in the SQL Server table to 'Reimbursable' and received the error message 'Control can't be edited; it's bound to unknown field <Reimbursable>' - in other words no improvement.  

I then tried adding a TextBox to the sub-form bound to 'Reimbursable' but it displays '#Name?' and I get the same error when I attempt to edit the field.

When I open the query for the sub-form I can edit the Reimbursable field in the Recordset and I can see the data is saved when I close the query and re-open it.

What do I have to do to make the Reimursable field editable?

Thanks,
merdeka
merdekaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lee555J5Commented:
The field you added is named ..._TF and you are binding it to a checkbox. Are you sure you want an NVARCHAR data type?
Lee
0
merdekaAuthor Commented:
lee555j5,

SQL Server 2000 doesn't have a Boolean data type so we have used NVARCHAR 5 throughout th program for True/False CheckBoxes without any problem and the other CheckBox control on the form seems to work fine using NVARCHAR 5.  

Most of the program is written in VB6, it's just this small part written in Access but the VB programmers will be accessing (no pun) the same data.

Thanks,
merdeka
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First off: I agree with Lee that a Boolean data is typically not tied to a Text datatype. I've always used a Bit datatype when dealing with SQL Server based boolean values.

Are you using linked tables? If so, then did you recreate your links AFTER you added the field to the server table? Access won't recognize this automatically - you've got to rebuild that link in some fashion.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

merdekaAuthor Commented:
LSMConsulting,

I considered using a bit data type too but the programmers use the NVARCHAR 5 data type which worked for the first CheckBox control in my MS Access form.

I am using linked tables and I did re-link the tables a few times since.  I can see the field in the form's query so I don't think that's the problem.  I can also add data to the Reimbursable_TF field in the form's query.

Thanks,
merdeka
0
merdekaAuthor Commented:
LSMConsulting,

I was out of town for a while so haven't commented on the problem lately.

I opened the program on another PC running MS Access 2003 and had the same error message as above using MS ccess 2007.   I then created a new form in MS Access 2007 using the same query as used in the form in question above and used the same control as above and the control works fine.

My suspicion is that there is some error in the MS Access compiler that doesn't include the new control.

Any comments or suggestions?

Thanks,
merdeka
0
lee555J5Commented:
I have seen this with Access in other situations, as well.
Recently, I was trying to import text data into an existing table. One field in the destination table was truncating the import data at 255 characters because the field was initially defined as Text. Even after I changed the table field to Memo, it still truncated the import data. Even copying the table structure to a new table and deleting the old did not help. Compact and Repair Database did not help. I even looked at the hidden system tables but did not see anything.
In the end, I had to create a new table and mimic the original table's structure except that one field which I created as Memo. Now, it works, but it cost me an hour. :-/
Sometimes in Access you have to start clean.
Lee
0
merdekaAuthor Commented:
Lee,

I created a new form by copying the controls and code from the old form and used the query I used in the new form above (which is actually copied from the original form's query) but still have the same problem.  The odd thing is that I added other controls bound to the same field in the original form and none of them worked (CheckBox, TextBox, RadioButton) so the problem doesn't seem to be related to a specific control type.

I will start from scratch and hope that works.

Thanks,
merdeka

0
lee555J5Commented:
That is what I would try, but do *not* copy the controls. Start the form and controls from scratch. It *is* ok to copy the code.
Lee
0
merdekaAuthor Commented:
Hi All,

I found the problem was that I didn't use the same recordset throughout the form.  I have made this mistake before but forgot about the problem and its solution as it happened some time ago and also I revisited his form to add this field after not looking at the form for a few months.

My apologies, I will award the points 50/50.

Thanks,
merdeka
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.