Recordset Loop stuck on first record

[ScrmingWhisprs]

I am attempting to use 2 fields from a query to supply the Top and Left Properties of a Collection of Rectangle Controls on my form. The purpose of this is to display the locations on a map of "Spots" in a haunted house I help run. The query that I am using shows the spots that have been pre-tagged with the location of where they belong on the map (currently the query has only 24 tagged spots). On the actual form I have rectangle controls (control type acRectangle) with their visible property set to False by default, named box1 through box25 (there will be more eventually, as I am just working with this test group).

I started with the following code, yet it stops after (correctly) placing the first spot on the map (please see the attached jpg):

Private Sub Form_Open(Cancel As Integer)

Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT qrySpotMap.Spot, qrySpotMap.MAPX, qrySpotMap.MAPY FROM qrySpotMap"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

For Each ctl In Me.Controls
    If ctl.ControlType = acRectangle Then
        rst.MoveFirst
            ctl.Left = rst.Fields("MAPX")
            ctl.Top = rst.Fields("MAPY")
            ctl.Visible = True
    End If
        rst.MoveNext
Next ctl
        

Set rst = Nothing
Set db = Nothing
            
End Sub

I'm sure I need to have 'Loop' in there somewhere, but I am not sure exactly where to place it, or if another line is also needed.

Thanks ahead of time!
Mike

 

[pbaldy]

I'm not sure I understand the goal, but looking at the code I'd expect every rectangle to be in the same spot because of:

rst.MoveFirst

 

[stopher]

I agree with pBaldy.

So you need to put the rst.movefirst before the For Each loop.

You also need to put the rst.movenext inside the If construct I think because otherwise you will skip records in the recordset when a non-rectangle control is found.

But would it not be better to loop through the recordset and move a rectangle according to each record like this. Note how we can refer to each rectangle using the variable x:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim x As Integer

strSQL = "SELECT qrySpotMap.Spot, qrySpotMap.MAPX, qrySpotMap.MAPY FROM qrySpotMap"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

x = 1
rst.MoveFirst
Do While Not rst.EOF
    Me.Controls("Box" & x).Left = rst.Fields("MAPX")
    Me.Controls("Box" & x).Top = rst.Fields("MAPY")
    Me.Controls("Box" & x).Visible = True
    x = x + 1
    rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

Of course you will need to test that there are not more records than rectangles.

hth
Chris

 

[ScrmingWhisprs]

So I ended up getting it working thanks to both pbaldy and stopher.

This was my final code with a couple of adjustment I did have to make, declaring the MAPX and MAPY variables as Integers, and I have attached what is looks like in the end:

Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim x As Integer
Dim intMAPX As Integer
Dim intMAPY As Integer

strSQL = "SELECT qrySpotMap.Spot, qrySpotMap.MAPX, qrySpotMap.MAPY FROM qrySpotMap"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    
x = 1

rst.MoveFirst
intMAPX = rst.Fields("MAPX")
intMAPY = rst.Fields("MAPY")

Do While Not rst.EOF
intMAPX = rst.Fields("MAPX")
intMAPY = rst.Fields("MAPY")

    Me.Controls("box" & x).Left = intMAPX
    Me.Controls("box" & x).Top = intMAPY
    Me.Controls("box" & x).Visible = True
    
    rst.MoveNext
    x = x + 1
    Loop

Set rst = Nothing
Set db = Nothing

The purpose of this map is to show where all the spots are in a haunted house. My system is a volunteer tracker, so we assign workers to spots that they will scare for the night. Eventually what I would like to happen with this map is to show all the available spots in green (as they are), and show all spots that have been taken for the night in red. Perhaps I can pick your brains down the road.

Thanks again!
Mike

 

[pbaldy]

Shouldn't be hard. Add a field to that table. In your loop test that field and set the color accordingly.

 

[MarkK]

You could get away with a bit less code . . .

Private Sub SetBoxPositions()
    With CurrentDb.OpenRecordset("qrySpotMap")
        Do While Not .EOF
            SetBoxPosition .AbsolutePosition + 1, !MAPX, !MAPY
            .MoveNext
        Loop
        .Close
    End With
End Sub

Private Sub SetBoxPosition(index As Integer, x As Integer, y As Integer)
    With Me.Controls("box" & index)
        .Left = x
        .Top = y
        .Visible = True
    End With
End Sub

 

[ScrmingWhisprs]

Hi pbaldy,

I seem to be stumbling my way through adjusting this query to incorporate Spots that have been assigned for a night, by having the rectangles on the map be a different color. I have a feeling I will now need to incorporate 2 different recordsets: one to show spots that have a worker assigned to and another to show all UNASSIGNED spots. Now I'm not exactly sure how to do that in the code.

When I try to add a field to the table like you mentioned last night, my query is now only showing me Spots that are assigned to workers. My query design is attached...

Mike

 

[pbaldy]

How do you tell the difference? I'd expect to be able to test the "Worker" field (for example). If there's a value there make it one color, if not make it another.

 

[ScrmingWhisprs]

Exactly, however the query is now only showing me spots from the Spot Table that have a related record in the Transaction (Sign In) table. Basically what I am getting are the spots that do have assigned workers (which I would want the rectangles to be red), but I also need in the same query the rest of the spots from the Spots table that do not have a worker assigned to for that specific night. My goal was to loop through this query and if the worker column was blank, the box would be green, otherwise the box would be red.

-Mike

 

[pbaldy]

If you edit the join you can make sure you get all records from the spots table. Fields from the transactions table will be Null if there is no record there, so you can test for that.

 

[ScrmingWhisprs]

Hello again,

I had finally got around to working on this again, and I ended up finding a way around what I was trying to do. I actually have 2 different queries (As 2 different recordsets in the code), 1 query to show the unassigned spots (the green boxes) and another query to show the assigned spots (the red boxes).

There are a total of 100 boxes on the form labeled 'box1' through 'box100'

The first recordset loops through the boxes starting at box1 until it reaches EOF.

The second recordset loops through the boxes starting at box100 and works backwards until EOF and changes those boxes to Red.

Thank you everyone who helped!
-Mike