DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

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

wordsearch1.jpg (39Kbytes)

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.

   ' 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
   randomize
  
   '--- 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
      Next j
   Next i
  
   numCellsTried = 0
  
nextCell:
   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 been tried
   numCellsTried = numCellsTried + 1
   bytGrid(iX, iY) = 1

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, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy