Creating A Form to List Teams - Please Help

[spinny]

Hi All,

I am trying to create a form where I can access queries by using a combo box and bring up data on 2 teams on the the same screen. I have attached the database to this post.

The database opens up to the form I am trying to design. I have added the combo box at the top so you can see what I am trying to achieve - I know it wont work the way it is setup! I would like to select 2 teams from the combo box(s) and have those teams data appear so that I can add comments when recruiting.

If anyone could help me with how I can do this I would be very grateful.



Thanks

Andy

[marianne]

sounds like homework to me

[RuralGuy]

Spinny,
If it is not homework, then let us know with what specific issue you are having a problem and we'll try and help.

[spinny]

unfortunately its my job to set this up so not homework.... i basically want to be able to select a team from the combo box at the top of the page and that teams details to fill the form below. I cant figure out how to do this. If anyone could help that would be great.

thanks

[RuralGuy]

First off, fill in some dummy details for the teams so you have some records to work with. Then use the ComboBox wizard to create another cbo in the header and pick Option #3 when the wizard asks. You should really have AutoNumber PrimaryKeys in your tables.

[spinny]

ok I have done that but there are two problems

1. There are 15 players on each team. Is there a way to stop the combo box displaying the same team 15 times.

2. All I want on the screen is the 15 players from team A. I seem to get everyone in the DB

Thanks so much for the earlier help

Andy

[RuralGuy]

How many records do you have in your RecordSource?

[spinny]

2-3000 records
75-200 teams
Fields are

ID (Primary Autonumber)
Team Name
First
Last
Number
Grad Year
Comments
Rating

I can send you the DB with data if you want to have a look at it - its 704k

[RuralGuy]

What is the SQL for the RowSource of your ComboBox?

[RuralGuy]

You need at least one more table, PLAYERS. I'm not sure what NFC stands for but the records in there now seem to be players on teams. Are the team names, names for the teams in the TEAMS table? If so then that is the table where the team names belong, in another field in that table. I've also added an AutoNumber PrimaryKey field to the TEAMS and RATINGS tables.

[spinny]

thank you for looking at that for me. im almost there. there are two things that i still cant figure out.

1. If I select DALLAS on the combo box at the top of the page I only want the players who play for DALLAS to show up.

2. I need to be able to see 2 teams on the same screen - i.e. if PRINCETON are playing DALLAS I need to get one on the left and one on the right. Is this possible?

I have attached the DB as it is now

Thank you so much for your time.
Andy

[RuralGuy]

What is the Team field in the Players table used for? If it is one of the teams in the Teams table then it should be a LongInteger field with the TeamID in it and *not* a text field with the name. In a RDBMS the data should only be in one table. The other tables have a ForeignKey pointing to the record in the other table. What happened to the names like Barracudas and Beach Premier Breakers? If those are the names of the teams then I would have added a field to the Teams table.

[spinny]

The data that was in the original table was wrong. The team names for the tournament are in the TEAMS table (16 Teams). The PLAYERS table refers to all the players who are at the tournament but also has the team that they play for as I don’t know how to link the two tables and work with the TEAMID field as you suggested. The RATINGS table is just a simple lookup combo box for rating these players.

[RuralGuy]

I removed the Team field from the Players table and added a TeamID field and populated it with the FK from the Teams table. Look at the qryPlayers and you will see everything put back together.

[RuralGuy]

I lost track of this thread. Is it solved Andy?