Complete novice with Access and got a few simple (hopefully) questions

[niall]

Only been using Access for just under a year in A-level Computing, and I've hit a few walls in my coursework project.

It's a football (soccer) league table with a form to input the match data, and I've got most of it working. I've got lines upon lines of coding to determine things like who the winner is and update queries to add the points into a table along with clean sheets, goal difference and games played etc.

1) Basically, the actual league table is shown through a subform on a report. It's opened through a button, and just displays a preview of the report. The *LEAGUE* table is sorted into points which, I'm sure many of you know, is the way in which league tables are ordered. However, other fields such as games played and goal difference come in to account - for example: Man Utd; 79 pts; 32 games played take a higher league position than Chelsea; 79 pts; 33 games played. Goal difference is 3rd in the sorting priority order - for example: Man Utd; 79 pts; 32 games played; +40 goal difference take a higher league position than Chelsea; 79 pts; 32 games played; 38 goal difference.
How can I sort the *LEAGUE* table in to order through more than one field? Will I need to put code into the criteria in my select query than generates the fields for the subform? Or will it not work at all in a subform? Any help appreciated but please remember I am a novice and don't have as much of an understand of Access as a lot of you seem to!!



2) Another thing - possibly more simple to answer - is what is the code to check if a form is open? All my forms for the project are open via a click event on another form by using the line of code 'DoCmd.OpenForm "FormName"'. I want an error message to appear when a user clicks on a button to open another form when a form that shouldn't be open at the same time is open. I've tried 'If "FormName".Open = True Then MsgBox "Error - please complete or close the other form"' but that didn't work . You get the jist, so a bit of help there would also be appreciated.

3) One last thing is append queries. I used to be great at them when we first started learning them in class, but I've completely forgotten them. I want to be able to add a new player to my 'All Players' table, which the values are input by the user on a form. So the user inputs the squad number, player name, position and select the team name from a combo box which takes the values from the table with the team names and team ID's in. How do I add that record to the 'AllPlayers' table. It also needs validation in that if the new player has the same squad number as a player already in that team, it shouldn't allow it.

I don't expect answers to every single thing I've asked, but any help would be extremely appreciated!
Thanks in advance

[June7]

1. Don't sort tables. Records in tables have no intrinsic order and order in table should be irrelevant. Build queries and apply sort/filter criteria in query.

2. If CurrentProject.AllForms("your form name").IsLoaded Then

3. Why don't you use a combobox for selecting player? If player not listed, can use NotInList event to add new player record. Review: http://support.microsoft.com/kb/197526

[niall]

1. Don't sort tables. Records in tables have no intrinsic order and order in table should be irrelevant. Build queries and apply sort/filter criteria in query.

2. If CurrentProject.AllForms("your form name").IsLoaded Then

3. Why don't you use a combobox for selecting player? If player not listed, can use NotInList event to add new player record. Review: http://support.microsoft.com/kb/197526

1. By 'table' I mean the 'League Table' that is created through a subform which is sorted in ascending order of a field named 'Pts'. I've question point 1 in the OP. Sorry for the confusion, I should have been clearer.

2. Thanks. Will put that in and tell you if it works!

3. I found it hard to explain what I need for number 3 without explaining the WHOLE project and providing screenshots. Basically, I just need an append query that adds a new player into a table which includes NewPlayerName, NewPlayerTeam, NewPlayerPosition and NewPlayerSquadNumber which will need validation so that two players can't have the same number. If you need any screenshots or more explanation, I'd obviously be more than happy to provide them!

[June7]

Okay, no combobox for player but how will you know the player is new? Just run the code regardless? Perhaps in form BeforeUpdate event? Assuming player name and team name fields are text and position and squad fields are number, something like:

If IsNull(DLookup("PlayerName", "Players", "PlayerName='" & Me.PlayerName & "'")) Then
CurrentDb.Execute "INSERT INTO Players(NewPlayerName, NewPlayerTeam, NewPlayerPosition, NewPlayerSquadNumber) VALUES('" & Me.NewPlayerName & "', '" & Me.NewPlayerTeam & "', " & Me.NewPlayerPosition & ", " & Me.NewPlayerSquadNumber)
End If

