Code in report stopping it from running. (1 Viewer)

[Linda1503]

Hi, I'm a coding absolute rookie and am trying to make 2 info fields visible/invisible depending on the value in a 3rd field.
The code has compiled but it's not working - is there something else I need in there?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Text99 = 1 Then
Me.[Collection Info].Visible = False
Else
Me.[Delivery Info].Visible = True
End If

If Me.Text99 = 2 Then
Me.[Collection Info].Visible = False
Else
Me.[Delivery Info].Visible = True
End If

If Me.Text99 = 3 Then
Me.[Delivery Info].Visible = False
Else
Me.[Collection Info].Visible = True

End If


End Sub

 

[theDBguy]

Hi. Is that code in a report? If so, are you viewing it in Report View or Print Preview?

 

[Gasman]

I cannot see any reason?

 

[Linda1503]

Hi. Is that code in a report? If so, are you viewing it in Report View or Print Preview?

Yes the code is in the detail section of the report in the On Format Event - so not exactly sure ... image might help?

 

[Linda1503]

The message box says "A custom macro" rather than code but when I take the code out the report generates fine...

 

[Linda1503]

Hi. Is that code in a report? If so, are you viewing it in Report View or Print Preview?

Sorry - should have added the report is called from a button with macro below...

 

[isladogs]

Your screenshot shows the report is opened in Report view. Formatting only works in Print Preview or when printed

 

[theDBguy]

Sorry - should have added the report is called from a button with macro below... View attachment 94729

Try changing the "View" argument from Report to Print Preview.

 

[Pat Hartman]

Your conditions are not mutually exclusive as they are written. Each runs in turn and only the last one will actually do what you want. You need to use a Case statement. I couldn't work out the logic based on your three conditions but the case would look like the following example. For each case you need to set the visible property for one control and hide the other. I'm guessing that this is your intention.

In the future, try to remember to give a control a rational name before writing code to reference it. Knowing what text99 is would be very helpful to someone reading this code in the future. Perhaps even you.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.Text99
    Case = 1,2
        Me.[Collection Info].Visible = False
        Me.[Delivery Info].Visible = True
    Case = 3         
        Me.[Collection Info].Visible = True
        Me.[Delivery Info].Visible = False
    Case Else
        ??
End Select

 

[arnelgp]

maybe change the Forecolor only:

If Me.Text99 = 1 Then
Me.[Collection Info].Forecolor = vbwhite
Else
Me.[Delivery Info].Forecolor = vbBlack
End If

If Me.Text99 = 2 Then
Me.[Collection Info].ForeColor = vbWhite
Else
Me.[Delivery Info].ForeColor = vbBlack
End If

If Me.Text99 = 3 Then
Me.[Delivery Info].ForeColor = vbWhite
Else
Me.[Collection Info].ForeColor = vbBlack

End If

 

[Linda1503]

Your conditions are not mutually exclusive as they are written. Each runs in turn and only the last one will actually do what you want. You need to use a Case statement. I couldn't work out the logic based on your three conditions but the case would look like the following example. For each case you need to set the visible property for one control and hide the other. I'm guessing that this is your intention.

In the future, try to remember to give a control a rational name before writing code to reference it. Knowing what text99 is would be very helpful to someone reading this code in the future. Perhaps even you.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.Text99
    Case = 1,2
        Me.[Collection Info].Visible = False
        Me.[Delivery Info].Visible = True
    Case = 3        
        Me.[Collection Info].Visible = True
        Me.[Delivery Info].Visible = False
    Case Else
        ??
End Select

Thank you Pat - makes sense & sorry for delayed response...
Now getting Run-time error '2427': You entered an expression that has no value...
I can see the value in the filtered report but I'm obviously missing something - Arrrgh

 

[Pat Hartman]

Do you have "Option Explicit" set at the top of all your code modules? This requires variables to be defined at compile time. Are you sure that Me.Runtype is the name of a field or control on your form?

 

[Linda1503]

Do you have "Option Explicit" set at the top of all your code modules? ...... I do now!
RunType is the name of a text box on the form & I refer to it as =[Forms]![DownstairsOrders]![RunType] on the report.

 

[Linda1503]

Do you have "Option Explicit" set at the top of all your code modules? This requires variables to be defined at compile time. Are you sure that Me.Runtype is the name of a field or control on your form?

but I'm still getting: You entered an expression that has no value.

 

[Gasman]

You are using Me. which is referring to the object the code is running in, your report?
Try

Dim iRunType as Integer
iRunType = =[Forms]![DownstairsOrders]![RunType]
Select Case iRunType

or Me.Whatever the name of the control on the report is ?

 

[Pat Hartman]

Is the control empty? Is the form closed?

Instead of using Me. to reference your control, try directly referencing the form in the code using Forms!....

 

[Linda1503]

Is the control empty? Is the form closed?

Instead of using Me. to reference your control, try directly referencing the form in the code using Forms!....

The control wasn't empty and the form was open but..... it worked!!! Yeh hey hey heyyyyy! Thank you! XXXXX
Honest will try to figure out why so I do learn from it (I hope :-/)

 

[Linda1503]

You are using Me. which is referring to the object the code is running in, your report?
Try

Dim iRunType as Integer
iRunType = =[Forms]![DownstairsOrders]![RunType]
Select Case iRunType

or Me.Whatever the name of the control on the report is ?

Thank you

 

[Pat Hartman]

You're welcome I think