Multiple CommandButtons set as Variables 2

[kentover]

I have a form with 20 buttons. I get the number of unique values for a field, make the same number of buttons visible, and set the caption to the button number. The button names are "btn1", through "btn20" I will use this to change the subform between values. I have tried many ways to get this to work but VB does not like my commandbutton variable as a string or variant. "Set bttn = varTB" is the line that gives the error "Object Required".

Below is the code:

Dim strArea As String
Dim intX As Integer
Dim strBtn As String
Dim varTB As Control
Dim bttn As New CommandButton
Dim strCnt As String
Dim rsCnt As Recordset

strArea = Me.Combo6.Value
Me.Filter = "SftCstItm = '" & strArea & "'"
Me.FilterOn = True
Me.Requery

'Set bttn.Name = Me.btn1.Name

intX = 1

Do Until intX = 21
varTB = ("Me.btn" & intX)
Set bttn = varTB
bttn.Visible = False
intX = intX + 1
Loop

'Get number of FTEs for Area
strCnt = "SELECT qryResAreaFrm.FTE " & _
"FROM qryResAreaFrm " & _
"WHERE qryResAreaFrm.SftCstItm = '" & strArea & "' " & _
"GROUP BY qryResAreaFrm.FTE;"
Set rsCnt = CurrentProject.Connection.Execute(strCnt)

If rsCnt.EOF Then
Exit Sub
Else
intX = 1
rsCnt.MoveFirst
Do Until rsCnt.EOF
varTB = "Me.btn" & intX
Set bttn = varTB
With bttn
.Visible = True
.Caption = intX
End With
intX = intX + 1
Loop
End If


Thank you in advance for your help,

 

[kentover]

The line "Dim varTB As Control should have been posted as "Dim varTB As Variant".

 

[PaulF]

I'm pretty sure you don't need the Set command, or the bttn variable, all you need is the Control variable

Do Until intX = 21
varTB = "btn" & intX
Me(varTB).Visible = False
intX = intX + 1
Loop

Likewise:

Do Until rsCnt.EOF
varTB = "btn" & intX
me(varTB).Visible = True
me(varTB).Caption = intX
intX = intX + 1
Loop

PaulF

 

[kentover]

Thanks for the info. I tried your suggestion with several different syntaxes and I seem to get the error

Runtime Error 438
"Object doesn't support this property or method"

I have the variable varTB declared as a control

Current code is:
Do Until intX = 21
varTB = ("btn" & intX)
Me(varTB).Visible = False
intX = intX + 1
Loop

Any suggestions?

Thanks again,

 

[PaulF]

this code worked in a test form

Dim vartb
Dim intx As Integer
intx = 1
Do Until intx = 21
vartb = "btn" & intx
Me(vartb).Visible = False
intx = intx + 1
Loop

not required to Set anything

PaulF

 

[willir]

kentover

When I reviewed your diaglog about the code working for one system and not another, I had a sneaking suspicion ... I bounced over to Microsoft KBase and dug this up for you...

http://support.microsoft.com/default.aspx?scid=kb;en-us;200271

Depending on your OS, you are looking at installing / reinstalling a service pack. You may also consider going to their update site, and run the update. In the suggestion of updates MSN will make, look for DCOM, and perhaps MDAC. (While you are at it, you may want to install a few security updates -- there has been a few of them published lately.)

Good luck
Richard