Macro for subform

  • Thread starter Thread starter Eric Plante
  • Start date Start date
E

Eric Plante

I have a FormA that contains a FormB that is supposed to call a FormC by
double clicking on an ItemX in FormB.

When I open FormB directly, it works fine but when I do the same thing in
FormA, I get the prompt dialog box instead.

The condition of the macro is:
[key of FormC] = [Forms]![FormB]![ItemX]

The spelling is correct(or else it wouldn't work when executed in FormB)

What am I doing wrong?
Thanks
 
When a form is serving as the subform for another form, you cannot reference
the form directly, because it's not open as a form. It exists as part of the
other form. You therefore use a reference similar to this to get to a
control on a subform in a mainform:

Forms!MainFormName!SubformControlName!ControlName

where MainFormName is the name of the main form, SubformControlName is the
name of the subform control (on the main form) that actually holds the
subform (note that the name of this subform control may or may not be the
same name as the name of the form that is serving as the subform -- the
subform control's SourceObject), and ControlName is the name of the control
on the subform.

Therefore, the macro must be written differently for when the form is open
on its own, and for when the form is a subform.

Using VBA code gets you around this problem because you can put code in the
form B (your example) that refers to itself using the Me. object, which
avoids this problem entirely.
 
Eric,

Do you mean that FormB is a subform on FormA? If so, FormB is not
open in fact, it is held within a control (i.e. a subform control) on
FormA, so Access is not able to evaluate the expression
[Forms]![FormB]![ItemX]. Instead, you need to use the full reference
to ItemX, i.e. [Forms]![FormA]![FormB]![ItemX]
or, as some would insist, [Forms]![FormA]![FormB].[Form]![ItemX]
Thes examples assume that the name of the subform control on FormA is
the same as the name of the form in contains, i.e. FormB, but this is
not necessarily the case.

- Steve Schapel, Microsoft Access MVP
 
I forgot to say that I'm using Access97 but I tried what you suggested and I
still get the prompt. I also cannot make that macro by simply clicking on
the form folders and items because the subform control doesn't appear in
FormA folder, only the title of the subform control.

The name of the subform control is the same as the name of FormB.

Steve Schapel said:
Eric,

Do you mean that FormB is a subform on FormA? If so, FormB is not
open in fact, it is held within a control (i.e. a subform control) on
FormA, so Access is not able to evaluate the expression
[Forms]![FormB]![ItemX]. Instead, you need to use the full reference
to ItemX, i.e. [Forms]![FormA]![FormB]![ItemX]
or, as some would insist, [Forms]![FormA]![FormB].[Form]![ItemX]
Thes examples assume that the name of the subform control on FormA is
the same as the name of the form in contains, i.e. FormB, but this is
not necessarily the case.

- Steve Schapel, Microsoft Access MVP


I have a FormA that contains a FormB that is supposed to call a FormC by
double clicking on an ItemX in FormB.

When I open FormB directly, it works fine but when I do the same thing in
FormA, I get the prompt dialog box instead.

The condition of the macro is:
[key of FormC] = [Forms]![FormB]![ItemX]

The spelling is correct(or else it wouldn't work when executed in FormB)

What am I doing wrong?
Thanks
Click to expand...
Click to expand...
 
Eric,

Can you please post back with the full details of the macro, and the
exact wording of the "prompt".

- Steve Schapel, Microsoft Access MVP
 
The wording of the prompt is simply the Key of the Form it is supposed to
open. FormC has KeyA and in the where section of the macro I wrote [KeyA] =
[Forms]![FormA]![FormB]![ItemX]. The prompt only showed a text box titled
KeyA but works when I type ItemX manualy in it.

The form name is FormC, I set the read only flag and it should show a form.
There is nothing in the filter. The mode is standard.

Previously, it was [KeyA] = [Forms]![FormB]![ItemX] but worked only when
opening FormB, it showed the prompt when opening FormA but still worked if I
typed ItemX manualy.

I tried [KeyA] = [Forms]![FormsA]![FormsB].[Forms]![ItemX] but the prompt
was shown as well.

Anything else that you need?
Thanks for your help
Eric
 
I am sorry, Eric, I can't think what the problem could be. As far as
I can see from what you have told us, it should work. If you like, I
would be willing to take a look at the database and see if I can
figure it out. If so, email it to me... just remove the .ns at the
end of my return email address.

- Steve Schapel, Microsoft Access MVP
 
Eric,

I have looked at the database you sent. The probelm is with a
spelling/?translation error. The Where condition of the OpenForm
action in your Spell1 macro, to open form Spell, is...
[Name]=[Forms]![Spell Sheet]![Spell Lists]![Lvl 1]
However, form Spell does not contain a control called Name. The macro
argument should read...
[Nom]=[Forms]![Spell Sheet]![Spell Lists]![Lvl 1]

In any case, please note that it is not a good idea to use the word
Name as the name of a field or control or database object, as this
word has a special meaning to Access, and can lead to problems under
some circumstances.

Another lesson here is to use the actual names of database elements
when asking for help. In your earlier communications you used FormB,
ItemX etc, but these are not the names of items in your database.

- Steve Schapel, Microsoft Access MVP


I am sorry, Eric, I can't think what the problem could be. As far as
I can see from what you have told us, it should work. If you like, I
would be willing to take a look at the database and see if I can
figure it out. If so, email it to me... just remove the .ns at the
end of my return email address.

- Steve Schapel, Microsoft Access MVP


The wording of the prompt is simply the Key of the Form it is supposed to
open. FormC has KeyA and in the where section of the macro I wrote [KeyA] =
[Forms]![FormA]![FormB]![ItemX]. The prompt only showed a text box titled
KeyA but works when I type ItemX manualy in it.

The form name is FormC, I set the read only flag and it should show a form.
There is nothing in the filter. The mode is standard.

Previously, it was [KeyA] = [Forms]![FormB]![ItemX] but worked only when
opening FormB, it showed the prompt when opening FormA but still worked if I
typed ItemX manualy.

I tried [KeyA] = [Forms]![FormsA]![FormsB].[Forms]![ItemX] but the prompt
was shown as well.

Anything else that you need?
Thanks for your help
Eric
Click to expand...
Click to expand...
 
Steve Schapel said:
Eric,

I have looked at the database you sent. The probelm is with a
spelling/?translation error. The Where condition of the OpenForm
action in your Spell1 macro, to open form Spell, is...
[Name]=[Forms]![Spell Sheet]![Spell Lists]![Lvl 1]
However, form Spell does not contain a control called Name. The macro
argument should read...
[Nom]=[Forms]![Spell Sheet]![Spell Lists]![Lvl 1]
Click to expand...
Translation error. Nom must have became Name in your Access 97 in some
places and must have remained Nom elsewhere because in mine, the spelling
looks correct. I noticed that Name was automaticaly translated into Nom in
mine at some places.
In any case, please note that it is not a good idea to use the word
Name as the name of a field or control or database object, as this
word has a special meaning to Access, and can lead to problems under
some circumstances.
Click to expand...
Thanks, that's good to know.
Another lesson here is to use the actual names of database elements
when asking for help. In your earlier communications you used FormB,
ItemX etc, but these are not the names of items in your database.
Click to expand...
Ok. I chose a more generic description to make it easier to understand but
in this case, it didn't help indeed.

Thanks for your help, it works now.
 
Can there be other sources of the problem? While it worked for my Spell
Sheet form, I have another table that use a subform that should open a form
but even though that this time there is no translation, spelling or syntax
error(I think I checked for those at least 10 times), it still gives me the
prompt.

In the Monstre table I now have a Feat List subform, containing the Feat
component, that should opens the Feats form that contains the NomF component
as its key.

The macro when double clicking in the subform's Feat component is [NomF] =
[Forms]![Monstre]![Feat List]![Feat]

However the way I look at it, I just don't see the bug, it should work the
same way it did in Spell Sheet but it doesn't.

Thanks

Steve Schapel said:
Eric,

I have looked at the database you sent. The probelm is with a
spelling/?translation error. The Where condition of the OpenForm
action in your Spell1 macro, to open form Spell, is...
[Name]=[Forms]![Spell Sheet]![Spell Lists]![Lvl 1]
However, form Spell does not contain a control called Name. The macro
argument should read...
[Nom]=[Forms]![Spell Sheet]![Spell Lists]![Lvl 1]

In any case, please note that it is not a good idea to use the word
Name as the name of a field or control or database object, as this
word has a special meaning to Access, and can lead to problems under
some circumstances.

Another lesson here is to use the actual names of database elements
when asking for help. In your earlier communications you used FormB,
ItemX etc, but these are not the names of items in your database.

- Steve Schapel, Microsoft Access MVP


I am sorry, Eric, I can't think what the problem could be. As far as
I can see from what you have told us, it should work. If you like, I
would be willing to take a look at the database and see if I can
figure it out. If so, email it to me... just remove the .ns at the
end of my return email address.

- Steve Schapel, Microsoft Access MVP


The wording of the prompt is simply the Key of the Form it is supposed to
open. FormC has KeyA and in the where section of the macro I wrote [KeyA] =
[Forms]![FormA]![FormB]![ItemX]. The prompt only showed a text box titled
KeyA but works when I type ItemX manualy in it.

The form name is FormC, I set the read only flag and it should show a form.
There is nothing in the filter. The mode is standard.

Previously, it was [KeyA] = [Forms]![FormB]![ItemX] but worked only when
opening FormB, it showed the prompt when opening FormA but still worked if I
typed ItemX manualy.

I tried [KeyA] = [Forms]![FormsA]![FormsB].[Forms]![ItemX] but the prompt
was shown as well.

Anything else that you need?
Thanks for your help
Eric
Click to expand...
Click to expand...
Click to expand...
 
Eric,

As far as I know, the only reason you will get a parameter prompt for
the value of NomF is if Access can't find a field called NomF in the
recordsource of the Feats form. If you can't figure it out, I guess
you can send it on over again, and I'll have another looksie.

- Steve Schapel, Microsoft Access MVP
 
I'll try to avoid sending it to you again but I might take up your offer
before I scrap my computer out of frustration :)

By the way the prompt says '[Forms]![Monstre]![Feat List]![Feat] not [NomF]

Steve Schapel said:
Eric,

As far as I know, the only reason you will get a parameter prompt for
the value of NomF is if Access can't find a field called NomF in the
recordsource of the Feats form. If you can't figure it out, I guess
you can send it on over again, and I'll have another looksie.

- Steve Schapel, Microsoft Access MVP


Can there be other sources of the problem? While it worked for my Spell
Sheet form, I have another table that use a subform that should open a form
but even though that this time there is no translation, spelling or syntax
error(I think I checked for those at least 10 times), it still gives me the
prompt.

In the Monstre table I now have a Feat List subform, containing the Feat
component, that should opens the Feats form that contains the NomF component
as its key.

The macro when double clicking in the subform's Feat component is [NomF] =
[Forms]![Monstre]![Feat List]![Feat]

However the way I look at it, I just don't see the bug, it should work the
same way it did in Spell Sheet but it doesn't.

Thanks
Click to expand...
Click to expand...
 
Eric,

before I scrap my computer out of frustration :)
Click to expand...

I am heartened by the attitude you are taking here. This is one of
the essential steps in the process of becoming an expert!
By the way the prompt says '[Forms]![Monstre]![Feat List]![Feat] not [NomF]
Click to expand...

OK, so it's the calling form that is the problem. The expression you
use here must refer to the name of the subform control on the main
form, not to the name of the form which is the source object of the
subform. While these are often the same, it is not necessarily so.
So one thing to check is whether the name of the *subform control* on
the Monstre form is Feat List.

- Steve Schapel, Microsoft Access MVP
 
Back
Top