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 would go
• 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 design.
• 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:

tblPuzzleData

• 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)

tblWords

• 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 fit.

Further Thoughts

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.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.