Refer to multiple fields in VBA

[mrcost]

I am trying to format the first row of my report as currency (see custom format below), and keep the rest as standard. I found the below code to change one field (Est Contract), but does anyone know how can I change all fields on the report at once?

If CurrentRecord = 1 Then
Me![Est Contract].Format = "$ #,##0;($ #,##0);$ 0"
Else
Me![Est Contract].Format = "#,##0;(#,##0);0"
End If

Thanks,

Mrcost

 

[MarkK]

I would do this...

Private m_count As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   m_count = m_count + 1
   Select Case m_count
      Case 1
         SetDetailTextboxFormat "$ #,##0;($ #,##0);$ 0"
      Case 2
         SetDetailTextboxFormat "#,##0;(#,##0);0"
   End Select
End Sub

Private Sub SetDetailTextboxFormat(format As String)
   Dim ctrl As Control
   For Each ctrl In Me.Detail.Controls
      If ctrl.ControlType = acTextBox Then ctrl.format = format
   Next
End Sub

 

[mrcost]

Thanks lagbolt. Cool name.

Your code reformats all rows, including text boxes that need to stay as percentages. I changed your code to be as follows:

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

Row_count = CurrentRecord
Select Case Row_count
Case 1
SetDetailTextboxFormat "$ #,##0;($ #,##0);$ 0"
Case Else
SetDetailTextboxFormat "#,##0;(#,##0);0"
End Select


End Sub
Private Sub SetDetailTextboxFormat(format As String)
Dim ctrl As Control
For Each ctrl In Me.Detail.Controls
If ctrl.ControlType = acTextBox And ctrl.format <> "Percent" Then ctrl.format = format
Next
End Sub

I appreciate your quick response.

-Mrcost

 

[MarkK]

Very good. And just one small thing. Your code will run SetDetailTextboxFormat() for every record in the report, and this may not be necessary. That's why I just had 1 and 2 in the Select Case. Once the dollar signs are removed on the second line they may not need to be removed again. Not sure. If there's a performance penalty it'll be slight, but I also try to only run code if I must.

 

[mrcost]

Thanks. Maybe you can help with this one. Instead of showing zeros in the report, I put a formula in the detail section for one of fields:

=IIf([cost in excess]=0,"-",[cost in excess])

This gives me the desired result, but now that dollar sign VBA code we just did does not format the hyphen. I'd like it to show "$ - " when the resulting formula is zero.

Thanks,

Mrcost