Access Table Problem

[Steven Lovell]

Good Evening All,

i have a little problem and i wondered if anyone could help me

Background Information

Im the results secretary of a darts and dominoes league in the uk,
i have to produce various league tables weekly and various team stats once
every 4 weeks
Currently im using excel but to be honest with you its getting to the stage
that its becoming to much for excel,
so i have decided to have a go in access
i have a little knowledge of access setting up simple databases so designing
this one will become a massive task and im upto the challenge.

Current Info

The database set up consists of
1.A table for list of teams and contains test data of 3
teams...................for info this season is 13
2.A table for a list of players and teams and contains test data of 5
players ...........for info this season is 186
3.A table for a list of doubles dart matches and contains test data but
doesnt work see below

My problem at the moment is when on step no 3 above i want to select a team
in one box which is a drop down menu and in another box the players names
appears who only play for that team as set up in step 2, this is to happen 8
times on the table as there are 4 doubles darts matches and if the match is
won each player gets 1 point each and the team gets 1 point

i also have to set a table for 8 darts singles matches, 3 dominoes pairs
matches and 3 5&3's matches(a variant of dominoes)

Can anyone kindly help me in my problem in step 3

Regards

Steven Lovell


Also for info


some players play darts, dominoes and 5&3's
i have to record all wins for the above plus dart scores of 100 or more and
scores of 180
also produce weekly leagues tables for everything and produce team stats
monthly
also i produce all the above info on webpages and i upload them to the
internet so anybody in the league or the anybody on the internt can have a
look at them
the current excel file is nearly 17mb and consists of 16 macroes and 1
master macro which runs all 16 and when activated takes nearly 1 min to run

megga thanks in advance

Steve

 

[strive4peace]

Hi Steven,

I am assuming you will set up a form to enter your data. Here is an
example you can pattern after.

Each table should have an autonumber ID field to uniquely identify the
records -- TeamID, PlayerID, etc.

Your Matches table will have MatchID, MatchDate (date and time), etc.

Your MatchPlayers table will have MatchPlayID, MatchID, PlayerID

The main form will be based on Matches

The subform will be based on MatchPlayers and will probably be a
continuous subform (or Datasheet)

You can collect a TeamID for purposes of limiting the Player combo, but
it does not have to be stored because the Players table should have
TeamID in it

on your subform for the player, limit the combobox to specific records
when it gets the focus, show all records when you leave it

on the gotFocus event of the Player combobox, assign this:

=SetRowSource(nz(TeamID_controlname,0))


on the lostFocus event of the combobox, assign this:

=SetRowSource(0)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource( _
pTeamID as long)

on error goto Err_proc

dim strSQL as string

strSQL = "SELECT PlayerID, PlayerName" _
& " FROM Players"

if pTeamID > 0 then

strSQL = strSQL _
& " WHERE (TeamID= " & pTeamID & ")"

end if

strSQL = strSQL & "ORDER BY PlayerName;"

debug.print strSQL

me.PlayerID.RowSource = strSQL
me.PlayerID.Requery

Exit_proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_proc
End function

'~~~~~~~~

WHERE
PlayerID is the NAME of the combobox for the player ID


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*