Automatic editing - or a bug in Access?

  • Thread starter Thread starter Rolf Rosenquist
  • Start date Start date
R

Rolf Rosenquist

In a form with a subform, I want to show the sum of all calculations in the
subform. I have read in the Access 2002 Bible on page 859 how to do it. The
sum control in the form should have the Control Source like this:
=[Forms]![Form name]![Sub form name].Forms![Field name in subform]

When I write this into the properties, Access automaticly corrects the Forms
after the dot to [Forms] e.g. with brackets. Now you may think that it is
correct to do, but then the function does not workat all. The control shows
#Name? instead of the calculated value.

With the book there were also all example files on a CD and I tested it. The
sum function works really and if I go to the properties, the last Form is
showed there without the brackets. But if I change any letter and
immediately changes it back again, Acces will automatic edit the statement
to the brackets. And the function does not any longer work. I had to install
it once again from the CD to get it working.

Strange thing... Could it be fixed? Does anyone have a clue?
/ Rolf
 
Rolf Rosenquist said:
In a form with a subform, I want to show the sum of all calculations
in the subform. I have read in the Access 2002 Bible on page 859 how
to do it. The sum control in the form should have the Control Source
like this: =[Forms]![Form name]![Sub form name].Forms![Field name in
subform]
Click to expand...

No, that is *not* correct. Either it was a typo in the book, or an
error on your form, or you made the mistake in transcribing it for your
post to the newsgroup. The controlsource expression should be

=[Forms]![Form name]![Sub form name].Form![Field name in subform]

or even this:

=[Sub form name].Form![Field name in subform]

Since the expression is there on the main form containing the subform,
you shouldn't have to include the [Forms]![Form name] qualifier. In
either case, note that the next-to-last qualifier is ".Form", not
".Forms".
When I write this into the properties, Access automaticly corrects
the Forms after the dot to [Forms] e.g. with brackets. Now you may
think that it is correct to do, but then the function does not workat
all. The control shows #Name? instead of the calculated value.
Click to expand...

The insertion of the brackets is not a problem, because Access just does
that automatically in case whatever you typed might contain a space or
an invalid character. That's not the source of your problem. But if
you're putting in "Forms" there where it should be "Form", that will
certainly give you an error.

Of course, there are also other errors you could have that would make
this fail, but let's eliminate this one first.
With the book there were also all example files on a CD and I tested
it. The sum function works really and if I go to the properties, the
last Form is showed there without the brackets. But if I change any
letter and immediately changes it back again, Acces will automatic
edit the statement to the brackets. And the function does not any
longer work. I had to install it once again from the CD to get it
working.

Strange thing... Could it be fixed? Does anyone have a clue?
Click to expand...

If all you did was replace "Form" with "Form" in the example, I'm at a
loss as to why it would fail. But if you changed "Form" to "Forms", it
will certainly fail.
 
Have tried it again and in the working program from the CD I have:
=[Formulär]![Adding Visit Details]![Data for Subform
Example].Formulär![Total Sum]

When I go to the edit window and just copy it to be able to paste in this
message I get:
=[Formulär]![Adding Visit Details]![Data for Subform
Example].[Formulär]![Total Sum]

- and the control does not work anymore.

I also tried to change Formulär to the english versions Forms and Form, but
got Formulärs every time.
This way I see that Access changes it automatic for the worse.

Note that [Formulär] is the swedish for [Form] (and [Forms]).
/ Rolf



Dirk Goldgar said:
Rolf Rosenquist said:
In a form with a subform, I want to show the sum of all calculations
in the subform. I have read in the Access 2002 Bible on page 859 how
to do it. The sum control in the form should have the Control Source
like this: =[Forms]![Form name]![Sub form name].Forms![Field name in
subform]
Click to expand...

No, that is *not* correct. Either it was a typo in the book, or an
error on your form, or you made the mistake in transcribing it for your
post to the newsgroup. The controlsource expression should be

=[Forms]![Form name]![Sub form name].Form![Field name in subform]

