I have a report that has two department fields. Depending on what is in an EquipID field, the appropriate department field needs to be visible. My code for the OnFormat of the detail section is as follows:

If EquipID = "misc" Then
Department.Visible = True
Dept.Visible = False
Else
Dept.Visible = True
Department.Visible = False
End If

This is not working. Whichever field I have on top is the only one that shows up. I have done this in another report the same way, but it doesn't seem to be working now. Any ideas?

I have a report that has two department fields. Depending on what is in an EquipID field, the appropriate department field needs to be visible. My code for the OnFormat of the detail section is as follows:

If EquipID = "misc" Then
Department.Visible = True
Dept.Visible = False
Else
Dept.Visible = True
Department.Visible = False
End If

This is not working. Whichever field I have on top is the only one that shows up. I have done this in another report the same way, but it doesn't seem to be working now. Any ideas?

I do not see anything wrong with your code unless it is because the Form Control names are being referenced in a different manner than I normally see them. I usually use a Form name along with the Control name when I refer to a Form Control in a VB Statement (either directly or via the "me." method). Something like:


If me.EquipID = "misc" Then
me.Department.Visible = True
me.Dept.Visible = False
Else
me.Dept.Visible = True
me.Department.Visible = False
End If


NOTE: I realized that you called the one Form Control "EquipID", but are treating it as a string (If me.EquipID = "misc"). Make sure that it is a string, becasue the results will not be correct unless it is.

This is not a form. It is a report. Do the same principles apply?

This is not a form. It is a report. Do the same principles apply?

What datatype your EquipID has ?

Also make sure that you are using the correct names for your controls. Otherwise your code seems ok

EquipID is an alphanumeric field. All the names are correct. I can't figure out why this is not working.

Try changing to:


If Me.EquipID = "misc" Then
Me.Department.Visible = True
Me.Dept.Visible = False
Else
Me.Dept.Visible = True
Me.Department.Visible = False
End If

Try changing to:


If Me.EquipID = "misc" Then
Me.Department.Visible = True
Me.Dept.Visible = False
Else
Me.Dept.Visible = True
Me.Department.Visible = False
End If


I pointed this out before (although I did not use the RED for emphasis like Bob did), but I was unable to provide a reason to change it that way. Is the Form Name necessary in a Form or Report when it is referring to a Form Control?

I did the Me. modifications, still not working. I am puzzled because I have done the very same thing on another report in the same database and it works just fine.

Is the Form Name necessary in a Form or Report when it is referring to a Form Control?
Actually, only really when the control name and field name are not named the same. If the control name is not named the same as the field name then I would change it to

Me!EquipID

to make sure it refers to the field.

Hey,

Bit late to join the chat, but I have just be trying to do something evry similar myself. Now this is a bit of a long shot, but for my report I wanted to check if a field was blank and if so use an alternative field, I couldn't jest change the control source of the text box as the two fields were different formats.

Anyway by original code was

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Startdate = vbNullString Then
Me.Startdate.Visible = False
Me.ExpStartDate.Visible = True
Else
Me.Startdate.Visible = True
Me.ExpStartDate.Visible = False
End If
End Sub

This did nothing! So what I did was change it to:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Startdate <> vbNullString Then
Me.Startdate.Visible = True
Me.ExpStartDate.Visible = False
Else
Me.Startdate.Visible = False
Me.ExpStartDate.Visible = True
End If
End Sub

And now it works. Anyway as I said a bit of a long shot but if you are still trying to solve this, try changing the = to <> and reverse the visibility. It worked for me, I have no idea why but it did.