so, a quiz wasn't enough and now I have (why not) made a scrabble game (I also have a point and click adventure game in progress can be found on here)
very basic at moment but does have a few scrabble functions which work well (well I think so)
On Playing after typing users’ names and progressing it assigns you the 7 letters (I did think about making these clickable tiles, if anyone has played scrabble on the PS1 you have an idea what I mean)
Scores your words (Again not 100% as it will do whole board)
Checks if your word is correct (if you type in to the text box at moment, again might be missing some two letter words)
It has the function to pick random letter to see who goes first (not yet fully implemented the lookup to show the result)
more to come with future updates and hope like what see so far
I think I misunderstood purpose of PlayerXTable. I had thought the seven letter fields were for words constructed but now presume they are for letters drawn by player and available during a turn.
Should I be able to see player letters?
Should the "select for first player" occur before letters are given to players or are all letters considered still available for the "first player" draw?
Suggest you get player tables correct before coding.
the board at present is like that just for the first move ( when i code it fully ) and yes true the select letter should be before name and letters given ill change the logic on that later
I thin you could do this much simpler if you normalized the data. This would significantly reduce the number of tables, amount of code, and number of action queries. Provides more flexibility too. Puts everything into one place.
Basically need 3 tables for managing the game.
With this design you can have more than 2 players if you wish without modifying code or table strcuture.
I modified Scrabbleletters to what I called tblLetters. This moves the points into this table for ease of calculations and add a Sort order field.
-LetterID
-Letter
-LetterScore (points)
-LetterOrder
Then you simply update the LetterOrder once at the beginning of the game and then pop letters from that list into tblGameLetters.
You push the correct amount of letters IDs for each player needing letters from this table into what I called tblGameLetters.
tblGameLetters
-PlayerID_FK
-LetterID_FK
-Played (yes if it is played, no if not)
-Multiplier (you can add a multiplier if this letter played on double, triple letter score)
(With this design you have one table and know what was played by what player, what letters are in their 'hand" but not yet played. Therefore how many new letters they need. With a simple query you can calculate all scoring except double,triple word score)
1. If I letter is played mark it played.
2. You can then simply figure out using a query how many letters a player needs.
3. You know what letters to draw from and in what order using this query. Pop the next available letter.
qryavailable
Code:
SELECT tblLetters.LetterID, tblLetters.LetterOrder
FROM tblLetters LEFT JOIN tblGameLetters ON tblLetters.LetterID = tblGameLetters.LetterID_FK
WHERE (((tblGameLetters.LetterID_FK) Is Null))
ORDER BY tblLetters.LetterOrder;
So almost all of your code simply becomes this.
1. You update the sort order at the beginning of the game
2. Whenever you need to you loop the players and determine from the query how many letters each player needs to draw. At the beginning of the game you can also specify 1 letter to determine sort order. (Obviously if a player did not just play any letters, they will not be assigned new ones. So you do not have to figure out who actually just played letters.
3. In scrabble I think players can throw letters back. Then just delete from tblGameLetters
Code:
Public Sub AssignRandom()
'Update the table at beginning of game
Dim I As Integer
Dim rs As DAO.Recordset
Dim strSql As String
Randomize
strSql = "Select rnd([id]) as sort, letter, Letterorder from tblLetters order by rnd([id])"
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
I = I + 1
rs.Edit
rs!letterOrder = I
rs.Update
rs.MoveNext
Loop
End Sub
Public Sub DrawLetters(Optional LetterDraw As Integer = 7)
Dim rsAvailable As DAO.Recordset
Dim rsPlayers As DAO.Recordset
Dim I As Integer
Dim InHand As Integer
Dim ToDraw As Integer
Dim strSql As String
'sorted letters not used or inhand
'Set rsAvailable = CurrentDb.OpenRecordset("qryAvailableLetters")
Set rsPlayers = CurrentDb.OpenRecordset("qrySortedPlayers")
'loop players
Do While Not rsPlayers.EOF
InHand = DCount("*", "tblGameLetters", "PlayerID_FK = " & rsPlayers!PlayerID & " AND Played = False")
ToDraw = LetterDraw - InHand
'if They have less in their hand then number to draw
If ToDraw > 0 Then
strSql = "Select Top " & ToDraw & " * from qryAvailableLetters order by letterOrder"
Debug.Print strSql
Set rsAvailable = CurrentDb.OpenRecordset(strSql)
'Check if any letters left
If Not rsAvailable.EOF Then
rsAvailable.MoveLast
rsAvailable.MoveFirst
If rsAvailable.RecordCount = 0 Then
MsgBox "No tiles left"
Exit Sub
End If
Do While Not rsAvailable.EOF
strSql = "Insert into tblGameLetters (LetterID_FK,PlayerID_FK) VALUES (" & rsAvailable!LetterID & ", " & rsPlayers!PlayerID & ")"
CurrentDb.Execute strSql
rsAvailable.MoveNext
Loop
End If
End If
rsPlayers.MoveNext
Loop
End Sub
( in one square , the middle square ) code to check whether the letter placed is/on your tiles/selection of letters allocated
text box ( wow kev, how do you come up with these ideas ) which shows the letters assigned/allocated ( yes it will be hidden on a turn by turn basis in future ) but also ability to shuffle letters as would maybe on real scrabble to maybe see a word
added function to whosfirst to show which is closer to A ( so in future can use that to pick which player plays the first word )
Updated
The about form was not quite working as should you would open from smallerscreen form and close the about and end up back on normal size form so fixed that
-------
Thanks for looking and for commenting and all ideas are very much welcome
Record of who was closer to a now works so gameboard small screen ( at the moment ) works to show if player one or player two is first and hides parts accordingly
I thin you could do this much simpler if you normalized the data. This would significantly reduce the number of tables, amount of code, and number of action queries. Provides more flexibility too. Puts everything into one place.
Basically need 3 tables for managing the game.
With this design you can have more than 2 players if you wish without modifying code or table strcuture.
I modified Scrabbleletters to what I called tblLetters. This moves the points into this table for ease of calculations and add a Sort order field.
-LetterID
-Letter
-LetterScore (points)
-LetterOrder
Then you simply update the LetterOrder once at the beginning of the game and then pop letters from that list into tblGameLetters.
You push the correct amount of letters IDs for each player needing letters from this table into what I called tblGameLetters.
tblGameLetters
-PlayerID_FK
-LetterID_FK
-Played (yes if it is played, no if not)
-Multiplier (you can add a multiplier if this letter played on double, triple letter score)
(With this design you have one table and know what was played by what player, what letters are in their 'hand" but not yet played. Therefore how many new letters they need. With a simple query you can calculate all scoring except double,triple word score)
1. If I letter is played mark it played.
2. You can then simply figure out using a query how many letters a player needs.
3. You know what letters to draw from and in what order using this query. Pop the next available letter.
qryavailable
Code:
SELECT tblLetters.LetterID, tblLetters.LetterOrder
FROM tblLetters LEFT JOIN tblGameLetters ON tblLetters.LetterID = tblGameLetters.LetterID_FK
WHERE (((tblGameLetters.LetterID_FK) Is Null))
ORDER BY tblLetters.LetterOrder;
So almost all of your code simply becomes this.
1. You update the sort order at the beginning of the game
2. Whenever you need to you loop the players and determine from the query how many letters each player needs to draw. At the beginning of the game you can also specify 1 letter to determine sort order. (Obviously if a player did not just play any letters, they will not be assigned new ones. So you do not have to figure out who actually just played letters.
3. In scrabble I think players can throw letters back. Then just delete from tblGameLetters
Code:
Public Sub AssignRandom()
'Update the table at beginning of game
Dim I As Integer
Dim rs As DAO.Recordset
Dim strSql As String
Randomize
strSql = "Select rnd([id]) as sort, letter, Letterorder from tblLetters order by rnd([id])"
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
I = I + 1
rs.Edit
rs!letterOrder = I
rs.Update
rs.MoveNext
Loop
End Sub
Public Sub DrawLetters(Optional LetterDraw As Integer = 7)
Dim rsAvailable As DAO.Recordset
Dim rsPlayers As DAO.Recordset
Dim I As Integer
Dim InHand As Integer
Dim ToDraw As Integer
Dim strSql As String
'sorted letters not used or inhand
'Set rsAvailable = CurrentDb.OpenRecordset("qryAvailableLetters")
Set rsPlayers = CurrentDb.OpenRecordset("qrySortedPlayers")
'loop players
Do While Not rsPlayers.EOF
InHand = DCount("*", "tblGameLetters", "PlayerID_FK = " & rsPlayers!PlayerID & " AND Played = False")
ToDraw = LetterDraw - InHand
'if They have less in their hand then number to draw
If ToDraw > 0 Then
strSql = "Select Top " & ToDraw & " * from qryAvailableLetters order by letterOrder"
Debug.Print strSql
Set rsAvailable = CurrentDb.OpenRecordset(strSql)
'Check if any letters left
If Not rsAvailable.EOF Then
rsAvailable.MoveLast
rsAvailable.MoveFirst
If rsAvailable.RecordCount = 0 Then
MsgBox "No tiles left"
Exit Sub
End If
Do While Not rsAvailable.EOF
strSql = "Insert into tblGameLetters (LetterID_FK,PlayerID_FK) VALUES (" & rsAvailable!LetterID & ", " & rsPlayers!PlayerID & ")"
CurrentDb.Execute strSql
rsAvailable.MoveNext
Loop
End If
End If
rsPlayers.MoveNext
Loop
End Sub
Been a bit busy with work and the like but have been toiling away when i can, see version .91N
updated
The button new word check now asks for a word and checks it against dictionary ( still need to sort some error catching and rules if invalid like in real life i think its a miss a go ) it will then ask for coordinates and place your letters and remove them from your selection, calculate and replace to make back to 7 ( but seen a few times where it is maybe over riding some letters in the PlayerOne / PlayerTwoTable )
also have changed the start up logic ever so. So now users put in names choosed who will be first and then on this it assigns the random 7 letters
player turn is now working so after played a word it will switch after done all of the logic for calc and the like
working on
i do have a check if letter is allocated so need to get that worked in and also when it assigns letters i think it is ticking them all of same letter so need to check that
added - exchange of letters so now you can exchange your 7 letters just like in a normal real life old fashioned scrabble game ( may not be fancy or slimline in code/query but it works for me )
to work on still - the code some times seems to get stuck when you place a word doesn't seem to update active player, also still need to work out 2 main things to check if letter is in the users selection when they type the word when asked and also on scoring, when using letters already on the game board
( think that last one going to be biggest headache )
