Changing Linked Form code

  • Thread starter Thread starter Noel
  • Start date Start date
N

Noel

Hi. Can anyone help with this. I have rearranged part of
my Access 2002 mdb and now a command button that I set up
on a Schools form to open up a linked Mentors form needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is based
on a table also called Mentors. Yes, I know this is bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that now the
records in table Mentors no longer contain the SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is imbedded in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor form
also has a button that opens the Schools form where the
SchoolID is the same. I need to change this buttons code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool subform.

I hope this makes sense. Thanks for the help. Noel
 
If I understand you correctly your subform which contains MentorID and
SchoolID is not directly on the form Mentor. When you create a subform to a
main form through the wizard you have two choices. Create a 'true' subform
or create a linked form. If you chose the latter, then you have to pass the
mentorID value to the linked form using VBA code with the OpenArgs function.
However, if you create a 'true' subform, where the subform resides directly
on the Mentor Form, you don't have to worry about passing the Mentor ID to
the subform form. It will be 'linked' automatically. As you change the Main
form data, so will the subform data.
 
Hi. Thanks for the reply. Perhaps I need to explain this
a bit further. I have one form called Schools and another
called Mentors. Mentors had a standard subform, linked to
the Mentors form by MentorID. The subforms Control Source
is tblMentorSBTSchool which contains, among other fields,
MentorID and SchoolID.

I want to create a new, or modify the existing, command
button on form Schools which opens the Mentors form to
show those Mentor records whose subform records have the
same SchoolID as the School form record.

So, say tblMentorSBTSchool has a record for MentorID 20
showing SchoolID 12. Now if I am in the School form and I
select the record for SchoolID 12, then click on the
command button, I want it to open the Mentors form at the
record for MentorID 20 because its subform record will be
the one showing the schoolname for SchoolID 12.

Is that any clearer? Thanks again for the help. Cheers,
Noel
-----Original Message-----
If I understand you correctly your subform which contains MentorID and
SchoolID is not directly on the form Mentor. When you create a subform to a
main form through the wizard you have two choices. Create a 'true' subform
or create a linked form. If you chose the latter, then you have to pass the
mentorID value to the linked form using VBA code with the OpenArgs function.
However, if you create a 'true' subform, where the subform resides directly
on the Mentor Form, you don't have to worry about passing the Mentor ID to
the subform form. It will be 'linked' automatically. As you change the Main
form data, so will the subform data.


Hi. Can anyone help with this. I have rearranged part of
my Access 2002 mdb and now a command button that I set up
on a Schools form to open up a linked Mentors form needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is based
on a table also called Mentors. Yes, I know this is bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that now the
records in table Mentors no longer contain the SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is imbedded in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor form
also has a button that opens the Schools form where the
SchoolID is the same. I need to change this buttons code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool subform.

I hope this makes sense. Thanks for the help. Noel
Click to expand...


.
Click to expand...
 
Perhaps like this:

DoCmd.OpenForm "Mentors",,,"MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE tblMentorSBTSchool.SchoolID=" & SchoolID & ")"

HTH
- Turtle

Noel said:
Hi. Thanks for the reply. Perhaps I need to explain this
a bit further. I have one form called Schools and another
called Mentors. Mentors had a standard subform, linked to
the Mentors form by MentorID. The subforms Control Source
is tblMentorSBTSchool which contains, among other fields,
MentorID and SchoolID.

I want to create a new, or modify the existing, command
button on form Schools which opens the Mentors form to
show those Mentor records whose subform records have the
same SchoolID as the School form record.

So, say tblMentorSBTSchool has a record for MentorID 20
showing SchoolID 12. Now if I am in the School form and I
select the record for SchoolID 12, then click on the
command button, I want it to open the Mentors form at the
record for MentorID 20 because its subform record will be
the one showing the schoolname for SchoolID 12.

Is that any clearer? Thanks again for the help. Cheers,
Noel
-----Original Message-----
If I understand you correctly your subform which contains MentorID and
SchoolID is not directly on the form Mentor. When you create a subform to a
main form through the wizard you have two choices. Create a 'true' subform
or create a linked form. If you chose the latter, then you have to pass the
mentorID value to the linked form using VBA code with the OpenArgs function.
However, if you create a 'true' subform, where the subform resides directly
on the Mentor Form, you don't have to worry about passing the Mentor ID to
the subform form. It will be 'linked' automatically. As you change the Main
form data, so will the subform data.


