By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Make button visible/invisible on a per record bases in subform

P: n/a
PCB
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. The buttons are adjacent to each record (i.e. - if
there are 5 records, there would be 5 buttons; 10 records, 10 buttons;
etc.) 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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You cannot selectively make controls visible/invisible in a continuous form
or datasheet.

In Access 2000 and latter, you may be able to use Conditional Formatting to
simulate the effect of disabled buttons. I believe Stephen Lebans has the
code to do that at:
www.lebans.com

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PCB" <pe***********@sympatico.ca> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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. The buttons are adjacent to each record (i.e. - if
there are 5 records, there would be 5 buttons; 10 records, 10 buttons;
etc.) 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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.