#Name? Suddenly Showing in ALL Calculated Fields

[highflight1985]

I've been working on a multi-item form. I've put several hours of work into it. Everything was working just fine until suddenly, after minimizing the database to go work on an expression in notepad, I come back and find that every single one of my calculated unbound fields is showing "#Name?".

I've Googled this extensively but cannot come up with anyone else having had the same problem where ALL of their fields suddenly started showing "#Name?". Of the results I did find, the suggestion was usually to check the naming of my fields for any typos or names used more than once.

Even though I was doubtful that this was my issue due to all of the fields doing this suddenly all at once, I still checked and cannot seem to find any such issue. I've tried "undoing" until it wouldn't let me "undo" any more. I tried closing out the Access and reopening just to see if it was a fluke. (It wasn't...)

This one has me :banghead:....

Suggestions?

 

[Galaxiom]

Compact and Repair.

If that doesn't fix it, import all objects into a new database.

 

[boblarson]

If any control has the same name as a field used in any formula on the form, or report, it will do this.

 

[highflight1985]

Sorry guys, that didn't help.

I'm fairly confident I've not named anything improperly and the compact and repair/export idea didn't work.

I decided to change the way my table was made. I was just going to calculate the data for display purposes only but that was proving to be too :banghead:.

I decided, based on reading an article, that I'd go ahead and save the calculated values into the database using calculated fields. With this in place, I was able to sum, avg, min, and max a column/field of data in the footer from layout view. I decided since Design view is a little easier to work with when copying and pasting (for me, anyway), I'd go there and copy->paste the textboxes I already made and just change their data source to the right column. The idea being it'd save me time in not having to write Sum(), Avg(), Min(), and Max() another three dozen times.

When I left design view and went to look at it in form view, ALL of my Sums, Avgs, Mins, and Maxs are showing "#Error", INCLUDING the one I got to work while in Layout view. So, it went from working to not working, and I didn't do anything to it!

I then tried getting the same information via a summary query of the fields I wanted. The query works, but when I try to set the textboxes control source to the query, I get "#Name?" What gives???

I'm seriously :banghead:!

I've attached the database for you guys to look at. I don't know what else to do. This has to be a simple stupid mistake on my part.

 

[boblarson]

You stated:

but when I try to set the textboxes control source to the query,

Sorry to ask this but I can't open the database until I get home because I don't have 2010 and it won't open in 2007, show an example of the control source you are using.

 

[highflight1985]

I am away from my laptop at the moment. Replying via my phone.

I'm not sure what you'd like me to show. The text that's written into the textbox control source property us written something like this:

[CreditSpreadsSummation]![Sum of CreditReceived]

That's the best I can recall from memory until I can get back to my laptop...

 

[boblarson]

That would explain it. You can't refer to something like that in your control sources. If it is a field in the underlying form's record source then you would just bind it to that field. If it is not included in the form's (or report's) record source you would need to use a DLookup to return the value. But you can't just refer to a table or query by using TableOrQueryNameHere!FieldNameHere.

 

[highflight1985]

Bob,

I initially did link it to the field. It worked in layout view. I switched to design view, added the rest of the fields I wanted summations for, and then came back to layout/form view and everything was fubar.

This works in a textbox in the detail section: =[MaxRisk]

This does NOT work in a textbox in the footer section: =Sum([MaxRisk])
Nor does this: =Sum([CreditSpreadsSummation]![MaxRisk]); which I understand wouldn't based on what you said. (I tried this based on a suggestion from another thread located on this site.)

MaxRisk is a column in the table [Credit Spreads]. [Credit Spreads] is the record source for the form, but is displayed as Credit Spreads - no brackets.

Would you please take a look at the database when you get home and "fix" a few columns in the "Credit Spreads - Multi-Item" form and then reupload? I'll take a look at what you have that's working and compare it to what I did that isn't and learn from that.

Thanks!

 

[boblarson]

added the rest of the fields I wanted summations for, and then came back to layout/form view and everything was fubar.

Yes, that totally is why I am saying that you cannot have any control on the form which has the same name as any field in any calculation that occurs on the form. So, when you drag and drop onto the form, the default for Access is to use the field name for the control name. And so if you had a calculated control on your form which referred to the [Quantity] field for example and then you dragged the field quantity to the form it would then render your calculation formula to be returning #Name because it can't figure out what you want because there is both a field named quantity and a control named quantity. So it returns the error. That is why it is helpful to rename your controls to something other than the field name (Like txtQuantity) so that you don't have any that are the same as those fields, or controls, used in any calculations.

So, my suggestion as it will still be about 4 hours or so before I can go look, is to check that out.

 

[highflight1985]

Actually, none of my controls are named the same as a field in the table. They are all named either txtInsertFieldNameHere, or the default from a copy and paste action which seems to be Textnnn, where nnn is some incrementing 3-digit number.

 

[boblarson]

Okay, a few things.

1. You have fields in your query which you weren't using but you were trying to do calculations on the form instead. I changed those to bind them to the fields.

2. You had one where it should have read something like =Max([PercentAwayATM]) But you had =Max([Percent]) in it instead which wasn't a field in the record source. (probably the main problem).

3. And then on some control sources you had =Sum([CreditSpreadsSummation]![FieldNameHere]) when it should not have included the [CreditSpreadsSummation] part, just the field.

So take a look and see if you can see what I did. I did modify several control sources.

 

[highflight1985]

...I must be stupid. Your revision is working, but I still don't see why.

About #1) I did the query as a last resort before I responded here and uploaded the database just to see if I could get that method to work. I left the fields where I attempted the query version as they were out of laziness; why change them back to be bound to the field of the table when that wasn't working for me anyway? So, I didn't bother.

About #2) That was definitely a typo, but to which I hadn't caught yet because ALL of the summations were showing #Error.

About #3) Again, goes back to #1 above; it was my last attempt/effort to make it work before asking for help. Didn't bother changing it back.

So, I have to ask, when you opened my version, were you faced with "#Error" on all the summations in the bottom section (footer)? If not, then I'm even more confused. When you say, "I modified several control sources," did you modify all of the ones in the bottom? I can't see a difference between your file and mine in any of the control properties, and I'm comparing them side by side.

 

[boblarson]

Yes, when I opened it up I got all of the errors. And the thing is that many times it is due to ONE error on the form which then they all show up like that. So it is sometimes like trying to find a needle in a haystack.

 

[Galaxiom]

If any control has the same name as a field used in any formula on the form, or report, it will do this.

Could you explain precisely what you mean by this statement because it is certainly not consistent with my experience.

 

[aahmn]

This just happened to me also. Even the simplest calculations (writing a formatted name if not null) all became "#Name?." After redoing the textboxes, compacting and repairing, and restarting my computer, I finally just reselected the same query from the form's recordsource and it started working. I have no idea why or how - and don't know if it will happen again. Very strange...

I wanted to add that this was in Access 2010, and just happened suddenly. It was working one minute, the next it wasn't.