Command buttons visible in subform on a per record bases

[peter_bradley]

Hi all,

Not sure if this is possible, but can I change the controls of a
command button on a per record bases in a subform. In my case, I would
like to make a command button visible only if certain fields in a table
are a certain value. Here is the subroutine I've written so far:

Public Sub IsButtonVisible()

'For Access, define some object variables and make connections.
Dim cn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rsRepeatInfo As New ADODB.Recordset
rsRepeatInfo.ActiveConnection = cnn
Dim btnRepeatInfo As AccessObject

'Declare variables
Dim strSQL As String
Dim strTestCode As String
Dim strSampleNumber As String
Dim strTestStatus As String

'Get the values from the subform
strTestCode = "='" & Me.txtTestCode.Value & "'; "
strSampleNumber = "='" & Me.txtSampleNumber.Value & "' "

'Build the SQL string
strSQL = "SELECT tblTests.[Test_Status] " & _
"FROM tblSamples INNER JOIN tblTests " & _
"ON tblSamples.[LSTS_Number] = tblTests.[LSTS_Number] " & _
"WHERE tblSamples.[LSTS_Number] " & strSampleNumber & _
"AND tblTests.[Test_Code]" & strTestCode

'Open the recordset
rsRepeatInfo.Open strSQL

'Check for end-of-file (EOF) in recordset
If rsRepeatInfo.EOF Then
strTestStatus = "SIL"
Else
strTestStatus = rsRepeatInfo("Test_Status")
End If

'Make "btnRepeatInfo" visible if "strTestStatus" = RT-SIL or RRT-SIL
If strTestStatus = "RT-SIL" Or strTestStatus = "RRT-SIL" Then
Me.btnRepeatInfo.Visible = True
Else
Me.btnRepeatInfo.Visible = False
End If

'Clear the object variables
rsRepeatInfo.Close
cnn.Close
End Sub
Note: 'Me' actually is 'Forms![frmSelectSet].frmSelectSample.Form' but
removed for clearity.
The above works but makes all the buttons visible/invisible.
Thanks in advance!!
PCB