newbie..need help updating textboxes with combobox

[drumhrd]

Ok so I am new at access. I am attempting to build a database to track a pool league which will have 3 man teams. Track players, teams, scores and the end goal is to be able to pull player and team statistics via reports. I am going to try to document my database as well as possible before I give details to my issue.

Right now I have 4 tables (relationships attached via jpg image.)

Players table

playerID firstname lastname skill level address city state zip phone email

Teams Table

Team ID Team Name

Team Lineups Table

TeamNumber P1_ID P2_ID P3_ID

Match Table

week player team wins losses break and run


I have a simple form to add a name team where I get a teamID and team name in my teams table

I have another simple form to add a player's info into the players table

I have a 3'rd form that is a bit more complicated, using comboboxes and text boxes I am able to get the players lineup table populated. Basically each team has 3 players so I want to track what player is on what team. so the team lineups table has the Team_Number + player 1 ID player 2 ID and player 3 ID.

I have 4 combo boxes, I didn't change the names so it's a bit confusing (see team lineups form.jpg)

combo13 = team name
combo34 = player 1
combo 39 = player 2
combo43 = player 3

Now the combo boxes use a row source of a query of the players table. SQL view

SELECT Players.[First Name], Players.[Last Name], Players.Player_ID
FROM Players
ORDER BY Players.Player_ID;

I get the first name and last name of the player in the combo box list. I then have a little VB to push updates to the text boxes so I can actually use the text box control source to update my team lineups table.

Private Sub Combo13_AfterUpdate()
Me!Text11 = Me!Combo13.Column(0)
End Sub
Private Sub Combo34_AfterUpdate()
Me!Text28 = Me!Combo34.Column(2)
End Sub
Private Sub Combo39_AfterUpdate()
Me!Text37 = Me!Combo39.Column(2)
End Sub
Private Sub Combo43_AfterUpdate()
Me!Text41 = Me!Combo43.Column(2)
End Sub

Ok so everything up to this point is working just fine. What I need now is a form to enter in the weekly match information. (see weekly team scores.jgp) This is where I am having issues and am not sure where how to do it.

I have a combobox list for the week. 1-13 manually entered values..no biggie.

Now, here is what I need. I need to be able to select the team name from the combo box, autofill the player1 -3 firstname/lastname in the player1-3 fields and also fill the playerID fields. Then I can enter in their wins/losses and break and runs. I thought I could get the info somehow by using the team lineup table since I would know the teamID and playerIDs for each player on the team but I can't figure out how.

and

If I can get all of this info filled out I really don't know how to get the button to add these records. since there should be 3 records added to the Match table, one for each player, when the button is clicked. I think the default add record event on the button only adds 1 record..but in this case..there would be 3 that needs to be added.


Any and all help is highly apprieciated.

Tony

 

[Instance]

I think you're shooting yourself in the foot with your table design. Players can only be on a single team, so I'd do something like;

tPlayers
..PlayerID
..FirstName
..LastName
..TeamID
..Whatever

tTeams
..TeamID
..Name
..Whatever is team-specific. Perhaps mascot. Perhaps venue of choice. Perhaps nothing.

Two teams play a game and each have a score, so (I'm sure I'm doing this poorly, but it's 11:30p, and I've been up since 5am) something in the general vacinity of:

tGames
..GameID
..GameDate
..Team1ID
..Team2ID
..Team1Score
..Team2Score

With this structure you can easily and quickly figure out how many games a team/player has won/lost. You can figure out which players are on a team. The first or last game a team won/lost/played/whatever. Etc.

Then I'd make a subform that's dataset is tPlayers.*. I'd put that subform on your main form, and I'd set down a combo box for cboTeamID that lists teams. When you type in the date for the game, and select the team, it will set the RecordSource of the subform to something like:

"SELECT * FROM tPlayers WHERE (tPlayers.TeamID = " & cboTeamID & ");"

This would only list the three players, in their own textboxes, for the team selected. See the OnChange event for the cboTeamID combo box.

Duplicate this combobox/subform for Team2.

I'll try to make you an example tomorrow evening but I may be running errands.

If you can post the database you're using so far, I'll be glad to take a peek at it and see if I can help further.

Best of luck!

 

[drumhrd]

Like I said..I am a total newbie..if I have to throw it out then so be it. I want to do it the right way.

I attached the DB..note there is alot of crap I am not using but was playing with.

when designing the team match form(table) I need to take into consideration the finalized product which will be a report system that will give me the following

team wins
Individual Player win/loss averages
player break and run averages

by storing information in the tgames table as you suggest I loose the ablility to see individual player scores, which is a must to calculate win/loss averages and break and run averages..these scores will define the skill level of each player at the end of each 13 week session.

**background info driving need for individual scores**
skill level C thru A++
teams can have the following player skill combinations
ABC
BBC
BCC
BBC

Every team has to have a C player..and every team cannot have 2 A players...so a report system (based on individual player statistics) will help make sure that the teams are lined up properly.
**end background info**

If you or anyone else can take a peak at my DB and give me any idea of how to get this accomplished it would be awesome!

Thanks.

Tony