set value in subform

I want to use the set value macro to set a control in a subform based on a
control in another form.

I have three forms: FormA, FormB, FormC. FormC is a subform within FormB.
What I want is to be able to enter a value in FormA and then have FormC
automatically updated.

Currently, if I open FormA and FormC I am able to use the SetValue macro to
do this.

However, if I open FormA and FormB (recall that FormC is a subform within
FormB) and try to run the macro, Access gives me an error. I'm assuming that
it is because Access does not think FormC is open (even though it is open
indirectly as a subform within FormB).

Any advice? Thank you in advance.

It may be a problem with the syntax you are using to refer to form C when it
is a subform. To refer to a control on a subform use the following syntax;

Forms!MainFormName![SubformControlName].Form![ControlName]

The [SubformControlName] is the name of the control that holds the subform,
which may or may not be the same as the name of the subform itself.


HTH
--
_________

Sean Bailey


"cuyeda" wrote:

I want to use the set value macro to set a control in a subform based on a
control in another form.

I have three forms: FormA, FormB, FormC. FormC is a subform within FormB.
What I want is to be able to enter a value in FormA and then have FormC
automatically updated.

Currently, if I open FormA and FormC I am able to use the SetValue macro to
do this.

However, if I open FormA and FormB (recall that FormC is a subform within
FormB) and try to run the macro, Access gives me an error. I'm assuming that
it is because Access does not think FormC is open (even though it is open
indirectly as a subform within FormB).

Any advice? Thank you in advance.

As Beetle suggested, the syntax is your most likely issue. However, you
have a larger issue and that is - precisely which record of form C do you
think you are updating? I suspect a flaw in your logic and possibly your
table design. If you'll tell us more about what you are trying to do, we
can give you some advice.

"Beetle" wrote in message
...
It may be a problem with the syntax you are using to refer to form C when
it
is a subform. To refer to a control on a subform use the following syntax;

Forms!MainFormName![SubformControlName].Form![ControlName]

The [SubformControlName] is the name of the control that holds the
subform,
which may or may not be the same as the name of the subform itself.


HTH
--
_________

Sean Bailey


"cuyeda" wrote:

I want to use the set value macro to set a control in a subform based on
a
control in another form.

I have three forms: FormA, FormB, FormC. FormC is a subform within
FormB.
What I want is to be able to enter a value in FormA and then have FormC
automatically updated.

Currently, if I open FormA and FormC I am able to use the SetValue macro
to
do this.

However, if I open FormA and FormB (recall that FormC is a subform within
FormB) and try to run the macro, Access gives me an error. I'm assuming
that
it is because Access does not think FormC is open (even though it is open
indirectly as a subform within FormB).

Any advice? Thank you in advance.

Thanks Beetle - you are correct it was a syntax issue.

Pat, here's some more on what I'm doing. I would not be surprised if I had
a flawed design or logic and would appreciate any expert advice.

TABLE DESIGN:
I have four tables. Table 1 records the scientific name of a species, Table
2 records the "population" of the species. Table 1 and 2 are linked in a one
to many relationship as a species can have multiple populations. Table 3
records "petitions". Because a petition can have multiple species and a
species can be on multiple petitions Table 2 and 3 are related with a join
table (Table 4).

WHAT I'M DOING:
I'm developing a form so that a user can enter the species/populations that
were included in a petition

HOW I'M DOING IT:
I use three forms. FormA is for entering information regarding the petition
that is NOT related to species (eg. the date the petition was received).
FormB is a subform within FormA. It has two combo boxes. One for selecting
the species, the other for the population. Then the form displays the unique
ID associated with that species/population.
FormC is also a subform within FormA. The form allows the user to enter the
species ID of the petitioned species (which was already found in formB) and
then other data associated with that petitioned species. Since a petition
can have multiple species the form allows the user to enter multiple species.


Is there a better or more efficient way of doing this? This is the best I
could do with my current knowledge of Access. Thanks.

"Pat Hartman" wrote:

As Beetle suggested, the syntax is your most likely issue. However, you
have a larger issue and that is - precisely which record of form C do you
think you are updating? I suspect a flaw in your logic and possibly your
table design. If you'll tell us more about what you are trying to do, we
can give you some advice.

