requerying a parent form from a subform

[KRosier]

I have a parent form (char_frm) with a subform (char_sub_frm) linked by
playerID(one player, many characters).

The parent form shows the character’s name (Cname) and other info.

The subform shows all of the alternate characters that a player has
(Cname)and other info in datasheet format.

The parent form has been filtered to show only one record.

I would like to be able to doubleclick on an alt’s name in the subform
and have the parent form refresh to show that alt’s Cname and related alts.

What code do I need to write for the doubleclick event?

Any help would be very much appreciated.

Kathy

 

[Graham Mandeno]

Hi Kathy

I can't quite picture your table structure and I'm a little confused from
your description about what is a player and what is a character.

However, all you should need to do is to change the filter on the main form
to show the new record you want, and then the master/child link will
automatically update the subform. For example:

With Me.Parent
.Filter = "CharID=" & Me.CharID
If Not .FilterOn Then .FilterOn = True
End With

 

[KRosier]

Rats, I tried to boil it down so I wouldn't be too wordy. Let me try again.

Two tables in a one to many relationship on playerID

player_tbl with the name of the real person (PName) - i.e. Bob

char-tbl contains the name of the characters (CName) the real person
plays - i.e. Star, Moon, Sun, and other info about the character

The Parent form (char_frm) shows one CName and that character's
information i.e. Star

The Subform (char_sub_frm), linked by playerID, shows all other
characters that the real person plays - a list of CNames. i.e. Star,
Moon, Sun

I have arrived at the char_frm by doublclicking on a CName (i.e. Star)
in a third form using this code:

'open the selected character's information form
DoCmd.OpenForm "char_frm", , , "charID=" & [Char_names]

This shows me Star's record in the char_frm and all other characters
(Star, Moon, Sun) played by the real person (Bob) in the char_sub_frm.
The form has been filtered to show only Star's record.

What I would now like to do is to be able to doubleclick on one of the
names in the subform and bring up their information in the parent form.
i.e. doubleclick on Moon in char_sub_frm and show Moon and his
information in char_frm with the list of other characters (Star, Moon,
Sun)in the char_sub_frm, played by the same real person (Bob).

Basically, someone using this form may or may not know Bob's name, but
they know Star. They can see that Star is "related" to Moon and Sun and
would like more detailed information about Moon, so they doubleclick on
Moon to see that information.

Whew! I tried to make this concise, really! Please forgive me if I
haven't made myself clear or if the question is a really simple one. I'm
one of those (nightmares?) that is self-taught, by example. I know
enough to know something can be done, just not enough to know how to do
it. Thanks.

Kathy

 

[Graham Mandeno]

Hi Kathy

Before you continue too far, consider this question: Is it possible for the
role of Star to have been played by Jim as well as Bob?

It the answer is "yes" then there is a problem with your database structure
that should be addressed. You don't have a one-to-many relationship between
players and characters, but a many-to-many relationship. (Any player may
play many characters, and any character may be played by many players.) To
represent this, you need three tables:

player_tbl:
PlayerID
PName
... other stuff about the player

character_tbl:
CharID
CName
... other stuff about the character (e.g. a link to a Plays table)

roles_played_tbl
PlayerID
CharID
... perhaps other information, e.g. a link to a Performances table

If the relationship is really only one-to-many, then the technique I gave
you yesterday should work:

With Me.Parent
.Filter = "CharID=" & Me.CharID
If Not .FilterOn Then .FilterOn = True
End With

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Rats, I tried to boil it down so I wouldn't be too wordy. Let me try
again.

Two tables in a one to many relationship on playerID

player_tbl with the name of the real person (PName) - i.e. Bob

char-tbl contains the name of the characters (CName) the real person
plays - i.e. Star, Moon, Sun, and other info about the character

The Parent form (char_frm) shows one CName and that character's
information i.e. Star

The Subform (char_sub_frm), linked by playerID, shows all other characters
that the real person plays - a list of CNames. i.e. Star, Moon, Sun

I have arrived at the char_frm by doublclicking on a CName (i.e. Star) in
a third form using this code:

'open the selected character's information form
DoCmd.OpenForm "char_frm", , , "charID=" & [Char_names]

This shows me Star's record in the char_frm and all other characters
(Star, Moon, Sun) played by the real person (Bob) in the char_sub_frm. The
form has been filtered to show only Star's record.

What I would now like to do is to be able to doubleclick on one of the
names in the subform and bring up their information in the parent form.
i.e. doubleclick on Moon in char_sub_frm and show Moon and his information
in char_frm with the list of other characters (Star, Moon, Sun)in the
char_sub_frm, played by the same real person (Bob).

Basically, someone using this form may or may not know Bob's name, but
they know Star. They can see that Star is "related" to Moon and Sun and
would like more detailed information about Moon, so they doubleclick on
Moon to see that information.

Whew! I tried to make this concise, really! Please forgive me if I
haven't made myself clear or if the question is a really simple one. I'm
one of those (nightmares?) that is self-taught, by example. I know enough
to know something can be done, just not enough to know how to do it.
Thanks.

Kathy

Hi Kathy

I can't quite picture your table structure and I'm a little confused from
your description about what is a player and what is a character.

However, all you should need to do is to change the filter on the main
form to show the new record you want, and then the master/child link will
automatically update the subform. For example:

With Me.Parent
.Filter = "CharID=" & Me.CharID
If Not .FilterOn Then .FilterOn = True
End With

 

[KRosier]

Hi Graham!

It is indeed a one to many relationship. The database is for a guild
from an online game...there had better not be anyone else playing my
characters on my account ;-)

I tried your code yesterday and it didn't work. Tried it again tonight
and still didn't work, messed with it a bit, still didn't work and
finally, cleared the whole thing out, closed all the form design windows
and the module window and started again, and voila! It works like a charm.

Thank you very much for your help.

Kathy