or even this:

=[Sub form name].Form![Field name in subform]

Since the expression is there on the main form containing the subform,
you shouldn't have to include the [Forms]![Form name] qualifier. In
either case, note that the next-to-last qualifier is ".Form", not
".Forms".
When I write this into the properties, Access automaticly corrects
the Forms after the dot to [Forms] e.g. with brackets. Now you may
think that it is correct to do, but then the function does not workat
all. The control shows #Name? instead of the calculated value.
Click to expand...

The insertion of the brackets is not a problem, because Access just does
that automatically in case whatever you typed might contain a space or
an invalid character. That's not the source of your problem. But if
you're putting in "Forms" there where it should be "Form", that will
certainly give you an error.

Of course, there are also other errors you could have that would make
this fail, but let's eliminate this one first.
With the book there were also all example files on a CD and I tested
it. The sum function works really and if I go to the properties, the
last Form is showed there without the brackets. But if I change any
letter and immediately changes it back again, Acces will automatic
edit the statement to the brackets. And the function does not any
longer work. I had to install it once again from the CD to get it
working.

Strange thing... Could it be fixed? Does anyone have a clue?
Click to expand...

If all you did was replace "Form" with "Form" in the example, I'm at a
loss as to why it would fail. But if you changed "Form" to "Forms", it
will certainly fail.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Rolf Rosenquist said:
Have tried it again and in the working program from the CD I have:
=[Formulär]![Adding Visit Details]![Data for Subform
Example].Formulär![Total Sum]

When I go to the edit window and just copy it to be able to paste in
this message I get:
=[Formulär]![Adding Visit Details]![Data for Subform
Example].[Formulär]![Total Sum]

- and the control does not work anymore.

I also tried to change Formulär to the english versions Forms and
Form, but got Formulärs every time.
This way I see that Access changes it automatic for the worse.

Note that [Formulär] is the swedish for [Form] (and [Forms]).
/ Rolf
Click to expand...

You mean that the word "Formulär" is equivalent to both English "Form"
*and* "Forms"? I wonder if what's going on is the result of a failure
of the language-translation feature, such that "Formulär" is being
misinterpreted as "Forms" instead of "Form". That's just speculation,
though.

When you tried typing the ControlSource using "Forms" and "Form" instead
of "Formulär", and it changed them both to ""Formulär"", did you check
to see if that controlsource worked?

Is it true that "Data for Subform Example" is the name of the subform
control (on the main form) that is displaying the form object from which
you want to pick up the value of the control named "Total Sum"?

What happens if you do the following:

1. Open the form in design view.

2. Press Ctrl+G to open the Immediate Window.

