Hard Question about displaying records based on a listbox

[BrianPaul]

I hope this can be done through code or some work around:

I have a table that has every division I football Team
Table Name = Tteams only 1 field team

I have a form not bound that displays a listbox based on the table teams.
So it displays the team alphabetically for me.

I have another table called Tschedule with the following fields:
Date of Game
Home team
Hscore
Visiting Team
Vscore
Then I have a form called Fshedule to input all the games.

Question: On the main form when I double click a team, I would like to show
all records for that team. The problem is that the team can be the Home team
or it could be the visiting team. So how do I display all records if the
selected team in the listbox can be either the home team or visiting Team.

**Note I did get it to work in a perimeter query by combing the two fields
however, as you know when you run the query it prompts you for your selection.

Thanks, Any help would be appreciated

 

[Rick Brandt]

I hope this can be done through code or some work around:

I have a table that has every division I football Team
Table Name = Tteams only 1 field team

I have a form not bound that displays a listbox based on the table
teams.
So it displays the team alphabetically for me.

I have another table called Tschedule with the following fields:
Date of Game
Home team
Hscore
Visiting Team
Vscore
Then I have a form called Fshedule to input all the games.

Question: On the main form when I double click a team, I would like
to show all records for that team. The problem is that the team can
be the Home team or it could be the visiting team. So how do I
display all records if the selected team in the listbox can be either
the home team or visiting Team.

**Note I did get it to work in a perimeter query by combing the two
fields however, as you know when you run the query it prompts you for
your selection.

Thanks, Any help would be appreciated

SELECT *
FROM TSchedule
WHERE [Home Team] = Forms!FormName!ListBoxName
OR [Visiting Team] = Forms!FormName!ListBoxName

 

[BrianPaul]

Here is the really stupid question. I understand the query statement. Where
Do I place it. Doesnt seem to work by placing it on the double click event
property of the list box. Thinking about just creating another form that list
Fresults and trying to use the code when I double click on the list box to
open Fresults and display the select statement. Thanks, Hope I can get it to
work.

I hope this can be done through code or some work around:

I have a table that has every division I football Team
Table Name = Tteams only 1 field team

I have a form not bound that displays a listbox based on the table
teams.
So it displays the team alphabetically for me.

I have another table called Tschedule with the following fields:
Date of Game
Home team
Hscore
Visiting Team
Vscore
Then I have a form called Fshedule to input all the games.

Question: On the main form when I double click a team, I would like
to show all records for that team. The problem is that the team can
be the Home team or it could be the visiting team. So how do I
display all records if the selected team in the listbox can be either
the home team or visiting Team.

**Note I did get it to work in a perimeter query by combing the two
fields however, as you know when you run the query it prompts you for
your selection.

Thanks, Any help would be appreciated

SELECT *
FROM TSchedule
WHERE [Home Team] = Forms!FormName!ListBoxName
OR [Visiting Team] = Forms!FormName!ListBoxName

 

[Rick Brandt]

Here is the really stupid question. I understand the query
statement. Where Do I place it. Doesnt seem to work by placing it on
the double click event property of the list box. Thinking about just
creating another form that list Fresults and trying to use the code
when I double click on the list box to open Fresults and display the
select statement. Thanks, Hope I can get it to work.

Sorry, that would be the SQL of a query which is not really what you asked
for. To translate that into opening a filtered form you only need the WHERE
clause portion to use with the DoCmd.OpenForm method.

DoCmd.OpenForm "FormName",,,"[Home Team] = '" & Me!ListBoxName & "' OR
[Visiting Team] = '" & Me!ListBoxName & "'"

 

[BrianPaul]

Thanks for helping me out. Im sure the SQL statement will come in handy
later. I will paste your post in my library so I wont have to ask the same
question twice or if it comes up from someone in a future post.

Here is the really stupid question. I understand the query
statement. Where Do I place it. Doesnt seem to work by placing it on
the double click event property of the list box. Thinking about just
creating another form that list Fresults and trying to use the code
when I double click on the list box to open Fresults and display the
select statement. Thanks, Hope I can get it to work.

Sorry, that would be the SQL of a query which is not really what you asked
for. To translate that into opening a filtered form you only need the WHERE
clause portion to use with the DoCmd.OpenForm method.

DoCmd.OpenForm "FormName",,,"[Home Team] = '" & Me!ListBoxName & "' OR
[Visiting Team] = '" & Me!ListBoxName & "'"