Bring to Front and Turn Visible On - Report

[Tony2021]

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

[#2]

@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

[#3]

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.

[#4 · Accepted solution]

@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

[#5]

bingo! that was it. thank you.

[#6]

You're welcome. Good luck with your project