If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to add visible criteria to text box?
Hi there, similar questions have been asked before but I haven't found
a good solution yet. I have a report based on a query, which will show 10 columns. Some of those columns at, however, might be empty, which in that case I don't want to show. I also don't want to regenerate the report everytime, so I need the report to be able to handle any situation (for example if only 1 column is empty or 4 columns are empty, etc, etc). 1) How do I add a visible criteria to the text box? 2) Is there a way I can adjust the text boxes so they automatically shift over to the left when the unused text boxes do not show? |
#2
|
|||
|
|||
How to add visible criteria to text box?
You could add a text box to the report header that counts the values in a
column. If the count is 0 for Column3 then you could set the left property of columns 4-10 accordingly. This would require a fair amount of code, some handy naming conventions or tag properties, and a little math. -- Duane Hookom MS Access MVP -- "ida" wrote in message oups.com... Hi there, similar questions have been asked before but I haven't found a good solution yet. I have a report based on a query, which will show 10 columns. Some of those columns at, however, might be empty, which in that case I don't want to show. I also don't want to regenerate the report everytime, so I need the report to be able to handle any situation (for example if only 1 column is empty or 4 columns are empty, etc, etc). 1) How do I add a visible criteria to the text box? 2) Is there a way I can adjust the text boxes so they automatically shift over to the left when the unused text boxes do not show? |
#3
|
|||
|
|||
How to add visible criteria to text box?
Would this code be in the report itself?
What commands should I research for setting the "left" property? |
#4
|
|||
|
|||
How to add visible criteria to text box?
To move controls in the detail section, you would need to run code in the On
Format event of the detail section. You would probably create some memory variables for each column that get set in the On Format of the report header. It might be important to name your text boxes like text1, text2,.... -- Duane Hookom MS Access MVP -- "ida" wrote in message ups.com... Would this code be in the report itself? What commands should I research for setting the "left" property? |
#5
|
|||
|
|||
How to add visible criteria to text box?
Thanks Duane for the help. Unfortunately, I can't seem to find the ON
FORMAT option when I right click on a text box in the design view of a report. Am I accessing it incorrectly? Or do I directly code into the report? |
#6
|
|||
|
|||
How to add visible criteria to text box?
Ignore the previous post, I found what you were talking about. I am
going to try to find out what commands are needed to set properties of specific text boxes. ida wrote: Thanks Duane for the help. Unfortunately, I can't seem to find the ON FORMAT option when I right click on a text box in the design view of a report. Am I accessing it incorrectly? Or do I directly code into the report? |
#7
|
|||
|
|||
How to add visible criteria to text box?
First, I would like to go on record as suggesting that if you have columns
with some being all null/empty, I would question your table structure. I don't think this sounds normalized. However, since this is the Thanksgiving season, try this solution. Making some assumptions: -your columns are all the same width Labels and text boxes -You have a Report Header section with text boxes for each column. Names: txtCount1 - txtCount10 Control Source: = Count([Field1]) to =Count([Field10]) -The column labels are in the Page Header Names: Label1 to Label10 -The text boxes in the detail section Names: txt1 to txt10 -You add a text box to the detail section Name:txtRunningSum Control Source: =1 Running Sum: Over All Your code would look like: Private Sub Detail_Format( _ Cancel As Integer, FormatCount As Integer) Dim i As Integer Dim j As Integer Dim intCtlWidth As Integer If Me.txtRunningSum = 1 Then intCtlWidth = Me.txt1.Width For i = 10 To 1 Step -1 If Me("txtCount" & i) = 0 Then Me("txt" & i).Visible = False For j = i To 10 Me("txt" & j).Left = _ Me("txt" & j).Left - _ intCtlWidth Next End If Next End If End Sub Private Sub PageHeaderSection_Format( _ Cancel As Integer, FormatCount As Integer) Dim i As Integer Dim j As Integer Dim intCtlWidth As Integer intCtlWidth = Me.txt1.Width For i = 10 To 1 Step -1 If Me("txtCount" & i) = 0 Then Me("Label" & i).Visible = False For j = i To 10 Me("label" & j).Left = _ Me("Label" & j).Left - _ intCtlWidth Next End If Next End Sub -- Duane Hookom MS Access MVP "ida" wrote in message oups.com... Ignore the previous post, I found what you were talking about. I am going to try to find out what commands are needed to set properties of specific text boxes. ida wrote: Thanks Duane for the help. Unfortunately, I can't seem to find the ON FORMAT option when I right click on a text box in the design view of a report. Am I accessing it incorrectly? Or do I directly code into the report? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Is Access even the right idea? | BMB | New Users | 19 | November 21st, 2005 08:01 PM |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Access reports with a horizontal line after each record??? | Bill via AccessMonster.com | Setting Up & Running Reports | 6 | March 9th, 2005 04:51 PM |
Outline | Renee Hendershott | Page Layout | 2 | December 25th, 2004 02:49 PM |
Concatenatd fields in a query for a searching form | Marc | Running & Setting Up Queries | 8 | October 19th, 2004 08:49 PM |