Text Box Won't Bind

[HowardOfOcal]

This is driving me nuts.



In Access 2010 I opened an .mdb file originally created with Access 2003. I added a field to a table, then added the field to a query. I opened a form (in design mode) which used the query as its source.

I then drug the field from the form's field list to the form.

This always worked before, but now I get the dreaded #NAME? error in the field.

I can update the field in a query data sheet view, but it won't bind to the form.

[June7]

If Compact & Repair does not fix, might have to rebuild something, maybe even the table.

Do you have to retain the mdb version?

[HowardOfOcal]

If Compact & Repair does not fix, might have to rebuild something, maybe even the table.

Do you have to retain the mdb version?

Compact/Repair didn't help. I don't need the .mdb. I looked up on Microsoft support how to convert it to 2010 but the directions didn't work - I couldn't find the tabs they described.

It can't be that hard to convert, I just don't know how.

What's interesting is that I deleted several fields from the form, from the associated query and its supporting table. Then I closed the file and reopened it. I then redefined the old fields and the new one in the table. I put all of them in the query and then drug them to the form. The old fields worked, the new one didn't.

I'd love to hear how that's a design feature!

[boblarson]

The best way to convert to the newer version is to IMPORT ALL OBJECTS into a new, blank 2010 ACCDB file. It will eliminate a lot of problems that will still occur if you try to use the built in conversion stuff.

[June7]

I agree with Bob. 2003 to 2010 is a fairly big jump. I did a conversion at work from 2003 to 2007 with only a couple of small issues by doing a Save As. If that is not available in 2010 (not at home to check on this), then do import.

[boblarson]

I've just found that the safest, most effective way to not experience issues is to do the import instead of the save as.

[RuralGuy]

A RecordSource of a Form does not update when you add a field to a table of the RecordSource. Delete the RecordSource of the form and then assign it again.

[June7]

RG, that depends:
If RecordSource is an SQL statement and the statement uses the wildcard, then the new field will be available to the form. If doesn't use wildcard, no reason to delete, just open the query builder from the RecordSource and add field to the grid.

If it is a query object and the field is added to the grid, then it is again available to the form.

And if the RecordSource is just the table, then the new field is definitely available to the form.

In this case, it seems the OP is seeing the field in the form's RecordSource, but ControlSource is not recognizing the field.

[HowardOfOcal]

Well, I may have been cursing Access 2012 when it wasn't the problem.

First, I did what you suggested and imported all my stuff into a new 2012 database. But, the problem was the same.

Then, I was about to give up. So, I reinstalled Access 2003 and guess what, I couldn't add a field to the form in the original .mdb file with 2003 either!!!!!

I then used Access 2003 to open backup copies of the .mdb database from years ago. I couldn't add fields to the form there either. But, I could add fields to other forms.

So, I went back to Access 2012 and my .accdb version and I could add fields to other forms there as well.

So, it currently appears that there has been a problem in one particular form for years but I just discovered that fact. Now the trick is to figure out what is going on with that one particular form. Some of the event procedures triggered by controls on this form do use a lot of SQL so I'm trying to make myself understand some of the comments that have been made. I'm not a guru Access developer.


------Well, it's the next morning now and I've solved the problem. Operator Error!-----

This is the danger of trying to modify an application originally written years ago. I forgot that I overrode the query behind the form with a me.recordsource sql statement but didn't include the new field in the query. So, no wonder it couldn't bind to the form.

My apologies to Access 2010. It works great.

[boblarson]

Well, I may have been cursing Access 2012 when it wasn't the problem.

Since there isn't an Access 2012 then it would be obvious that wasn't the problem ( - I know, you meant 2010).

First, I did what you suggested and imported all my stuff into a new 2012 database. But, the problem was the same.

2010

So, I went back to Access 2012

2010

------Well, it's the next morning now and I've solved the problem. Operator Error!-----

This is the danger of trying to modify an application originally written years ago. I forgot that I overrode the query behind the form with a me.recordsource sql statement but didn't include the new field in the query. So, no wonder it couldn't bind to the form.

My apologies to Access 2010. It works great.

[RuralGuy]

Thanks for posting back with your success and solution.