'Serving the Microsoft® Access
Community since 1997'

Free Samples
About Us



MS Access as a Game Developing Tool - Part 2

Games? With an office productivity tool? Sure, why not!

In part 1 of this article (here) we looked at the form design, the table designs, and at the code for randomly placing words on the grid. Now we will look at the rest of the code that places the words and saves them.

Overview - User Driven

  • type desired word in text box
  • choose the direction they want the word to go in (for children you probably want just left to right and top to bottom directions)
  • click "Place Word" button

Overview - Computer Driven

  • initialize the random number generator
  • if user chooses random direction, pick a direction
  • set the X and Y deltas
  • initialize the temporary grid used to keep track of which cells we have tried
  • pick a starting cell at random
  • see if the word fits at that starting cell, in the chosen direction (must fit physically within grid, and must not conflict with a previously placed word)
  • if it fit, add the word to the table and place the letters from the word on the screen
  • wait for next word from user

Let's look at some details now.

Direction Deltas

Based on the direction that the user chose, we want to move from cell to cell in the correct order. For example, if the user chooses a simple left to right direction (the way we normally read) then each letter is on the same row as the previous letter, but one column to the right. Using good old fashioned X, Y co-ordinates, the X value increments by +1 and the Y value increments by 0. This is case 4 in the below code, with the 4 corresponding to the radio button that has a value 4.

Thus, if the first letter is assigned to grid location (4, 3) then the 2nd letter will be placed at grid location (5, 3), obtained by adding one to the X value and leaving the Y value at its previous value.

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.

'--- direction deltas
If Me.optDirection = 1 Then
    iD = Int((8 * Rnd) + 2)   '- number between 2 and 9
    iD = Me.optDirection
End If
Select Case iD
    Case 2
        iXdelta = 0
        iYdelta = -1
    Case 3
        iXdelta = 1
        iYdelta = -1
    Case 4
        iXdelta = 1
        iYdelta = 0
    Case 5
        iXdelta = 1
        iYdelta = 1
    Case 6
        iXdelta = 0
        iYdelta = 1
    Case 7
        iXdelta = -1
        iYdelta = 1
    Case 8
        iXdelta = -1
        iYdelta = 0
    Case 9
        iXdelta = -1
        iYdelta = -1
End Select

Initialize the Grid

I have an array the same size as the puzzles 15x15 grid. A value of 0 indicates that cell has not been tried yet as the starting place for the current word, and a value of 1 indicates it has been tried.

'--- initialize grid array
For i = 1 To 15
    For j = 1 To 15
        bytGrid(i, j) = 0
    Next j
Next i

numCellsTried = 0

Does the Word Fit?

Pick a starting point and loop through all the letters in the word.   See comments in code for further details.

'--- if we have tried all the cells, stop
If numCellsTried >= 225 Then GoTo DidNotFit

'--- randomly pick a starting cell
iX = Int((15 * Rnd) + 1)   '- number between 1 and 15
iY = Int((15 * Rnd) + 1)   '- number between 1 and 15

'--- has that cell be tried yet?
If bytGrid(iX, iY) = 1 Then GoTo nextCell

numCellsTried = numCellsTried + 1
bytGrid(iX, iY) = 1

iL = Len(Me.txtWord)    '--- length of word we are trying to place
iXs = iX    '---- iX and iY are the randomly chosen starting points
iYs = iY     '--- we will store them in iXs and iYs

'--- does the word fit?
'--- assume that it does (innocent until proven guilty !!!!)
ysnWordFit = True   
For i = 1 To iL    '--- for all the letters in the word
    '--- this next line allows for the fact that we can
    '--- choose to create a shape that is
    '--- not square or is not the full 15x15.
    '--- if a cell in the grid has been disabled,
    '--- it will be either a blank or null. 
    '--- We change those to "!" so that we cannot
    '--- have a match (unless you actually choose a
    '--- word with an exclamation mark!!)

    strX = Nz(Me("a" & Format(iX, "00") & Format(iY, "00")), "!")
    '--- if the cell we are examining is a "." or matches
    '--- the current letter being placed,
    '--- then we can continue,   otherwise, the word did not fit

    If (strX <> "." And strX <> Mid(Me.txtWord, i, 1)) Then
        ysnWordFit = False
        Exit For
    End If
    '---- move to the next applicable cell;  
    '--- if it is outside the 15x15 grid, stop
    iX = iX + iXdelta
    iY = iY + iYdelta
    If iX < 1 Or iX > 15 Or iY < 1 Or iY > 15 Then
        ysnWordFit = False
        Exit For
    End If
Next i

The Word Fit;   Save It

If we get through all the letters and are still alive, then place the letters on the screen and save them in a table.

iX = iXs
iY = iYs
'--- fill in the letters on the screen's grid 
'--- (note how I build the name of the text box
'--- representing each cell on the screen)

For i = 1 To iL
    Me("a" & Format(iX, "00") & Format(iY, "00")) = Mid(Me.txtWord, i, 1)
    iX = iX + iXdelta
    iY = iY + iYdelta
Next i
'--- save the word and info about it in the tmpWords table;
'--- this data will be transfered
'--- to the permanent table when the user chooses the Save option

Set rst = CurrentDb.OpenRecordset("select * from tmpWords where 1=2")
rst!wordstartC = iXs
rst!wordstartR = iYs
rst!worddirection = iD
rst!word = Me.txtWord
Set rst = Nothing

'--- refresh word and wait for next
'--- leave direction option the same as last time

Me.txtWord = Null
Exit Sub

MsgBox "could not fit " & Me.txtWord
GoTo exit_PlaceIt

Further Thoughts

In part 3 of this series, we will look at the rest of the code that saves the puzzle and allows for custom shapes, and any other loose ends.

(article continues after sponsor spot)

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