Show Record In Subform If Either Of Two Fields Match frmComboBox S

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 06 Jul 2007 02:02 GMT

I am having trouble figuring out how to accomplish the following in Access
2003:

I have two queries as the basis for my form.
1. One with a set of baseball teams in a league. Each record is one team,
and the key is TeamID.
2. One with the league's schedule. Each record is one scheduled game. Each
record has a TeamID in the VisTeam field, and another TeamID in the HomeTeam
field.

I want to set up a form and subform where:
A. The main form has a combo box to select one of the teams.
B. The subform shows all the games and only the games where that team is
scheduled as either VisTeam or HomeTeam.

For the life of me I cannot figure out how to accomplish this. Any help is
appreciated.

Reply to this Message

SteveM - 06 Jul 2007 03:22 GMT

So you you use the first query as the rowsource for your combo box on the
main form and use the second query as the recordsource for your subform.

In the second query in the VisTeam and HomeTeam fields, set the criteria to:
=Forms!frmMain!Combo

You need to put them on different criteria rows. If you place criteria on
the same row it represents an 'AND', if you put them on different rows it
represents an 'OR'. In this case, you want to return records where the
VisTeam is equal to the item selected in your combo box 'OR' the HomeTeam is
equal to the item selected in your combo box.

In your combo's AfterUpdate event, you should requery your subform:
Me.subform.Form.Requery

Another way to do it would be to have two subforms, one for home games te
other for away games. That way you could have both subforms based on the
league schedule table and just set the Master/Child links. One would have the
combo as master and VisTeam as child, th other would have the combo as master
and HomeTeam as child.

Steve

> I am having trouble figuring out how to accomplish the following in Access
> 2003:
[quoted text clipped - 13 lines]
> For the life of me I cannot figure out how to accomplish this. Any help is
> appreciated.

Reply to this Message

John - 06 Jul 2007 03:36 GMT

Thanks, Steve, I'll give that a try and post how it turns out...

> So you you use the first query as the rowsource for your combo box on the
> main form and use the second query as the recordsource for your subform.
[quoted text clipped - 36 lines]
> > For the life of me I cannot figure out how to accomplish this. Any help is
> > appreciated.

Reply to this Message