3. In the Immediate Window,. enter a line like the following (with some
adjustments as I'll explain):

Forms![Adding Visit Details]!txtTotal.ControlSource =
"=[Data for Subform Example].Form![Total Sum]"

The above should be all on one line, and the word "txtTotal" should be
the name of the control on the main form that is supposed to display the
total from the subform.

4. Save and close the form.

5. Open it and see if it works.

My idea behind this suggestion is that, as I understand it, non-English
versions of Access are supposed to also understand English-language
keywords, so I'm hoping that if you force an English-language
controlsource in there, it may work.
 
Your idea seemed very good, but I was dissapointed to see that I got the
swedish word Formulär after running it in the immediate window. I copied it
and paste the result here.

=[OrderSpec].Formulär![Totalen]

At least it was possible to get it without the brackets. That was not
possible for me earlier. Still got #Name? in the main form.



/ Rolf
NB OrderSpec is the subform and Totalen is the calculated field in the
subform in the footer.
 
Rolf Rosenquist said:
Your idea seemed very good, but I was dissapointed to see that I got
the swedish word Formulär after running it in the immediate window. I
copied it and paste the result here.

=[OrderSpec].Formulär![Totalen]

At least it was possible to get it without the brackets. That was not
possible for me earlier. Still got #Name? in the main form.
Click to expand...

Interesting. And you are sure that "OrderSpec" is really the name of
the subform control on the main form, not just the name of the form
object that control is displaying?

Would you be interested in e-mailing me a copy of your database to look
at? If your problem is really due to the language translation, that may
not help, but if there is some other problem I may be able to spot it.
If you'd like to send it to me, please make a cut-down copy of the real
database, containing only the elements necessary to demonstrate the
problem. Then compact it and zip it to less than 1MB in size
(preferably much smaller). Send it along, and I'll have a look at it,
time permitting. You can send it to the address derived by removing NO
SPAM from the reply address of this message.
 
Interesting. And you are sure that "OrderSpec" is really the name of
the subform control on the main form, not just the name of the form
object that control is displaying?
Click to expand...
No, OrderSpec is the subform name
Order is is the main form
Totalen is the control name in the subform

Have I messed up something then...?
/ Rolf
 
Rolf Rosenquist said:
No, OrderSpec is the subform name
Order is is the main form
Totalen is the control name in the subform

Have I messed up something then...?
Click to expand...

Maybe. Remember there are two objects involved in displaying a subform
on a main form, not counting the main form itself. There is the form
object that is to be displayed in the subform "window". This is the
object that appears on the Forms tab of the database window. And then
there is the control object, of type subform, that is placed on the main
form. This subform control object provides the window in which the
subform is viewed.

The name of the subform's form object and the name of the subform
control may be the same, or they may be different. When you want to
build a reference to a control on the subform, you have to use the name
of the subform *control*, not the name of the form object, to get to it.
So if you wrote, in a controlsource on the main form,

=[OrderSpec].Formulär![Totalen]

"OrderSpec" must be the name of the subform control on the main form, no
matter what is the name of the form object displayed in that control.
It's entirely possible that the subform control is actually named
"Subform1" or something like that, if Access named it. This is
something you should check.
 
Now it WORKS!
As you said, I had another name, generated by Access itself.
I am impressed and very greatful that you found it.

As a curiosity I tested to edit as I did first. Then I got the brackets as
[Formulär] - and guess what, - then the function did not work any longer. I
did not save it and you learned me how to put it there without the brackets.

Great thanks for your help!


/ Rolf
 
Rolf Rosenquist said:
Now it WORKS!
Click to expand...

Hurrah! I'm very glad.
As you said, I had another name, generated by Access itself.
I am impressed and very greatful that you found it.
Click to expand...

I've seen that sort of problem before.
As a curiosity I tested to edit as I did first. Then I got the
brackets as [Formulär] - and guess what, - then the function did not
work any longer. I did not save it and you learned me how to put it
there without the brackets.
Click to expand...

Now *that* is very interesting, and probably is a bug. You should not
have to go to such lengths to get your subform references to work
correctly. You may want to report this to Microsoft Product Support
Services.

If it's not a bug in the Swedish-language translation feature, the only
other thing I can think of is that it may be -- as many other odd
behaviors relative to names are -- a result of the buggy Name
AutoCorrect feature. If you have Name AutoCorrect enabled for your
database (Tools -> Options -> General tab, "Track name AutoCorrect" and
"Perform name AutoCorrect"), try disabling it and see whether you can
edit the controlsource normally and still have it work. But that's just
a possibility; I don't really know if that's why it's misbehaving this
way.
Great thanks for your help!
Click to expand...

You are most welcome.
 
If it's not a bug in the Swedish-language translation feature, the only
other thing I can think of is that it may be -- as many other odd
behaviors relative to names are -- a result of the buggy Name
AutoCorrect feature. If you have Name AutoCorrect enabled for your
database (Tools -> Options -> General tab, "Track name AutoCorrect" and
"Perform name AutoCorrect"), try disabling it and see whether you can
edit the controlsource normally and still have it work. But that's just
a possibility; I don't really know if that's why it's misbehaving this
way.
Click to expand...

Tried that now, but got the same result. So I guess it's a form of a bug.

Anyway, I'm quite reliefed now after our interesting and fruitful session!

/ Rolf
 
Back
Top