I hope you are not duplicating the NewPlayerTeam, NewPlayerPosition, NewPlayerSquadNumber data between tables.

[orange]

niall,
Could you post a jpg of your tables and relationships? Relationships window.

Here's a link to a model for Football or Soccer Teams and Fixtures. It may not be related to your needs but it may give you some ideas.

Good luck with your project

[niall]

I've provided brief explanations for some fields that seem to be causing a bit of confusion. I understand that "soccer" (pft - football!) isn't every Americans'/Candians' strong point... and I believe a lot of people on here are from around there!

[niall]

Possibly creating link entity tables (I think that's what they're called?) would be better, however I haven't had any duplication of records across the tables or anything like that and everything seems to be going fine! My problem with the append query is just getting it as one, new record. I could possibly work it out myself with a bit of fiddling around but I need some extra help as my teacher is a bit useless.

Edit: infact, I could probably still get a high mark without the append query for adding a new player, I just thought that it would be nice to have it! My main issue with this project at the moment is question 1 in the OP. Any help there? Cheers!!

[June7]

Did you try the code I suggested?

Sorry, lost on the criteria for sorting records. This might require a custom function that takes the 3 elements you describe and assigns a rank value. Function can be called in query.

[niall]

I'm not on the computer that has Access installed at the moment, so I'll get back at you tomorrow! Thanks for your help, you two.

[niall]

2. If CurrentProject.AllForms("your form name").IsLoaded Then

I've put this in but I don't know how to use it properly. I've tried:

Do
If Project_LEAGUE_TABLE.AllForms("frmHomeGoal").IsLoa ded Then
MsgBox "Please complete or cancel the 'Home Goal' form before trying to add an away goal"
End If
Loop Until Project_LEAGUE_TABLE.AllForms("frmHomeGoal").IsNot Loaded

Would this work? If not, what's a fix? Thanks again

[June7]

Why can't they have both forms open?

or

Why don't you just close the form for them if it's loaded?

[niall]

Why can't they have both forms open?

or

Why don't you just close the form for them if it's loaded?

Because the two forms we're talking about are GOAL forms - one for the away and one for the home team respectively. They pop up when the user presses the 'GOAL' button in the area for the home or the away team on my main form. The home team and the away team can't score at the same time, and also I want it to just remind the user to complete or cancel the other form, incase they forgot or made a mistake by clicking GOAL, before they add a goal to the other sides' count.

Could just close the form I suppose, but I'd have to have a warning message to serve as a reminder?

Also, another thing that's cropped up and I've forgotten is focusing. I've got validation for all my text boxes, and in the lost_focus events a message box appears if the text box/combo box IsNull or = "". So then I've attempted to just put in 'focus.textbox' until the users inputs correct data into the text box - but for some reason it doesn't like the syntax and highlights the 'focus.textbox' in the debugger. Am I typing the wrong syntax? Cheers

[June7]

If you set the Goal form as Modal then they can't go anywhere else until the Goal form is closed. They'll learn.

However, I don't normally set form as Modal because it interferes with debugging (I use code to set form as modal with acDialog parameter when I open form) - so don't set that property until you are sure everything works.

Show the exact code for the focus. I use BeforeUpdate event for validation code.

[niall]

If you set the Goal form as Modal then they can't go anywhere else until the Goal form is closed. They'll learn.

However, I don't normally set form as Modal because it interferes with debugging (I use code to set form as modal with acDialog parameter when I open form) - so don't set that property until you are sure everything works.

Show the exact code for the focus. I use BeforeUpdate event for validation code.

Just a simple IF sequence.

Private Sub txtKickOffTime_LostFocus()
If txtKickOffTime = "" Or IsNull(txtKickOffTime) Then
MsgBox "Please enter a time"
Focus.txtKickOffTime <-- I've been told to just put that in to get the focus back on the text box, so they can't leave the text box until txtkickofftime isn't empty. Maybe need a loop?
End If
End Sub

[June7]

That looks like VB.net syntax. VBA would be:

Me.txtKickOffTime.SetFocus