Controls showing #name 1

[THWatson]

Using Access 2016
Church donations database

On a form where donations are entered, there are the following controls all in a straight line across.
Date Given (date format)
txtEnvNbr (everybody has a donation number that corresponds to their donations envelopes)
Local (format: $#, ##0.00; ($#,##0.00)
M and S (format: $#, ##0.00; ($#,##0.00)
Building (format: $#, ##0.00; ($#,##0.00)
Memorial (format: $#, ##0.00; ($#,##0.00)
Other (format: $#, ##0.00; ($#,##0.00)
txtEnvTotal Control Source =[Local]+[M and S]+[Building]+[Memorial]+[Other] (format: $#, ##0.00; ($#,##0.00)

When, on the form, the user makes the appropriate entries, then clicks on a new line to make another entry, every one of the above controls ends up showing #Name.
The odd thing is that the data entered sticks properly, even though the visual suggests otherwise.

This worked properly for years but now, for whatever reason, since moving to Access 2016 the result is as described.

Any suggestions?

Thanks.
Tom

 

[dhookom]

I would create another similar form with just the significant text boxes and see if the error still occurs. You should be able to simply copy and paste the controls to a new form and set the Record Source property of the form.

Are any of the controls null? Can we assume most of the text boxes have field names as the control sources?

What happens if you remove the control source of txtEnvTotal?

BTW: if these reflect your table structure then your table is not normalized since you are storing data in your column names.


Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks. Using your tips, I will have to paw through and see what I need to fix.
Tom

 

[THWatson]

Duane
First of all, the structures are normalized properly, according to the built-in Access analyzer.

No controls are Null. One is a date field, one is a field that relates to the donor's envelope number, the rest are Currency fields.

I created this quite a while ago while still using Access 2000. It still worked in Access 2010. Perhaps Access 2016 has become more fussy about control names.
In any event, some of those control names did match their underlying fields, so I added txt in front of them.
e.g. changed Local to txtLocal

I then changed all VBA code behind the form to correspond to the new control names, and it's working properly now.

Thanks.

Tom

 

[dhookom]

If you have field names that are fund names then you are storing data in field names which is not normalized regardless of what the analyzer suggests.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Okay. Thanks. Anyway, I fixed them.

 

[THWatson]

Oooooppps...I thought I had it fixed but not the case.

As described when I first made this post, it's a form where the user enters weekly donations data by Envelope #.

It's a Continuous form.

The Detail section has a row of controls across the page. I'll use the renamed controls.

[txtDateGiven] [txtEnvNbr] [txtLocal] [txtMandS] [txtBuilding] [txtMemorial] [txtOther]   [txtDayTotal]

The controls store data in tblNewGivings under the relevant field names underlying them.

The control source for [txtDayTotal] is

=[txtLocal]+[txtMandS]+[txtBuilding]+[txtMemorial]+[txtOther]

I thought I had it fixed by making sure that the control names and the underlying field names aren't the same. But...
HERE'S THE STRANGE THING...
One time I enter the form, plug in a line of data, move to a new line to make another entry, and everything works perfectly.
Next time I enter the form, do the same thing, and as soon as I move to a new line, the first 7 controls display #Name, the txtDayTotal displays #Type
Close the form, go back to the Main Menu, enter the form again, and works fine maybe, and the next time again these errors displayed.

The interesting thing is that the data is there, even though the errors showing in the controls would lead one to think otherwise.

Since [txtDayTotal] is a calculated control, I removed its control source, as suggested.
Makes no difference. Still this works one time and not the next.

I don't get why it's not consistently one or the other.

Tom

 

[dhookom]

I would go back to using the field names rather than the control names
=[Local]+[MandS]+[Building]+[Memorial]+[Other]
I would also make sure the default value of all of these columns is set to 0.

You could also create this column in the record source query of the form.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Access doesn't like that. Reverting to the control names in the [txtDayTotal} results in an error:

"No entry is made in any of the amount fields. The record cannot be saved with a $0 total.

I checked back in tblNewGivings and the default value for each of those fields is 0.

In the form itself, each control has a value of $0.00 unless modified by the user.

I'll have a look at creating the column in the record source query, and see what happens there.
Tom

 

[dhookom]

Do you have a validation rule on the table?

I just noticed I use [MandS] rather than your field name that contains spaces. This produced the #Name error. When I corrected the field names, it worked as expected. Have you checked your field names?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Well, this is like pushing a string uphill...and there's a good stiff headwind here in my neck of the woods today.

I was just about to post and say I had things fixed. I changed all the control names and put txt in front of them, made a query and put the field that calculates the total for the row in the query, fixed all the code to change the necessary stuff in the VBA behind the form, ran it a couple of times with no more errors.

Then I closed the form and reopened it...Same old errors.
Close it and go back to the Main Menu, then reopen it again, works like a charm.
Next time, the errors again.

It's this inconsistency that's driving me bananas.

There are two fields back in tblNewGivings that have spaces. They are {M and S] and {Date Given}
If I'm sure that removing the spaces will do the trick, I'm happy to do that...but it means changing a whole number of queries and reports, not to mention a couple of other tables (e.g. NewGivingsArchive)

Is there any way to make all those changes in one fell swoop?

Tom

 

[THWatson]

Maybe I can do it with NameAutoCorrect. I just have to find it in Access 2016.

 

[dhookom]

Changing the field names won't fix anything. I would change the table structure before messing with field names. If you use spaces you need to wrap the names in []s.

Are you wrapping field names in "{}" or are these typos?

Consider changing the control source of txtDayTotal to a single field and running it for a while. Then add another field and another.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Those { } are typos. Working too quickly.

Still got work to do. Thanks.

 

[THWatson]

Duane
I finally got this fixed.

As I have said, what really had me puzzled was the intermittent behaviour, where one time the controls showed the #Name error and another time not.

I'm not totally clear why that was happening, but I have it working properly now.

Thanks for all your patient help.

Tom