469,125 Members | 1,629 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

How to choose which controls to show on report

31
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.Customer 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
Oct 11 '10 #1
4 1402
ADezii
8,800 Expert 8TB
In the Format() Event of the Detail Section, the Logic would be similar to the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2. Dim lngCustID As Long
  3.  
  4. lngCustID = Forms![<Form Name>]![Customer]
  5.  
  6. Select Case lngCustID
  7.   Case 1
  8.     Me![F1].Visible = True
  9.     Me![F2].Visible = False
  10.     Me![F3].Visible = True
  11.     Me![F4].Visible = False
  12.   Case 2
  13.     Me![F1].Visible = False
  14.     Me![F2].Visible = False
  15.     Me![F3].Visible = False
  16.     Me![F4].Visible = True
  17.   Case 3
  18.     Me![F1].Visible = True
  19.     Me![F2].Visible = True
  20.     Me![F3].Visible = True
  21.     Me![F4].Visible = False
  22.   Case Else     'Display all Fields for now
  23.     Me![F1].Visible = True
  24.     Me![F2].Visible = True
  25.     Me![F3].Visible = True
  26.     Me![F4].Visible = True
  27. End Select
  28. End Sub
Oct 11 '10 #2
NeoPa
32,161 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim fld As DAO.Field
  3.  
  4. ...
  5. For Each fld In rs
  6.   Me.Controls(fld.Name).Visible = fld
  7. Next fld
Oct 11 '10 #3
BarbQb
31
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 :)
Oct 12 '10 #4
NeoPa
32,161 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.   Dim rs As DAO.Recordset
  3.   Dim fld As DAO.Field
  4.  
  5.   Set rs = TableDefs("tblCustomers").OpenRecordset(dbOpenTable, dbReadOnly)
  6.   Call rs.FindFirst("[Cust] = " & Me.CustNo)
  7.   For Each fld In rs.Fields
  8.     If fld.Name <> "Cust" Then Me.Controls(fld.Name).Visible = fld
  9.   Next fld
  10.   Call rs.Close
  11. End Sub
Oct 12 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Mark Johnson | last post: by
2 posts views Thread by Chris Kennedy | last post: by
3 posts views Thread by sorCrer | last post: by
reply views Thread by =?Utf-8?B?U2hhcm9u?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.