VBA Code for Access reports using variables

[Fbobak]

I've tried looking through the threads and found a few that were close. I have a query that contain multiple records that have multiple expense field such as AdminFee, LegalFee, MgmtFee etc. (there are 56 total). When I run the report if the user chooses just to see the AdminFee that's all I want to see on the report. I know it's a variable and I've used AdminFee.value, Adminfee.controlsource, AdminFee = varAdminFee. I've gotten so many error message I don't even remember half of them.

Any help would be greatly appreciated.

Thanks

 

[Andrzejek]

Unfortunately, I have to deal with what I have.

True, but...
You could make your life a lot easier by creating a few queries to get your data normalized and use MajP's advise from the post on 5 Jun 17 22:19

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Fbobak]

Thanks MajP, I'd appreciate it.

MajP and Andrezejek, I'll try both suggestions.

 

[MajP]

This is how you do it.
Have a form with a comobbox that has all 56 fees in it. For the demo I used a field list, but you could put the names into a table.

The form passes the fee name to the report

Private Sub cmdOk_Click()
  DoCmd.OpenReport "RptFees", acViewPreview, , , acDialog, Me.cmboFee
End Sub

When the report opens it sets the controlsource and label caption of the fee field

Private Sub Report_Open(Cancel As Integer)
  On Error GoTo errlbl
  If Not Trim(Me.OpenArgs & " ") = "" Then
    Me.txtFee.ControlSource = Me.OpenArgs
    Me.lblFee.Caption = Me.OpenArgs
  End If
  Exit Sub
errlbl:
  MsgBox Err.Number & ": " & Err.Description
End Sub

see demo.

 

[Fbobak]

MajP, thanks for the code. I'm going to give that a try. Since I'm under deadline I went another route, very messy but it's gets the job done for now.