Linking Subforms in a Form

[Commish]

I'm usually pretty competant in Acccess. That is, I can find the
answers on the web or in a book, but this has me stumped.

I'm trying to build an application to view baseball stats.

And what I am trying to do - but haven't found the method yet is the
following:

I've made a query to pull out a unique list of teams, and put that
query in a form.
I've made a query to pull out a set of hitting statistics, and put
that query in a form. This hitting query has a relationship to the
team query.

Finally, I've trying to create a form, where I would insert both of
these forms, as subforms, and then use the first query to select a
team (from a pulldown) and have the hitting stats part of the form
populated with the players from the team that I selected in the team
query.

Any help would be appreciated, I've looked at quite a few books, and
online resources, and there's little direction about the correct way
to do subforms. I think that I don't even know what to search for.

Thanks.

 

[Arvin Meyer [MVP]]

The easiest way I've seen is used in the Northwind sample database that
comes with Access. The Orders form has 2 subforms. The first subfrom is
linked on CustomerID, and Subform2 links on OrderID and:

[Customer Orders Subform1].Form![OrderID]

Works great.

 

[golfinray]

You need a mainform and subform. The mainform would be the hitters and the
subform would be the stats for each hitter (one-to-many) relationship. You
need to have a primary key in the table of hitters. Something to uniquely
identify each hitter. The that same number will be the foreign key in your
stats table. Join those two tables in your query and then create the form off
that with mainform/subform. Your unique number should be maybe a hitter ID
number you assign.

 

[Commish]

You need a mainform and subform. The mainform would be the hitters and the
subform would be the stats for each hitter (one-to-many) relationship. You
need to have a primary key in the table of hitters. Something to uniquely
identify each hitter. The that same number will be the foreign key in your
stats table. Join those two tables in your query and then create the formoff
that with mainform/subform. Your unique number should be maybe a hitter ID
number you assign.

I appreciate the help from both of you Access gurus - but is there
anything I can do to get Access 2007 to look like Access 2000?
Everything I do takes 2X as long now because I have to re-learn where
everything was moved to....

 

[Commish]

OK, I've used the Northwinds DB, and I have the picklist picking
(Temas) and the form refreshing the hitters - but it doesn't filter on
the team that's been selected, what should I be looking for as far as
properties or events in the Northwinds DB.

Any thoughts to share on where to look?

The easiest way I've seen is used in the Northwind sample database that
comes with Access. The Orders form has 2 subforms. The first subfrom is
linked on CustomerID, and Subform2 links on OrderID and:

[Customer Orders Subform1].Form![OrderID]

Works great.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access

I'm usually pretty competant in Acccess. That is, I can find the
answers on the web or in a book, but this has me stumped.

I'm trying to build an application to view baseball stats.

And what I am trying to do - but haven't found the method yet is the
following:

I've made a query to pull out a unique list of teams, and put that
query in a form.
I've made a query to pull out a set of hitting statistics, and put
that query in a form. This hitting query has a relationship to the
team query.

Finally, I've trying to create a form, where I would insert both of
these forms, as subforms, and then use the first query to select a
team (from a pulldown) and have the hitting stats part of the form
populated with the players from the team that I selected in the team
query.

Any help would be appreciated, I've looked at quite a few books, and
online resources, and there's little direction about the correct way
to do subforms. I think that I don't even know what to search for.

Thanks.

 

[Arvin Meyer [MVP]]

Do you have a TeamID field in both sunforms to link on?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


OK, I've used the Northwinds DB, and I have the picklist picking
(Temas) and the form refreshing the hitters - but it doesn't filter on
the team that's been selected, what should I be looking for as far as
properties or events in the Northwinds DB.

Any thoughts to share on where to look?

The easiest way I've seen is used in the Northwind sample database that
comes with Access. The Orders form has 2 subforms. The first subfrom is
linked on CustomerID, and Subform2 links on OrderID and:

[Customer Orders Subform1].Form![OrderID]

Works great.
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access

I'm usually pretty competant in Acccess. That is, I can find the
answers on the web or in a book, but this has me stumped.

I'm trying to build an application to view baseball stats.

And what I am trying to do - but haven't found the method yet is the
following:

I've made a query to pull out a unique list of teams, and put that
query in a form.
I've made a query to pull out a set of hitting statistics, and put
that query in a form. This hitting query has a relationship to the
team query.

