Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Feb 19, 2022

Bring to Front and Turn Visible On - Report

Hello Experts,

 

I have been trying to get this one field on my report to turn visible on and bring to front if another field is Null or "". 

The below is what I have and it doesnt seem to do anything.  The field names are correct.  
I also need to add (I think):

DoCmd.RunCommand acCmdBringToFront

DoCmd.RunCommand acCmdSendToBack

 

Where have I made the mistake? 

thank you

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Nz(Me.txtAcctNo, 0) = "" Then 
     Me.txtAcctNoFront.Visible = True
Else
     Me.txtAcctNoFront.Visible = False
End If

End Sub

 

access
  • isladogs's avatar
    isladogs
    Feb 19, 2022

    Tony2021 

    Format events only work in Print Preview
    Can't see anything obvious that's wrong....

     

    Are both controls on the detail section?

    Try the opposite logic. Make txtAcctNoFront visible by default then use this code

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me.txtAcctNo, "") <> "" Then Me.txtAcctNoFront.Visible = False
End Sub

     

     

  • isladogs's avatar
    isladogs
    MVP
    Feb 19, 2022

    Tony2021 

    One obvious problem is with the Nz expression

    If its a text field, use:

    If Nz(Me.txtAcctNo, "") = "" Then

    but if its a number field, then you need

    If Nz(Me.txtAcctNo, 0) = 0 Then

     

    Form your description, your field is normally hidden so you can place it at the front in design view. No need to shift from back to front. 

    As its normally hidden the else part is superfluous, so you can simplify to (assuming a number field):

     

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Nz(Me.txtAcctNo, 0) = 0 Then Me.txtAcctNoFront.Visible = True

End Sub

     

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Feb 19, 2022
      Hi, thanks for the response. I am still having an issue unfortunately. The hidden field txtAcctNoFront doesnt appear. I have it placed in the front and not behind any other fields.


      The property of the field named txtAcctNo is TEXT since there are letters sometimes.

      Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      If Nz(Me.txtAcctNo, "") = "" Then Me.txtAcctNoFront.Visible = True
      End Sub


      I have the following in the field txtAcctNoFront (it performs as expected):
      =IIf(Nz([txtAcctNo],"")="","**Chk IBAN**",Null)

      Let me know if I am doing something wrong. thank you.
      I tried both Print Preview and Report View thinking there is possibly a difference.
      • isladogs's avatar
        isladogs
        MVP
        Feb 19, 2022

        Tony2021 

        Format events only work in Print Preview
        Can't see anything obvious that's wrong....

         

        Are both controls on the detail section?

        Try the opposite logic. Make txtAcctNoFront visible by default then use this code

        Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me.txtAcctNo, "") <> "" Then Me.txtAcctNoFront.Visible = False
End Sub

         

         

Resources