MS Access as a Game Developing Tool - Part 1
Games? With an office productivity tool? Sure, why not!
For my first game project, I chose a Word Search creation tool. I had
originally programmed this game in Turbo Pascal version 3. Now with Windows XP, my ancient
version would not work and I was looking to re-program it using a more modern version or
another language. Why not Access with VBA?
(article continues after sponsor spot)
Let's survey the various pieces that I required
- a form that would have a grid of "cells" in which the letters of the puzzle
- a list of already chosen words
- a method of storing and retrieving puzzles
- a method for showing solutions
- a random number generator
- the ability to design custom shapes
- the ability to remove words from the puzzle
- the ability to print puzzles
1. Form Design
Since I knew how I wanted the screen to look, I started with it. This
screen shot shows an existing puzzle being examined, with 3 of the words highlighted using
the word finder option.
You can see (starting with objects on the left side)
- a list of available puzzles
- the Edit, Print, and New Puzzle buttons
- a check box for special sorting during printing
- the puzzle
- the puzzle title
- a text box for entry of words to be inserted
- a series of radio buttons to choose direction
- the Place Word, Fill Blanks, and Save Changes buttons
- a list of words in the current puzzle
- the word finder instructions and reset button
Some explanations of the above
- List of available puzzles is obvious. I currently have 9 saved puzzles.
- The Edit button reads all the data from the database for the chosen puzzle and places
that data on the screen
- The Print and New Puzzle buttons should be obvious.
- The check box for special sorting during printing is a feature that is not fully
implemented in this version. The idea is that when the printout lists the words for the
puzzle, they are normally listed in alphabetic order. There will be times when you want a
custom sort order. For example, when I made a puzzle for young children called
"Numbers", I wanted the numbers from 1 to 20 done in word form, and listed in
numeric order. Well, the word"one" comes after the word "four"
alphabetically, but I wanted it in the obvious order. Although this feature is working in
my system with manual modification of the appropriate table, I want to modify the screen
to allow the GUI to handle this.
- The puzzle consists of 225 text boxes (a 15 x 15 grid) designed to hold one character
each. In Access 2000 you are allowed about 750 objects on a form over the lifetime of the
form. The pictured form has under 300, so we could go to a larger grid based on form
limits. However, the table is designed to have the same 225 fields, and since tables have
a limit of 255 fields, I chose to stay with 15 x 15 rather than use a different table
- The puzzle title is used to populate the list of available puzzles, and also appears on
the report as a title.
- When you want to add a word to a puzzle in progress (the pictured one is finished) you
enter it in the "next word" textbox, choose a direction, and then click the
"Place Word" button.
- By default, each word will be placed in a random direction.In the above screen shot you
can see that each of the 3 words shown in highlighting goes in a different direction.
- A new puzzle starts out with a period "." in each "cell". As you
enter words the "empty" cells decrease in number. In most cases, you finish the
puzzle with some empties still in the puzzle. When done, click the "Fill Blanks"
button to have the program put random characters in the remaining empties.
- Save Changes is obvious.
- List of words in the current puzzle is obvious.
- If you are solving the puzzle on paper, and cannot find one of the words (stumped
yourself!!!) you can double click on the word and it will highlight it for you. In the
screen shot above there are 3 such words.
- The Reset button turns the highlighting off on the found words.
2. Table Designs
There are two primary tables:
- autonumber primary key
- title of puzzle
- 225 single character text fields (field names are of the style "accrr", eg.
a0712, which represents the character in column 7, row 12) I chose to store each character
separately as it speeds up the reports and the placing of words on the screen when editing
a puzzle (I had considered just using tblWords and adding more logic to the report and
edit functions; also, the left over empty cells needed to be stored as a future version
will allow custom placing of left over characters)
- autonumber primary key
- puzzle number (link to primary key of tblPuzzleData)
- column of first character in the word
- row of first character in the word
- direction of the word
- the word
- sort position (optional)
NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.
3. Use of Random Numbers
To determine the starting positions of words, we used a random brute force
method. That is, randomly choose a starting point for a word and see if it fits. If it
does, great. If it does not fit, randomly choose another starting point. Continue this
process until you either find a place to fit the word, or you determine that it will not
|| ' use byte to save memory, since the
largest these numbers get is 15
Dim i as Byte
Dim j as Byte
Dim iX as Byte
Dim iY as Byte
Dim numCellsTried as Integer
Dim bytGrid(15, 15) as Byte '- array matching the
cells (text boxes)
'--- initialize the random number generator
'--- initialize grid array
' keeps track of which
starting cells we have tried
For i = 1 To 15
For j = 1 To 15
bytGrid(i, j) = 0
numCellsTried = 0
If numCellsTried >= 225 Then GoTo DidNotFit
'--- randomly choose a starting cell using Rnd function
iX = Int((15 * Rnd) + 1) '- number between 1 and 15
iY = Int((15 * Rnd) + 1) '- number between 1 and 15
'--- if cell already tried, go to another cell
If bytGrid(iX, iY) = 1 Then GoTo nextCell
'--- tried another cell, mark it and also track how many have
numCellsTried = numCellsTried + 1
bytGrid(iX, iY) = 1
I have never considered myself an "elegant" coder, often using
brute force methods. They work, but maybe you have a more elegant way of doing what I am
doing. Let me know if you do have a cleaner way of handling the logic.
In part 2 of this series
(here), we will look at
the rest of the code that places a new word on the grid, and saves the words.
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.