Hi. Can anyone help with this. I have rearranged part of
my Access 2002 mdb and now a command button that I set up
on a Schools form to open up a linked Mentors form needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is based
on a table also called Mentors. Yes, I know this is bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that now the
records in table Mentors no longer contain the SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is imbedded in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor form
also has a button that opens the Schools form where the
SchoolID is the same. I need to change this buttons code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool subform.

I hope this makes sense. Thanks for the help. Noel
Click to expand...


.
Click to expand...
Click to expand...
 
Hi. Im not sure what your name is but this worked
perfectly, after I got around the syntax error because of
the wrap adding an extra quote symbol after FROM. My
original question also asked how I could modify a similar
existing command button on the Mentors form so that it
opened the Schools form where the SchoolID is the same as
that currently being shown on the Mentors subform. I
think I can modify your code to work in this way -
something like:

DoCmd.OpenForm "Schools",,,"SchoolID IN (SELECT SchoolID
FROM
tblMentorSBTSchool WHERE tblMentorSBTSchool.MentorID=" &
Click to expand...
MentorID & ")"

Thank you very much for your help. Cheers, Noel
-----Original Message-----
Perhaps like this:

DoCmd.OpenForm "Mentors",,,"MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE tblMentorSBTSchool.SchoolID=" & SchoolID & ")"

HTH
- Turtle

Hi. Thanks for the reply. Perhaps I need to explain this
a bit further. I have one form called Schools and another
called Mentors. Mentors had a standard subform, linked to
the Mentors form by MentorID. The subforms Control Source
is tblMentorSBTSchool which contains, among other fields,
MentorID and SchoolID.

I want to create a new, or modify the existing, command
button on form Schools which opens the Mentors form to
show those Mentor records whose subform records have the
same SchoolID as the School form record.

So, say tblMentorSBTSchool has a record for MentorID 20
showing SchoolID 12. Now if I am in the School form and I
select the record for SchoolID 12, then click on the
command button, I want it to open the Mentors form at the
record for MentorID 20 because its subform record will be
the one showing the schoolname for SchoolID 12.

Is that any clearer? Thanks again for the help. Cheers,
Noel
-----Original Message-----
If I understand you correctly your subform which contains MentorID and
SchoolID is not directly on the form Mentor. When you create a subform to a
main form through the wizard you have two choices. Create a 'true' subform
or create a linked form. If you chose the latter, then you have to pass the
mentorID value to the linked form using VBA code with the OpenArgs function.
However, if you create a 'true' subform, where the subform resides directly
on the Mentor Form, you don't have to worry about passing the Mentor ID to
the subform form. It will be 'linked' automatically.
Click to expand...
As
you change the Main
form data, so will the subform data.


Hi. Can anyone help with this. I have rearranged
Click to expand...
part
of
my Access 2002 mdb and now a command button that I
Click to expand...
set
up
on a Schools form to open up a linked Mentors form needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is based
on a table also called Mentors. Yes, I know this is bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that
Click to expand...
now
the
records in table Mentors no longer contain the SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is
Click to expand...
imbedded
in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor form
also has a button that opens the Schools form where the
SchoolID is the same. I need to change this buttons code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool subform.

I hope this makes sense. Thanks for the help. Noel



.
Click to expand...
Click to expand...


.
Click to expand...
 
This approach will open your School form, filtered to show all the schools
for which your current Mentor is indeed a mentor; it will not show just the
school you have selected in the subform.

For that, somthing like this might be more effective:
DoCmd.OpenForm "Schools",,,"SchoolID =" & Me!sfmSchools.Form!SchoolID & ")"
(where sfmSchools is the name of the subform control)

HTH
- Turtle (my name)

Noel said:
Hi. Im not sure what your name is but this worked
perfectly, after I got around the syntax error because of
the wrap adding an extra quote symbol after FROM. My
original question also asked how I could modify a similar
existing command button on the Mentors form so that it
opened the Schools form where the SchoolID is the same as
that currently being shown on the Mentors subform. I
think I can modify your code to work in this way -
something like:

DoCmd.OpenForm "Schools",,,"SchoolID IN (SELECT SchoolID
FROM
tblMentorSBTSchool WHERE tblMentorSBTSchool.MentorID=" &
Click to expand...
MentorID & ")"

Thank you very much for your help. Cheers, Noel
-----Original Message-----
Perhaps like this:

DoCmd.OpenForm "Mentors",,,"MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE tblMentorSBTSchool.SchoolID=" & SchoolID & ")"

