How to choose which controls to show on report

[BarbQb]

Hi All. I am sorry if this has been asked before.

I have a form with a Customer ComboBox and a few other fields and I would like to be able to choose which controls are shown on the report based on the different choice of Customer.

The report is based off a select query.

Not all customers require the same data to be shown in the report. Is there a way to only show what is needed with VBA?

ComboBox1
Select tblCustomers.Cu stomer From tblCustomer

tblCustomers is a Y/N table that tells the user what fields are required for each customer.

Cust F1 F2 F3 F4
1 y n y n
2 n n n y
3 y y y n

[ADezii]

In the Format() Event of the Detail Section, the Logic would be similar to the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngCustID As Long

lngCustID = Forms![<Form Name>]![Customer]

Select Case lngCustID
  Case 1
    Me![F1].Visible = True
    Me![F2].Visible = False
    Me![F3].Visible = True
    Me![F4].Visible = False
  Case 2
    Me![F1].Visible = False
    Me![F2].Visible = False
    Me![F3].Visible = False
    Me![F4].Visible = True
  Case 3
    Me![F1].Visible = True
    Me![F2].Visible = True
    Me![F3].Visible = True
    Me![F4].Visible = False
  Case Else     'Display all Fields for now
    Me![F1].Visible = True
    Me![F2].Visible = True
    Me![F3].Visible = True
    Me![F4].Visible = True
End Select
End Sub

[NeoPa]

If the fields in the table are named the same as the controls on the form/report, then simply use a For Each fld In rs.Fields loop in your code to process each of the Fields in the recordset to set the .Visible property of the control to the value of the field :

Dim rs As DAO.Recordset
Dim fld As DAO.Field

...
For Each fld In rs
  Me.Controls(fld.Name).Visible = fld
Next fld

[BarbQb]

Thanks ADezii and NeoPa.

NeoPa - where would I put the code in?
I'm also not really sure what you mean by "loop in my code," I am still pretty new to VBA, so if you could be specific I would appreciate it :)

[NeoPa]

I don't know your project so I can only approximate to what you'd need. It should illustrate the sort of code you'll want though. I hope it helps :

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field

  Set rs = TableDefs("tblCustomers").OpenRecordset(dbOpenTable, dbReadOnly)
  Call rs.FindFirst("[Cust] = " & Me.CustNo)
  For Each fld In rs.Fields
    If fld.Name <> "Cust" Then Me.Controls(fld.Name).Visible = fld
  Next fld
  Call rs.Close
End Sub