rst.movenext

[supmktg]

I can't get this code to move to the next record. There are 32 label records, but rst!label_value is stuck on record 1's value for all 32 records? (I checked it with the msgbox)

Dim rst As Recordset
Dim sql as string

sql = "Select * from T_Labels where Code = '" & Me.cboProcedure & "'"
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)
'Show labels
If rst.RecordCount <> 0 Then
rst.MoveFirst
While Not rst.EOF
Me("lbl" & rst!Label_value).Visible = True
'msgbox rst!Label_value
rst.MoveNext
Wend

rst.Close
Set rst = Nothing
End If

What am I doing wrong?

Thanks,
Sup

 

[RuralGuy]

What is this line:
Me("lbl" & rst!Label_value).Visible = True
supposed to be doing?

 

[supmktg]

T_Labels contains 32 records, where label_value = 1 thru 32. Depending on the selection of cboProcedure, some (or all) of the labels are visible and some (or all) are not. The labels are named 'lbl1', 'lbl2', etc thru 'lbl32'. So, if the recordset filtered by cboProcedure contains label_values = 1,3 and 5, lbl1 and lbl3 and lbl5 should be visible. 'Me("lbl" & rst!Label_value).Visible = True' sets them to visible.

My problem is that only lbl1 becomes visible. I tested the code with a msgbox. When the code loops through the recordset, it moves through the correct number of records, but rst!label_value remains = 1 (the first record).

Any clues?
Sup

 

[RuralGuy]

Are you saying that when you uncomment the MsgBox line in the code you get the correct number of MsgBoxes popping up but the value is always the same? Are there any other fields in the table you could display in the MsgBox statement?

 

[supmktg]

Exactly! The other field in the table will be the same value for all of the filtered records.

Sup

 

[RuralGuy]

Something is wrong, as you suspected. How about changing the MsgBox to:
Debug.Print "Value [" & rst!Label_value & "] and position [" & rst.AbsolutePosition & "]" and then post what shows up in the Immediate window.

 

[supmktg]

As suspected, the absolute position changes but the label_value remains [1]?

Sup

 

[RuralGuy]

Hmmm...How about:
Debug.Print "Field0 [" & rst.Fields(0) & "] and Field1 [" & rst.Fields(1) & "]"

 

[boblarson]

Instead of using this:

Me("lbl" & rst!Label_value).Visible = True

Try using this:

Dim strLabelName As String

strLabelName = "lbl" & rst!Label_value
Me(strLabelName).Visible = True

 

[RuralGuy]

But the rst!Label_value field is not changing Bob. Something else is going on.

 

[boblarson]

Oh, I thought he had said the value was changing but wasn't displaying. I would think that the field name might not be correctly spelled then. Is the field name in the table really Label_value? Or maybe Label value with a space? Or maybe just Label?

 

[WayneRyan]

Hi all,

Can't resist this one.

The code looks OK.

The concept is hard to understand.
You have a table with Label (number) and Procedure (string):

Label Procedure
===== =========
1        A
2        B
3        B
4        C
5        A
6        A
7        C
.
.
.
31       A
32       B

If you selected 'B' in your combo, you'd expect to "See" --> Lbl2, Lbl3, and Lbl32
set .Visible = True

If that's the case, the code posted looks OK to me.

The only possible thing that I could see is that recordset data is sometimes
"right-padded" with spaces.

You might be trying to set "Lbl2 ".Visible = True

I don't think that would work too well.

As for looping through the recordset the correct number of times and seeing the same
value ... I don't think that's possible with what you have posted.

Can you post a sample?

Wayne

 

[supmktg]

OK, the code is correct!
I checked the data in the table. This is a linked sql server table. When I view the data directly on the server it is correct, but when I view it from my db, all of the label values are '1'.

I've deleted and relinked the table, and it's fixed. Here's what I think happened:
When I relinked the table just before the problem occured, I inadvertantly selected procedure as the unique identifier and that caused all of the label_values to be the same for each record with the same value. This time I clicked cancel to the unique identifier.

I'll add a unique ID to the table so this won't happen again!

Thank you for your help! I'm sorry I didn't check the table first.

Sup

 

[WayneRyan]

Sup,

Thanks for confirming that, it just didn't seem possible.

I've also seen some weird behaviour with SQL Server.

A scenario like query, then restore the DB, then requery and it's the same as
the original query. Not the restored DB.

Oh well, things aren't always as they seem.

See ya,
Wayne

 

[RuralGuy]

Glad to hear you got it sorted. Knew there had to be something else going on. Lots of success with the rest of the project.