Loop to set value of command button based on field value.

[aJay]

Hi,

What I am trying to achieve is the following:

A form has 24 command buttons related to 24 network ports. If the port
is shown as 'Connected', then its command button is shown. If the port
is not shown as 'Connected', then its command button is transparent.

---------------------
testport3 = DCount("[Port Number]", "[ports]", "[Switch ID] Like
switchid AND [Port Number] Like '3' AND [Connected?] = true")
If testport3 = 1 Then
port3.Transparent = False
Else
port3.Transparent = True
End If
---------------------

That is what I currently have, but for every single port. The form is
slow to load up, and I'm wondering if there is an easier and quicker
way to do this!

Thanks in advance,
Ally

 

[Guest]

Ally:

You could iterate through the rows in the Ports table in the form's Open
event procedure and hide/show each button as appropriate by setting the each
button's property's value to that of the current row's Connected? column.
I'm assuming the buttons are named Port1 – Port24, so the code would go like
this:

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim intPortNum As Integer

strSQL = _
"SELECT [Port Number], [Connected?] " & _
" FROM Ports"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=OpenForwardOnly

With rst
Do While Not .EOF
intPortNum = .Fields("[Port Number]")
Me("Port" & intPortNum).Visible = .Fields("[Connected?]")
.MoveNext
Loop
End With

Note that I've used the Visible rather than Transparent property in the
above to hide the button. Even better, I'd think, would be to use the Enabled
property. This shows the button greyed out and makes it unavailable, i.e.

Me("Port" & intPortNum).Enabled = .Fields("Connected")

Ken Sheridan
Stafford, England