HTH
- Turtle

Hi. Thanks for the reply. Perhaps I need to explain this
a bit further. I have one form called Schools and another
called Mentors. Mentors had a standard subform, linked to
the Mentors form by MentorID. The subforms Control Source
is tblMentorSBTSchool which contains, among other fields,
MentorID and SchoolID.

I want to create a new, or modify the existing, command
button on form Schools which opens the Mentors form to
show those Mentor records whose subform records have the
same SchoolID as the School form record.

So, say tblMentorSBTSchool has a record for MentorID 20
showing SchoolID 12. Now if I am in the School form and I
select the record for SchoolID 12, then click on the
command button, I want it to open the Mentors form at the
record for MentorID 20 because its subform record will be
the one showing the schoolname for SchoolID 12.

Is that any clearer? Thanks again for the help. Cheers,
Noel

-----Original Message-----
If I understand you correctly your subform which
contains MentorID and
SchoolID is not directly on the form Mentor. When you
create a subform to a
main form through the wizard you have two choices.
Create a 'true' subform
or create a linked form. If you chose the latter, then
you have to pass the
mentorID value to the linked form using VBA code with
the OpenArgs function.
However, if you create a 'true' subform, where the
subform resides directly
on the Mentor Form, you don't have to worry about
passing the Mentor ID to
the subform form. It will be 'linked' automatically. As
you change the Main
form data, so will the subform data.


message
Hi. Can anyone help with this. I have rearranged part
of
my Access 2002 mdb and now a command button that I set
up
on a Schools form to open up a linked Mentors form
needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is
based
on a table also called Mentors. Yes, I know this is bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that now
the
records in table Mentors no longer contain the
SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is imbedded
in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open
the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor
form
also has a button that opens the Schools form where the
SchoolID is the same. I need to change this buttons
code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool
subform.

I hope this makes sense. Thanks for the help. Noel



.
Click to expand...


.
Click to expand...
Click to expand...
 
Ah yes, I see. Ive already tried my revised version of
your code and it does work - but then my mdb doesnt yet
have more than one school alocated to each mentor. That
will change in the near furure, so your suggestion will
be perfect. Thanks again for the help. Cheers, Noel
-----Original Message-----
This approach will open your School form, filtered to show all the schools
for which your current Mentor is indeed a mentor; it will not show just the
school you have selected in the subform.

For that, somthing like this might be more effective:
DoCmd.OpenForm "Schools",,,"SchoolID =" & Me! sfmSchools.Form!SchoolID & ")"
(where sfmSchools is the name of the subform control)

HTH
- Turtle (my name)

Hi. Im not sure what your name is but this worked
perfectly, after I got around the syntax error because of
the wrap adding an extra quote symbol after FROM. My
original question also asked how I could modify a similar
existing command button on the Mentors form so that it
opened the Schools form where the SchoolID is the same as
that currently being shown on the Mentors subform. I
think I can modify your code to work in this way -
something like:

DoCmd.OpenForm "Schools",,,"SchoolID IN (SELECT SchoolID
FROM
tblMentorSBTSchool WHERE
Click to expand...
tblMentorSBTSchool.MentorID=" &
MentorID & ")"

Thank you very much for your help. Cheers, Noel
-----Original Message-----
Perhaps like this:

DoCmd.OpenForm "Mentors",,,"MentorID IN (SELECT
Click to expand...
MentorID
FROM
tblMentorSBTSchool WHERE
Click to expand...
tblMentorSBTSchool.SchoolID=" &
SchoolID & ")"
HTH
- Turtle

Hi. Thanks for the reply. Perhaps I need to explain this
a bit further. I have one form called Schools and another
called Mentors. Mentors had a standard subform,
Click to expand...
linked
to
the Mentors form by MentorID. The subforms Control Source
is tblMentorSBTSchool which contains, among other fields,
MentorID and SchoolID.

I want to create a new, or modify the existing, command
button on form Schools which opens the Mentors form to
show those Mentor records whose subform records have the
same SchoolID as the School form record.

So, say tblMentorSBTSchool has a record for MentorID 20
showing SchoolID 12. Now if I am in the School form and I
select the record for SchoolID 12, then click on the
command button, I want it to open the Mentors form
Click to expand...
at
the
record for MentorID 20 because its subform record
Click to expand...
will
be
the one showing the schoolname for SchoolID 12.

Is that any clearer? Thanks again for the help. Cheers,
Noel

