counting details in report and hiding fields 1

[irethedo]

I have 2 reports questions:

1. I would like to have a field in the detail section of a report which displays the sequence number (seq #) of the item in the detail section. For example:

=====================
seq # Item
1 apples
2 oranges
3 pears
=====================

I set up the count function on one of the fields in the query for this report but it displays "1" for each seq. # listed in the detail section. How can I set this up so that it displays in the example above?

2. I have many fields on my report but this report is to be filled out by forms in the database as certain work operations are completed and entered into the database. How can I only dislay the information for the fields that have been populated on the report and hide all of the ones that are not yet populated?

In other words, instead of displaying:
==================================================
seq # | Item | picking | washing | sorting | packaging
--------|---------|---------|-----------|----------|---------
1 | apples | Yes | | |
--------|---------|---------|-----------|----------|----------
2 |oranges| Yes | Yes | |
--------|---------|---------|-----------|----------|----------
3 |pears | Yes | Yes | |
--------|---------|---------|-----------|----------|----------
==================================================

I wish to display:

========================================
seq # | Item |picking | washing
--------|---------|---------|---------|
1 | apples | Yes | |
--------|---------|---------|---------|
2 |oranges| Yes | Yes |
--------|---------|---------|---------|
3 |pears | Yes | Yes |
--------|---------|---------|---------|

========================================

Any strategies on how to hide the unused fields and boxes?

Thanks

 

[mp9]

One way is to add this code to the report's module:

Dim lngCount As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    lngCount = lngCount + 1
    Text1 = lngCount
End Sub

Where Text1 is an unbound text box in the detail of the report.

Another is to modify the recordsource of the report to have a sequence number field in, using a query to replicate a rownum which could then be included on the report - an example of how this can be done is shown at

http://www22.brinkster.com/accessory/forum/topic.asp?TOPIC_ID=4

 

[irethedo]

Thanks MP9

I chose to deal with this another way after giving it a little bit more thought by adding this number to the table when I create the records. I do like the way that you suggest this and I will definitely keep your suggestion in mind for a future project for sure!

The second part of my original post deals with displaying or hiding certain parts of the form if certain fields are not needed. This part of the form contains text, text boxes, & drawn boxes. I found that I can use a conditional format for the text boxes but these are not available for the text boxes or the text. Can I disable these through code somehow?

 

[mp9]

Yes, in the OnFormat event of your Detail section...

ControlNameHere.Visible = (YourConditionHere)

E.g.

Text1.Visible = (Len(Text1) > 0)

So in this example if the contents of Text1 are longer than 0 the Text1 text-box is displayed, otherwise it's hidden. This can be easily modified with different conditions on the right hand side of the equation. This all works because the bracketed right-hand side eva;uates to either True or False, which is then used to set the control's Visible property.

 

[irethedo]

Thanks MP9-

I am trying to set these based upon the contents of a field on the form or a string in the query but with no success:

(the top line is commented out but it didn't work either...)
--------------------------------------------------------
' If Me.Op6 = IsNull Then
If [Me!Operation6] = False Then
Op6.Visible = False
Op6Init.Visible = False
Op6date.Visible = False
op6line.Visible = False
op6bline.Visible = False
op6head.Visible = False
op6box.Visible = False
Else
Op6.Visible = True
Op6Init.Visible = True
Op6date.Visible = True
op6line.Visible = True
op6bline.Visible = True
op6head.Visible = True
op6box.Visible = True
End If
-----------


Also once I can figure out how to do this I was wondering if this can be condensed a little bit as I have 7 controls that I need to perform this on. What I was wondering was if the following was possible (I am not sure of the syntax..)
=====================================
for loop = 1 to 7
If "Op" & loop" = IsNull Then
"Op" & loop".Visible = True
"Op" & loop & Init".Visible = True
"Op" & loop & date".Visible = True
"Op" & loop & line".Visible = True
"Op" & loop & bline".Visible = True
"Op" & loop & head".Visible = True
"Op" & loop & box".Visible = True
Else
"Op" & loop".Visible = False
"Op" & loop & Init".Visible = False
"Op" & loop & date".Visible = False
"Op" & loop & line".Visible = False
"Op" & loop & bline".Visible = False
"Op" & loop & head".Visible = False
"Op" & loop & box".Visible = False
EndIf
=======================

Is this do-able?

 

[mp9]

Try

If IsNull(Me.Op6) Then ...

Or

If Nz(Me.Op6,"") = "" Then ...

Also, for conditionally formatting lots of controls fo different names/types, take a look at

http://www22.brinkster.com/accessory/forms/002.shtml

 

[irethedo]

Awesome MP9!

Thank you very much

the IfISNull() did the trick!

 

[irethedo]

Thanks for the link MP9-

I as reading it over and it might work well for making all fields visible or not at a time but it doesn't look like it can be made to be as selectable as I need for my reort.

That is why I was wondering if it is possible to trick the visible command and control names by a little string manipulation to cut down on a large number of lines of code as in my revious example shown below... Is there aa way to make this work if the string syntax is fixed?

============

for loop = 1 to 7
If "Op" & loop" = IsNull Then
"Op" & loop".Visible = True
"Op" & loop & Init".Visible = True
"Op" & loop & date".Visible = True
"Op" & loop & line".Visible = True
"Op" & loop & bline".Visible = True
"Op" & loop & head".Visible = True
"Op" & loop & box".Visible = True
Else
"Op" & loop".Visible = False
"Op" & loop & Init".Visible = False
"Op" & loop & date".Visible = False
"Op" & loop & line".Visible = False
"Op" & loop & bline".Visible = False
"Op" & loop & head".Visible = False
"Op" & loop & box".Visible = False
EndIf

============

 

[mp9]

I think you could maybe experiment with the Eval() function to do this?