"Beetle" wrote in message
...
It may be a problem with the syntax you are using to refer to form C when
it
is a subform. To refer to a control on a subform use the following syntax;

Forms!MainFormName![SubformControlName].Form![ControlName]

The [SubformControlName] is the name of the control that holds the
subform,
which may or may not be the same as the name of the subform itself.


HTH
--
_________

Sean Bailey


"cuyeda" wrote:

I want to use the set value macro to set a control in a subform based on
a
control in another form.

I have three forms: FormA, FormB, FormC. FormC is a subform within
FormB.
What I want is to be able to enter a value in FormA and then have FormC
automatically updated.

Currently, if I open FormA and FormC I am able to use the SetValue macro
to
do this.

However, if I open FormA and FormB (recall that FormC is a subform within
FormB) and try to run the macro, Access gives me an error. I'm assuming
that
it is because Access does not think FormC is open (even though it is open
indirectly as a subform within FormB).

Any advice? Thank you in advance.

Well, here are some thoughts (FWIW)

First, do you petition a species or do you petition a population? If you
petition a species, do you automatically petition every population within
that species? What I'm getting at is the actual nature of the many-to-many
relationship. Is it between the petition and the species or the petition and
the population?

Second, if all you're doing with FormB is selecting a species/population,
I'm not even sure it's necessary. Since you're selecting the species again in
FormC, any information related to that species ID can be retrieved and
displayed on your form in unbound controls.

Third, FormC should be based on your junction table (if it's not already -
you don't specify in your post). Likewise, any fields that are related
*specifically* to that petition/species record should be in the junction
table.
--
_________

Sean Bailey


"cuyeda" wrote:

Thanks Beetle - you are correct it was a syntax issue.

Pat, here's some more on what I'm doing. I would not be surprised if I had
a flawed design or logic and would appreciate any expert advice.

TABLE DESIGN:
I have four tables. Table 1 records the scientific name of a species, Table
2 records the "population" of the species. Table 1 and 2 are linked in a one
to many relationship as a species can have multiple populations. Table 3
records "petitions". Because a petition can have multiple species and a
species can be on multiple petitions Table 2 and 3 are related with a join
table (Table 4).

WHAT I'M DOING:
I'm developing a form so that a user can enter the species/populations that
were included in a petition

HOW I'M DOING IT:
I use three forms. FormA is for entering information regarding the petition
that is NOT related to species (eg. the date the petition was received).
FormB is a subform within FormA. It has two combo boxes. One for selecting
the species, the other for the population. Then the form displays the unique
ID associated with that species/population.
FormC is also a subform within FormA. The form allows the user to enter the
species ID of the petitioned species (which was already found in formB) and
then other data associated with that petitioned species. Since a petition
can have multiple species the form allows the user to enter multiple species.


Is there a better or more efficient way of doing this? This is the best I
could do with my current knowledge of Access. Thanks.

"Pat Hartman" wrote:

As Beetle suggested, the syntax is your most likely issue. However, you
have a larger issue and that is - precisely which record of form C do you
think you are updating? I suspect a flaw in your logic and possibly your
table design. If you'll tell us more about what you are trying to do, we
can give you some advice.

"Beetle" wrote in message
...
It may be a problem with the syntax you are using to refer to form C when
it
is a subform. To refer to a control on a subform use the following syntax;

Forms!MainFormName![SubformControlName].Form![ControlName]

The [SubformControlName] is the name of the control that holds the
subform,
which may or may not be the same as the name of the subform itself.


HTH
--
_________

Sean Bailey


"cuyeda" wrote:

I want to use the set value macro to set a control in a subform based on
a
control in another form.

I have three forms: FormA, FormB, FormC. FormC is a subform within
FormB.
What I want is to be able to enter a value in FormA and then have FormC
automatically updated.

Currently, if I open FormA and FormC I am able to use the SetValue macro
to
do this.

However, if I open FormA and FormB (recall that FormC is a subform within
FormB) and try to run the macro, Access gives me an error. I'm assuming
that
it is because Access does not think FormC is open (even though it is open
indirectly as a subform within FormB).

Any advice? Thank you in advance.