473,320 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 1532
ADezii
8,834 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,556 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,556 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

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

Similar topics

7
by: simonmarkjones | last post by:
Hi, I have a report i would like created based upon a date the user types in. I have a query which in the criteria section of my date field i have put . The report is created fine when a...
0
by: Mark Johnson | last post by:
Sometimes Controls that have been added to a GroupBox do not show up. What I am doing : 1) I am not using the designer, but create all the Controls per hand: groupBoxProdukt_01 = new...
2
by: Chris Kennedy | last post by:
We are just starting with asp.net and have just set up a test server. I can get my projects to work properly on my local machine. I try a test project on the test server, a label and a button which...
3
by: sorCrer | last post by:
Hi All, Posted after extensive searching! I have a nested repeater control as follows: (Simplified ;-)) <table> <asp:repeater id=parent onItemDataBound=createChild> <tr><td>Level...
3
by: jimfortune | last post by:
At what point is a Form added to the Forms collection or a Report added to the Report collection? I.e., listed as currently open. The reason I ask is that I have a subreport for an exclusive...
6
by: nairda | last post by:
Hi all, I'm making a sales report form that linked to my sales database (sales.mdb). I want to show the sales report in a MSHFlexgrid sorted by a period of time. I have these 2 combo boxes (Each...
0
by: voroojak | last post by:
Hi I have some problem with report service in sql 2005. what ia mdoing is that when the report is generated first just show some columns and if they wants to see the details they have to click on...
0
by: =?Utf-8?B?U2hhcm9u?= | last post by:
Hello Gurus, I need to create a Microsoft Visual Studio 2005 report at runtime. I wrote a C# window application, that holds a DataSet and several DataGridView controls. Each of the...
4
by: Trish | last post by:
Hello All and thanks in advance for your help. The code below works just fine in the Print event of the report Detail section. Is it possible to condense it using a loop or something? Can you...
3
by: muddasirmunir | last post by:
I am using vb6 and crystal report 10 for making reports. Now i am making a report , the report works fine just i want that the page number of the report start from the number which i give him. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.