Hiding certain fields in VBA Code (Microsoft Access)

Question

I have a problem with my Microsoft Access database where I've normalized some information and now it's not displaying correctly.

I calculate the fee based on a formula, but I need to hide 2 fields and display the accurate one based on FeeGroup.

I have written the following VBA code for the Report_Load event, however it doesn't appear to be working.

Private Sub Report_Load()

MsgBox "Case X"

Select Case Report_FeeGroupBox
    Case 1
        FullPrice_Months.Visible = False
        FullPrice_Weeks.Visible = False
        MsgBox "Case 1"
    Case 2
        FullPrice_Months.Visible = False
        FullPrice_Days.Visible = False
        MsgBox "Case 2"
    Case 3
        FullPrice_Days.Visible = False
        FullPrice_Weeks.Visible = False
        MsgBox "Case 3"
End Select

End Sub

How can I change it to update for each row in this report based on the Fee Group for that row?

Fee group works for days, weeks and months based on the number. I need to hide the other boxes if the fee group is not correct, basically all fee groups are displayed.

'Case X' displays as a message btw, so the callback does seem to work.

If that is a continuous form, you need to use conditional formatting. — Fionnuala
Thanks, but how can I apply VBA code with conditional formatting in this report? — nootskej
Have you thought of using a switch statement (or something similar) to get the fees all in one column, then display that one column? That way you don't have to worry about conditional formatting. — APrough
For more debugging options to find the problem yourself: If "Case X" shows but "Case 1" (or 2 or 3) don't than I would suggest you look at what the Report_FeeGroupBox value is. Just use a breakpoint and see what the code does at this point (and let us know). Might also be best to add a "Case Else". — Yoh

Mike1in3 Mike1in3 · Accepted Answer · 2014-05-20T03:55:49

You could use only one field to display the amounts, then set its recordsource to the appropriate table field in Report_Load.

Also, make sure you change the name for the text box to something other than the field name, or it will probably not work. Convention is to prefix a text box name with "txt".

Private Sub Report_Load()

MsgBox "Case X"

Select Case Report_FeeGroupBox
    Case 1
        txtFullPrice.RecordSource = "FullPrice_Days"
        MsgBox "Case 1"
    Case 2
        txtFullPrice.RecordSource = "FullPrice_Weeks"
        MsgBox "Case 2"
    Case 3
        txtFullPrice.RecordSource = "FullPrice_Months"
        MsgBox "Case 3"
End Select

End Sub

Hiding certain fields in VBA Code (Microsoft Access)

1 Answers