Randomize Help for an Access Game

[Jeff Conrad]

Hi,

Using Access 97 at the moment, but the game will work across later versions.

I've created a game in Access.
Why? I'm an Access Junkie, what can I say!
Who says you can't have fun with Access?
(Incidentially, I'd be happy to share it with anyone when completed)
Impress the kids.

Anyway, the game works fine.......for now.
I still need to put a few finishing touches on it and will no doubt probably
have to pose a few questions.

My game grid right now is always the same so it's fun for about one time
only. I need a way to randomize the game grid data so the game will be
different every time. I knew this would be the most challenging part of the
process. I came really close to getting a solution, but hit a brick wall.

The game is a matching-style game. You know, match two tiles and then keep
going until you get all the matches correct. You have to try and remember
where you saw the "other" letter or number. The game is timed and I'll then
build a table to post the best times (that will be the next problem I'm
afraid). Currently I just manually put a letter or number into the Control
Source of each text box on an unbound form like so ="A". Everything fine.

Next step:
Here's what I need to do in "English":
"Randomly fill all 64 text boxes on the grid with the pre-defined game
values so the game is different evey time."

So I thought I should create a table with all the predefined values and then
through code have Access randomly pull a record, stuff the first text box
with the value, mark that record as used (so it won't get pulled again) and
then continue the process until all 64 text boxes are filled. I *thought* I
had it last night, but you'll see why it failed further down.

The table I created is like so:
tblGameData
GameDataID (Autonumber)
GameDataText (Text)
Used (Yes/No)

I only have two records right now for testing.
This structure is not set in stone, if I need to change it, by all means let
me know.
The form is called frmGamePad and the text boxes are txt1, txt2,....txt64
for simplicity.

Here is the code I have right now which is soooo close to working:

Private Sub Form_Open(Cancel As Integer)

Dim Counter As Integer
' Matches number of text boxes

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim SelectString As String
Dim RandomRecord As Integer

For Counter = 1 To 2
' Will be 64, just testing with 2 right now

Randomize
' Reset the seed
RandomRecord = Int((2 * Rnd) + 1)
' Random number between 1 and 2 at the moment

Set db = CurrentDb
SelectString = "SELECT tblGameData.* " _
& "FROM tblGameData " _
& "WHERE (((tblGameData.Used)=False)) " _
& "And ((tblGameData.GameDataID)=" _
& RandomRecord & ");"
' Randomly select a record by matching
' the Autonumber field to the RandomRecord
' But only if the record has not been used

Set rst = db.OpenRecordset(SelectString)

Me("txt" & Counter) = rst!GameDataText
' Fill the text box with the record data

' Now mark the record as used
rst.MoveFirst
With rst
.Edit
rst!Used = -1
.Update
End With

' Close everything
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

' Now loop back through until all text boxes are filled
Next Counter

End Sub

This worked!!!
.......the first time though. :-((
I think you can see why it did not work the next time.
The random number generated MAY pull up an Autonumber for a record that has
already been used. In which case Access coughs up a hairball. Not good.

So, how can I solve this? I keep thinking of ideas, but can't seem to get
anything to work.
Can I make a query that randomly grabs all the records and then stuff the
text boxes?

Any ideas, thoughts, or comments are welcome.
Thanks,
Jeff Conrad
Bend, Oregon

 

[Roger Carlson]

On my website(see sig below) is a small sample database called
"DataScramble", which is used for randomizing data within a particular field
for confidentiality reasons. It does this by creating a separate, temporary
table with all the values of the field you want to scramble. Then is starts
at the top record of the main table and copies random values from the
temporary table back into the main table. As each value is copied back in,
it is deleted from the temporary table.

You might be able to adapt it for your use.

 

[Jeff Conrad]

Hi Roger,

Cool, I'll check it out!
Sounds like it may be what I'm after.

Thanks for the help,
Jeff Conrad
Bend, Oregon

 

[LGC]

Jeff,

I'll refrain from commenting on the idea of using Access as a game engine.

To make your idea work though, I would simply read your data into an array,
and shuffle it. There is no need for the 'Used' field.

I haven't used DAO for a while, so using your code as a basis, I would do
something like the following:

Air Code - No error checking:

---------------------

Dim GameData(1 to 64) as String
Dim Index as Integer
Dim SwapIndex as Integer
Dim SwapData as String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'Read Data
Set rst = db.Openrecordset ("SELECT GameDataText FROM tblGameData;")
With rst
.Movefirst
Index = 0
Do Until .Eof
Index = Index + 1
GameData(Index) = rst.Fields("GameData")
Loop
End With

'Shuffle Data
Randomize
For Index = 1 To 64
SwapIndex = Int((64 * Rnd) + 1)
SwapData = GameData(SwapIndex)
GameData(SwapIndex) = GameData(Index)
GameData(Index) = SwapData
Next

'Load Textboxes
For Index = 1 To 64
Me.Controls("txt" & Index) = GameData(Index) <<<<<<<Not sure of syntax
off the top of my head
Next

'Close Objects
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

---------------------

 

[Jeff Conrad]

Hi,

Comments below:

I'll refrain from commenting on the idea of using Access as a game engine.

LOL!!
I commend your restraint!

To make your idea work though, I would simply read your data into an array,
and shuffle it. There is no need for the 'Used' field.

OK, I removed it.
I also filled all 64 records in.

I haven't used DAO for a while, so using your code as a basis, I would do
something like the following:

Air Code - No error checking:

---------------------

Dim GameData(1 to 64) as String
Dim Index as Integer
Dim SwapIndex as Integer
Dim SwapData as String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'Read Data
Set rst = db.Openrecordset ("SELECT GameDataText FROM tblGameData;")
With rst
.Movefirst
Index = 0
Do Until .Eof
Index = Index + 1
GameData(Index) = rst.Fields("GameData")
Loop
End With

'Shuffle Data
Randomize
For Index = 1 To 64
SwapIndex = Int((64 * Rnd) + 1)
SwapData = GameData(SwapIndex)
GameData(SwapIndex) = GameData(Index)
GameData(Index) = SwapData
Next

'Load Textboxes
For Index = 1 To 64
Me.Controls("txt" & Index) = GameData(Index) <<<<<<<Not sure of syntax
off the top of my head
Next

'Close Objects
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

OK, I tried this, but I'm running into some issues.
At this part:

With rst
.Movefirst
Index = 0
Do Until .Eof
Index = Index + 1
GameData(Index) = rst.Fields("GameData")
Loop
End With

It errors out with a subscript out of range.
Stepping through the code the "index" goes up to 65 and continues up.
Not sure why it doesn't stop at 64.
Any ideas?

Temporarily I had it stop at 64 with an If statement to continue on through
the rest of the code. Each text box, however, was filled with the value from
the first record. So each text box said "A". Not sure why, but it is
probably because I'm not very familiar with arrays.

Thanks for any help,
Jeff Conrad
Bend, Oregon

 

[Douglas J. Steele]

OK, I tried this, but I'm running into some issues.
At this part:

With rst
.Movefirst
Index = 0
Do Until .Eof
Index = Index + 1
GameData(Index) = rst.Fields("GameData")
Loop
End With

It errors out with a subscript out of range.
Stepping through the code the "index" goes up to 65 and continues up.
Not sure why it doesn't stop at 64.
Any ideas?

Temporarily I had it stop at 64 with an If statement to continue on through
the rest of the code. Each text box, however, was filled with the value from
the first record. So each text box said "A". Not sure why, but it is
probably because I'm not very familiar with arrays.

You've forgotten the .MoveNext, so you've got an infinite loop.

 

[Jeff Conrad]

Hi Doug!

Ahhh haaa, much better!!
That would explain why it kept going out of range.
My initial tests seem to show this is working correctly.
I'll still need to do some testing as well as work on the
remaining issues.

However, I'm wondering if you could indulge me a bit? I
can step through the code and see everything happening,
but I can't quite grasp what *exactly* is going on in the
random area of the code. I'm not very familiar with
arrays. Could you give me a "dummies" version of what the
code is doing? I want to really understand it, not just
use it.

Thanks for any help,
Jeff Conrad
Bend, Oregon

 

[Douglas J. Steele]

The comments do sort of explain it...

This opens a recordset to bring all of the records back from tblGameData and
store them in an array. Since GameData was dimensioned from 1 to 64, the
code will fail if more than 64 records are returned.

'Read Data
Set rst = db.Openrecordset ("SELECT GameDataText FROM tblGameData;")
With rst
.Movefirst
Index = 0
Do Until .Eof
Index = Index + 1
GameData(Index) = rst.Fields("GameData")
Loop
End With

This "shuffles" the 64 records. For each value of Index from 1 to 64, it
generates a random number SwapIndex, which is between 1 and 64. It then
swaps element Index of the array with element SwapIndex

'Shuffle Data
Randomize
For Index = 1 To 64
SwapIndex = Int((64 * Rnd) + 1)
SwapData = GameData(SwapIndex)
GameData(SwapIndex) = GameData(Index)
GameData(Index) = SwapData
Next

This assumes that you have 64 text boxes on your form, named txt1, txt2,
txt3, ... to txt64. It loads a value from the array into each of the 64 text
boxes.

'Load Textboxes
For Index = 1 To 64
Me.Controls("txt" & Index) = GameData(Index) <<<<<<<Not sure of syntax
off the top of my head
Next

 

[Jeff Conrad]

Hi Doug,

Thanks for the walkthrough.
I was understanding everything pretty well except for the random swapping
thing. It makes more sense now.

Thanks for the help,
Jeff Conrad
Bend, Oregon