Larry Biederman
asked on
Make Access Sub-report visible or not visible based on record number in detail section of main report
Hello Experts !!
I have several sub-reports all present with in the detail section of an Access report.
Some sub-reports need only show in even # records, and others only once (in detail record 1).
What VBA code can I use, and in which event do I do this?
Seems I need a counter (to count which detail record is current), and write code around this counter.
Any Ideas appreciated,
Larry
I have several sub-reports all present with in the detail section of an Access report.
Some sub-reports need only show in even # records, and others only once (in detail record 1).
What VBA code can I use, and in which event do I do this?
Seems I need a counter (to count which detail record is current), and write code around this counter.
Any Ideas appreciated,
Larry
ASKER
mbizup,
Thanks! Will give a try.
How does "mod" function?
-Larry
Thanks! Will give a try.
How does "mod" function?
-Larry
<<
How does "mod" function?
>>
Mod is an arithmetic function (which happens to be included in VBA) which will give you the remainder of division between two numbers.
So...
x mod y is the remainder from x/y
To test if a number is odd or even, you can divide it by two and see if there is a remainder
eg: 4/2 = 2 with a remainder of 0
5/2 = 2 with a remainder of 1
Since Mod gives you the remainder of the division:
4 Mod 2 = 0
5 Mod 2 = 1
And in general...
-- an even number mod 2 will give you 0
-- an odd number mod 2 will give you 1
(That's your math lesson for today :-) )
How does "mod" function?
>>
Mod is an arithmetic function (which happens to be included in VBA) which will give you the remainder of division between two numbers.
So...
x mod y is the remainder from x/y
To test if a number is odd or even, you can divide it by two and see if there is a remainder
eg: 4/2 = 2 with a remainder of 0
5/2 = 2 with a remainder of 1
Since Mod gives you the remainder of the division:
4 Mod 2 = 0
5 Mod 2 = 1
And in general...
-- an even number mod 2 will give you 0
-- an odd number mod 2 will give you 1
(That's your math lesson for today :-) )
ASKER
I'm get error on (Me.RecordID = 1)
Should it be substituted for and actual object (textbox etc)?
To make invisible on records 2 on should I use something like this:
if (Me.RecordID = 1) then
Me.MySubformControlName.Vi sible = true
else
Me.MySubformControlName.Vi sible = false
end if
Thanks, Larry
Should it be substituted for and actual object (textbox etc)?
To make invisible on records 2 on should I use something like this:
if (Me.RecordID = 1) then
Me.MySubformControlName.Vi
else
Me.MySubformControlName.Vi
end if
Thanks, Larry
ASKER
Thanks for math lesson.
I do remember mod from my vb6 days, but didn't know if use was different here.
Thanks!!
I do remember mod from my vb6 days, but didn't know if use was different here.
Thanks!!
>> Should it be substituted for and actual object (textbox etc)?
Not necessarily a textbox (although that will work too) ... but it does need to be a valid field name from the underlying table or query.
What is the actual name of the field that you are using to determine whether to hide/show the form? If you substitute that field name, it should work.
You'll also have to subsitute the correct name of your subform.
Your block of code for hiding records that are not record 1 looks good... you just need to subsitute the correct fieldname for RecordID and subform for MySubformControlName.
Not necessarily a textbox (although that will work too) ... but it does need to be a valid field name from the underlying table or query.
What is the actual name of the field that you are using to determine whether to hide/show the form? If you substitute that field name, it should work.
You'll also have to subsitute the correct name of your subform.
Your block of code for hiding records that are not record 1 looks good... you just need to subsitute the correct fieldname for RecordID and subform for MySubformControlName.
ASKER
Having Problems:
I'm using this code:
Private Sub Report_Current()
If Me.TxtNozzle1 = 1 Then
Me.ShellRpt_sub.Visible = True
Else
Me.ShellRpt_sub.Visible = False
End If
End Sub
TxtNozzle1 is a text box (located in details section) bound to a field in the query that feeds the details section of report. I have four records. The value of TxtNozzle1 will be 1,2,3,4 as each detail record is printed.
Problem 1: I have pressed an f9 on the first line of code (If....), so it will stop when this module executes. It never does !!
Problem 2: the value of Txtnozzle1 (when it appears on the printout is 4,8,12,16.
First problem is only one I'm concerned about, as I can use value 4, to make sub-report visible.
Any idea why "Private Sub Report_Current()" code never executes? It is located in main report, not sub-report
Thanks, Larry
I'm using this code:
Private Sub Report_Current()
If Me.TxtNozzle1 = 1 Then
Me.ShellRpt_sub.Visible = True
Else
Me.ShellRpt_sub.Visible = False
End If
End Sub
TxtNozzle1 is a text box (located in details section) bound to a field in the query that feeds the details section of report. I have four records. The value of TxtNozzle1 will be 1,2,3,4 as each detail record is printed.
Problem 1: I have pressed an f9 on the first line of code (If....), so it will stop when this module executes. It never does !!
Problem 2: the value of Txtnozzle1 (when it appears on the printout is 4,8,12,16.
First problem is only one I'm concerned about, as I can use value 4, to make sub-report visible.
Any idea why "Private Sub Report_Current()" code never executes? It is located in main report, not sub-report
Thanks, Larry
Yikes! I just realized we are talking about a report not a form.
In a report, use the Detail section's format event. Instead. If that doesn't help, post a sample database. Actual data is not important if it is sensitive.
In a report, use the Detail section's format event. Instead. If that doesn't help, post a sample database. Actual data is not important if it is sensitive.
ASKER
Thanks. Will try again in morning
ASKER
mbizup,
OK, got most of it working!
Another sub-report has recordset with 4 records. It has the same TxtNozzle1 column in each record. How do I use TxtNozzle1 as filter so sub-report selects only that row, each time another detail record is printed?
So, when detail record 1 is printed, it selects row where TxtNozzle1 =1.
When detail record 2 is printed, it selects row where TxtNozzle1 =2, etc.
I see the 'filter' and 'filter on load' properties, which may be useful.
Your thoughts....
Thanks, Larry
OK, got most of it working!
Another sub-report has recordset with 4 records. It has the same TxtNozzle1 column in each record. How do I use TxtNozzle1 as filter so sub-report selects only that row, each time another detail record is printed?
So, when detail record 1 is printed, it selects row where TxtNozzle1 =1.
When detail record 2 is printed, it selects row where TxtNozzle1 =2, etc.
I see the 'filter' and 'filter on load' properties, which may be useful.
Your thoughts....
Thanks, Larry
Hi -
I'm having a hard time picturing that one... can you maybe arrange your next post, using numbers, to visually/graphically show what you are trying to accomplish?
I'm having a hard time picturing that one... can you maybe arrange your next post, using numbers, to visually/graphically show what you are trying to accomplish?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mbizup,
thanks, I will give that idea a try today.
-Larry
thanks, I will give that idea a try today.
-Larry
ASKER
Master/child works!
Finally, in which procedure do I put code to make these sub-reports visible or not visible?
With that, I'll award you the points.
Thanks,
-Lbiederman
Finally, in which procedure do I put code to make these sub-reports visible or not visible?
With that, I'll award you the points.
Thanks,
-Lbiederman
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Its all working!
Now I just need a table to control which sub-report is visible or not visible.
Thanks Much,
Lbiederman
Now I just need a table to control which sub-report is visible or not visible.
Thanks Much,
Lbiederman
The VBA to display a subform on even records :
Open in new window
Or for just the first record:
Open in new window
etc...