How to make only selected fields display in a form or report.
Q: Martin writes: I’m working on a function that should not display the field if it is not picked, but my code (below) does not work:
Private Sub picEpl()
Dim picEpl
If Me![picEpl.Visible] = 0 Then
picEpl.Visible = False
Else: picEpl.Visible = True
End If
End Sub
picEpl = field where you make a selection (yes/ no), in this case pick between some yes/no markers.
A: It looks as if you are using picEpl as a control name, a field name and a variable name, which is causing reference errors. If you use a naming convention, then each type of object has a specific prefix (tag) to avoid reference errors. If there is a Yes/No field (perhaps with a checkbox bound to it), and an Image control that should be displayed only if the checkbox value is True, then you can do this either with Conditional Formatting (in design view, for a form or report control) or in code, say on a report’s Detail_Format section, like the code below:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me![chkTest] = True Then
Me![imgGoldStar].Visible = True
Else
Me![imgGoldStar].Visible = False
End If
End Sub
My LNC Rename add-in (available from my Web site as Code Sample #10) can be helpful in applying a consistent naming convention to a database.
If you are trying to make a control visible or invisible depending on its own value, then you could use a procedure like the one below (called from the Form_Current event and the AfterUpdate event of the checkbox), where Preferred is the Yes/No field, and chkPreferred is the checkbox bound to it:
Private Sub ShowHideCheckbox()
On Error GoTo ErrorHandler
‘Set focus on another control, since you can’t make the control that
‘has the focus invisible
Me![txtFax].SetFocus
If Me![Preferred] = True Then
Me![chkPreferred].Visible = True
Else
Me![chkPreferred].Visible = False
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
Private Sub chkPreferred_AfterUpdate()
Call ShowHideCheckbox
End Sub
Private Sub Form_Current()
Call ShowHideCheckbox
End Sub
Bear in mind, though, that once you have made the checkbox invisible, you can’t later make it visible by checking it again, so you might want to make another control visible or invisible instead, based on the value in the checkbox’s bound field.