-----Original Message-----
If I understand you correctly your subform which
contains MentorID and
SchoolID is not directly on the form Mentor. When you
create a subform to a
main form through the wizard you have two choices.
Create a 'true' subform
or create a linked form. If you chose the latter, then
you have to pass the
mentorID value to the linked form using VBA code with
the OpenArgs function.
However, if you create a 'true' subform, where the
subform resides directly
on the Mentor Form, you don't have to worry about
passing the Mentor ID to
the subform form. It will be 'linked'
Click to expand...
automatically.
As
you change the Main
form data, so will the subform data.


message
Hi. Can anyone help with this. I have rearranged part
of
my Access 2002 mdb and now a command button that
Click to expand...
I
set
up
on a Schools form to open up a linked Mentors form
needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me! [SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is
based
on a table also called Mentors. Yes, I know this
Click to expand...
is
bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that now
the
records in table Mentors no longer contain the
SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is imbedded
in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open
the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor
form
also has a button that opens the Schools form
Click to expand...
where
the
SchoolID is the same. I need to change this buttons
code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool
subform.

I hope this makes sense. Thanks for the help. Noel



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi again. Just tried your new code and theres a bracket
missing. Where should it go? Thanks Noel
-----Original Message-----
This approach will open your School form, filtered to show all the schools
for which your current Mentor is indeed a mentor; it will not show just the
school you have selected in the subform.

For that, somthing like this might be more effective:
DoCmd.OpenForm "Schools",,,"SchoolID =" & Me! sfmSchools.Form!SchoolID & ")"
(where sfmSchools is the name of the subform control)

HTH
- Turtle (my name)

Hi. Im not sure what your name is but this worked
perfectly, after I got around the syntax error because of
the wrap adding an extra quote symbol after FROM. My
original question also asked how I could modify a similar
existing command button on the Mentors form so that it
opened the Schools form where the SchoolID is the same as
that currently being shown on the Mentors subform. I
think I can modify your code to work in this way -
something like:

DoCmd.OpenForm "Schools",,,"SchoolID IN (SELECT SchoolID
FROM
tblMentorSBTSchool WHERE
Click to expand...
tblMentorSBTSchool.MentorID=" &
MentorID & ")"

Thank you very much for your help. Cheers, Noel
-----Original Message-----
Perhaps like this:

DoCmd.OpenForm "Mentors",,,"MentorID IN (SELECT
Click to expand...
MentorID
FROM
tblMentorSBTSchool WHERE
Click to expand...
tblMentorSBTSchool.SchoolID=" &
SchoolID & ")"
HTH
- Turtle

Hi. Thanks for the reply. Perhaps I need to explain this
a bit further. I have one form called Schools and another
called Mentors. Mentors had a standard subform,
Click to expand...
linked
to
the Mentors form by MentorID. The subforms Control Source
is tblMentorSBTSchool which contains, among other fields,
MentorID and SchoolID.

I want to create a new, or modify the existing, command
button on form Schools which opens the Mentors form to
show those Mentor records whose subform records have the
same SchoolID as the School form record.

So, say tblMentorSBTSchool has a record for MentorID 20
showing SchoolID 12. Now if I am in the School form and I
select the record for SchoolID 12, then click on the
command button, I want it to open the Mentors form
Click to expand...
at
the
record for MentorID 20 because its subform record
Click to expand...
will
be
the one showing the schoolname for SchoolID 12.

Is that any clearer? Thanks again for the help. Cheers,
Noel

-----Original Message-----
If I understand you correctly your subform which
contains MentorID and
SchoolID is not directly on the form Mentor. When you
create a subform to a
main form through the wizard you have two choices.
Create a 'true' subform
or create a linked form. If you chose the latter, then
you have to pass the
mentorID value to the linked form using VBA code with
the OpenArgs function.
However, if you create a 'true' subform, where the
subform resides directly
on the Mentor Form, you don't have to worry about
passing the Mentor ID to
the subform form. It will be 'linked'
Click to expand...
automatically.
As
you change the Main
form data, so will the subform data.


message
Hi. Can anyone help with this. I have rearranged part
of
my Access 2002 mdb and now a command button that
Click to expand...
I
set
up
on a Schools form to open up a linked Mentors form
needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me! [SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is
based
on a table also called Mentors. Yes, I know this
Click to expand...
is
bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that now
the
records in table Mentors no longer contain the
SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is imbedded
in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open
the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor
form
also has a button that opens the Schools form
Click to expand...
where
the
SchoolID is the same. I need to change this buttons
code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool
subform.

I hope this makes sense. Thanks for the help. Noel



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi. Since my last post Ive played aroud and found that I
just needed to take out the existing bracket in the code.
All works perfectly now. Thanks, Noel (PS clocking off
now so wont see any reply from you until 10am GMT
tomorrow).
-----Original Message-----
This approach will open your School form, filtered to show all the schools
for which your current Mentor is indeed a mentor; it will not show just the
school you have selected in the subform.

For that, somthing like this might be more effective:
DoCmd.OpenForm "Schools",,,"SchoolID =" & Me! sfmSchools.Form!SchoolID & ")"
(where sfmSchools is the name of the subform control)

HTH
- Turtle (my name)

Hi. Im not sure what your name is but this worked
perfectly, after I got around the syntax error because of
the wrap adding an extra quote symbol after FROM. My
original question also asked how I could modify a similar
existing command button on the Mentors form so that it
opened the Schools form where the SchoolID is the same as
that currently being shown on the Mentors subform. I
think I can modify your code to work in this way -
something like:

DoCmd.OpenForm "Schools",,,"SchoolID IN (SELECT SchoolID
FROM
tblMentorSBTSchool WHERE
Click to expand...
tblMentorSBTSchool.MentorID=" &
MentorID & ")"

Thank you very much for your help. Cheers, Noel
-----Original Message-----
Perhaps like this:

DoCmd.OpenForm "Mentors",,,"MentorID IN (SELECT
Click to expand...
MentorID
FROM
tblMentorSBTSchool WHERE
Click to expand...
tblMentorSBTSchool.SchoolID=" &
SchoolID & ")"
HTH
- Turtle

Hi. Thanks for the reply. Perhaps I need to explain this
a bit further. I have one form called Schools and another
called Mentors. Mentors had a standard subform,
Click to expand...
linked
to
the Mentors form by MentorID. The subforms Control Source
is tblMentorSBTSchool which contains, among other fields,
MentorID and SchoolID.

I want to create a new, or modify the existing, command
button on form Schools which opens the Mentors form to
show those Mentor records whose subform records have the
same SchoolID as the School form record.

So, say tblMentorSBTSchool has a record for MentorID 20
showing SchoolID 12. Now if I am in the School form and I
select the record for SchoolID 12, then click on the
command button, I want it to open the Mentors form
Click to expand...
at
the
record for MentorID 20 because its subform record
Click to expand...
will
be
the one showing the schoolname for SchoolID 12.

Is that any clearer? Thanks again for the help. Cheers,
Noel

-----Original Message-----
If I understand you correctly your subform which
contains MentorID and
SchoolID is not directly on the form Mentor. When you
create a subform to a
main form through the wizard you have two choices.
Create a 'true' subform
or create a linked form. If you chose the latter, then
you have to pass the
mentorID value to the linked form using VBA code with
the OpenArgs function.
However, if you create a 'true' subform, where the
subform resides directly
on the Mentor Form, you don't have to worry about
passing the Mentor ID to
the subform form. It will be 'linked'
Click to expand...
automatically.
As
you change the Main
form data, so will the subform data.


message
Hi. Can anyone help with this. I have rearranged part
of
my Access 2002 mdb and now a command button that
Click to expand...
I
set
up
on a Schools form to open up a linked Mentors form
needs
to be changed. I probably used a wizard to create the
button and code.

The Schools forms buttons relevant On Click code is

stDocName = "Mentors"

stLinkCriteria = "[SchoolID]=" & Me! [SchoolID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

As you can see this calls up form Mentors, which is
based
on a table also called Mentors. Yes, I know this
Click to expand...
is
bad
practice but I only learned about using prefixes like
frm, tbl, cbo etc well after Id started designing the
mdb. The problem is I have changed things so that now
the
records in table Mentors no longer contain the
SchoolID.
Instead I have another table called tblMentorSBTSchool
whose records contain the Mentor ID and the SchoolID.
These records are presented on a subform called
frmMentorSBTSchoolSubform and this subform is imbedded
in
the Mentors form, linked by MentorID.

What change could I make to the code to make it open
the
Mentor form whose subform record has the same SchoolID.

I also have the same problem in reverse, the Mentor
form
also has a button that opens the Schools form
Click to expand...
where
the
SchoolID is the same. I need to change this buttons
code
so that it opens the Schools form where the SchoolID is
the same as that shown on the frmMentorSBTSchool
subform.

I hope this makes sense. Thanks for the help. Noel



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top