Finally, I've trying to create a form, where I would insert both of
these forms, as subforms, and then use the first query to select a
team (from a pulldown) and have the hitting stats part of the form
populated with the players from the team that I selected in the team
query.

Any help would be appreciated, I've looked at quite a few books, and
online resources, and there's little direction about the correct way
to do subforms. I think that I don't even know what to search for.

Thanks.

 

[Risse]

I'm usually pretty competant in Acccess. That is, I can find the
answers on the web or in a book, but this has me stumped.

I'm trying to build an application to view baseball stats.

And what I am trying to do - but haven't found the method yet is the
following:

I've made a query to pull out a unique list of teams, and put that
query in a form.
I've made a query to pull out a set of hitting statistics, and put
that query in a form. This hitting query has a relationship to the
team query.

Finally, I've trying to create a form, where I would insert both of
these forms, as subforms, and then use the first query to select a
team (from a pulldown) and have the hitting stats part of the form
populated with the players from the team that I selected in the team
query.

Any help would be appreciated, I've looked at quite a few books, and
online resources, and there's little direction about the correct way
to do subforms. I think that I don't even know what to search for.

Thanks.

 

[Commish]

Do you have a TeamID field in both sunforms to link on?
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access


OK, I've used the Northwinds DB, and I have the picklist picking
(Temas) and the form refreshing the hitters - but it doesn't filter on
the team that's been selected, what should I be looking for as far as
properties or events in the Northwinds DB.

Any thoughts to share on where to look?

The easiest way I've seen is used in the Northwind sample database that
comes with Access. The Orders form has 2 subforms. The first subfrom is
linked on CustomerID, and Subform2 links on OrderID and:

[Customer Orders Subform1].Form![OrderID]

Works great.

Yes, in the relationships, the teamid (HCRLShortName) is the link and
it is linked.

 

[Commish]

Do you have a TeamID field in both sunforms to link on?
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access


OK, I've used the Northwinds DB, and I have the picklist picking
(Temas) and the form refreshing the hitters - but it doesn't filter on
the team that's been selected, what should I be looking for as far as
properties or events in the Northwinds DB.

Any thoughts to share on where to look?

The easiest way I've seen is used in the Northwind sample database that
comes with Access. The Orders form has 2 subforms. The first subfrom is
linked on CustomerID, and Subform2 links on OrderID and:

[Customer Orders Subform1].Form![OrderID]

Works great.

I think that the part that is missing it this:

I use a query, the select a list if the 13 available teams, but
there's no control to go back and filter it on the 1 team that is
chosen in the form.

When the query to select the hitters is refreshed, it's still not
limited by the team selected in the top of the form.

So, where do I put that control?

 

[Armen Stein]

I use a query, the select a list if the 13 available teams, but
there's no control to go back and filter it on the 1 team that is
chosen in the form.

When the query to select the hitters is refreshed, it's still not
limited by the team selected in the top of the form.

So, where do I put that control?

You can put a hidden unbound textbox control on the main form. In the
Current event of the Team subform, set the hidden control to the
current Team key, something like this (replace My* fields with your
names):

Me.Parent!MyHiddenTeamKey = Me!MyTeamKey

Then, set the Master field property of the Hitters subform to that
hidden control on the main form.

Now, whenever you change Teams, the Hitters from that team will be
displayed in the other subform.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Arvin Meyer [MVP]]

Have a look at the way I've linked list boxes based on what is chosen in 1
list box:

http://accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

Do you have a TeamID field in both sunforms to link on?
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access


OK, I've used the Northwinds DB, and I have the picklist picking
(Temas) and the form refreshing the hitters - but it doesn't filter on
the team that's been selected, what should I be looking for as far as
properties or events in the Northwinds DB.

Any thoughts to share on where to look?

The easiest way I've seen is used in the Northwind sample database that
comes with Access. The Orders form has 2 subforms. The first subfrom is
linked on CustomerID, and Subform2 links on OrderID and:

[Customer Orders Subform1].Form![OrderID]

Works great.

I think that the part that is missing it this:

I use a query, the select a list if the 13 available teams, but
there's no control to go back and filter it on the 1 team that is
chosen in the form.

When the query to select the hitters is refreshed, it's still not
limited by the team selected in the top of the form.

So, where do I put that control?