where_condition in Sub-Reports !!

[Prakash Wadhwani]

I have a main Report with 3 sub-reports eg:

Report1 (Main Report) ... SubRpt1, SubRpt2, SubRpt3
SubRpt1, SubRpt2 & SubRpt3 are bound to 3 different tables with a
common relation on a NUMERIC field called Service_Ticket_No
(Ser_Tkt_Num).

The Main Report is not bound to any table but I could if required.
I have a similar FORM setup - Main Form + 3 Subforms. This form has a
PRINT button. When the user clicks that print button I would like to
print only the Service ticket he is positioned on. This is my code:

==== Code Snippet ========
Private Sub Cmd_Print_Click()
On Error GoTo Err_Cmd_Print_Click

Dim stDocName As String
Dim where_condition As String

stDocName = "Report1"
where_condition = "Ser_Tkt_Num = " & Me.Ser_Tkt_Num

With DoCmd
.OpenReport stDocName, acPreview, , where_condition
.Maximize
.RunCommand acCmdFitToWindow
End With
Exit_Cmd_Print_Click:
Exit Sub

Err_Cmd_Print_Click:
MsgBox Err.Description
Resume Exit_Cmd_Print_Click

End Sub
==== End of Code Snippet ============

My Problem:
~~~~~~~~~~
All the service tickets print.
I presume the parameter (Ser_Tkt_Num) is not visible to the
subreports. How can I accomplish this ?
Thks & Best Rgds,
Prakash.

[Allen Browne]

The WhereCondition is applied to the main report.
Since that is unbound, it hs no effect.

The simplest way to do this would be to add a text box to the main report,
and set its Control Source to:
[Forms]![YourForm]![Ser_Tkt_Num]

Then in the LinkMasterFields of your subreports, enter the name of the text
box. In the LinkChildFields, enter the name of the matching field (the
foreign key) in each subreport.

Another way to achieve the same result would be to include
=[Forms]![YourForm]![Ser_Tkt_Num]
in the Critieria of the queries (under the foreign key field).

A third alternative would be to use the Report_Open event of the subreports
to set their Filter property so they only show the desired records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Prakash Wadhwani" <si****@omantel.net.om> wrote in message
news:60**************************@posting.google.c om...

I have a main Report with 3 sub-reports eg:

Report1 (Main Report) ... SubRpt1, SubRpt2, SubRpt3
SubRpt1, SubRpt2 & SubRpt3 are bound to 3 different tables with a
common relation on a NUMERIC field called Service_Ticket_No
(Ser_Tkt_Num).

The Main Report is not bound to any table but I could if required.
I have a similar FORM setup - Main Form + 3 Subforms. This form has a
PRINT button. When the user clicks that print button I would like to
print only the Service ticket he is positioned on. This is my code:

==== Code Snippet ========
Private Sub Cmd_Print_Click()
On Error GoTo Err_Cmd_Print_Click

Dim stDocName As String
Dim where_condition As String

stDocName = "Report1"
where_condition = "Ser_Tkt_Num = " & Me.Ser_Tkt_Num

With DoCmd
.OpenReport stDocName, acPreview, , where_condition
.Maximize
.RunCommand acCmdFitToWindow
End With
Exit_Cmd_Print_Click:
Exit Sub

Err_Cmd_Print_Click:
MsgBox Err.Description
Resume Exit_Cmd_Print_Click

End Sub
==== End of Code Snippet ============

My Problem:
~~~~~~~~~~
All the service tickets print.
I presume the parameter (Ser_Tkt_Num) is not visible to the
subreports. How can I accomplish this ?
Thks & Best Rgds,
Prakash.

[Prakash Wadhwani]

Allen ... Wow !! 3 God-Send (sorry Allen-Send) alternatives. Thx! I'm
gonna try one of them tomorrow morning 1st thing.

Once again .. thx a ton !! I'll sure get back to you after I've tried
it. But any which way's ... one of them solutions has gotta work ...
Whew !!

Muchos Gracias !
Best Rgds,
Prakash.


"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<NA********************@news-server.bigpond.net.au>...

The WhereCondition is applied to the main report.
Since that is unbound, it hs no effect.

The simplest way to do this would be to add a text box to the main report,
and set its Control Source to:
[Forms]![YourForm]![Ser_Tkt_Num]

Then in the LinkMasterFields of your subreports, enter the name of the text
box. In the LinkChildFields, enter the name of the matching field (the
foreign key) in each subreport.

Another way to achieve the same result would be to include
=[Forms]![YourForm]![Ser_Tkt_Num]
in the Critieria of the queries (under the foreign key field).

A third alternative would be to use the Report_Open event of the subreports
to set their Filter property so they only show the desired records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Prakash Wadhwani" <si****@omantel.net.om> wrote in message
news:60**************************@posting.google.c om...

I have a main Report with 3 sub-reports eg:

Report1 (Main Report) ... SubRpt1, SubRpt2, SubRpt3
SubRpt1, SubRpt2 & SubRpt3 are bound to 3 different tables with a
common relation on a NUMERIC field called Service_Ticket_No
(Ser_Tkt_Num).

The Main Report is not bound to any table but I could if required.
I have a similar FORM setup - Main Form + 3 Subforms. This form has a
PRINT button. When the user clicks that print button I would like to
print only the Service ticket he is positioned on. This is my code:

==== Code Snippet ========
Private Sub Cmd_Print_Click()
On Error GoTo Err_Cmd_Print_Click

Dim stDocName As String
Dim where_condition As String

stDocName = "Report1"
where_condition = "Ser_Tkt_Num = " & Me.Ser_Tkt_Num

With DoCmd
.OpenReport stDocName, acPreview, , where_condition
.Maximize
.RunCommand acCmdFitToWindow
End With
Exit_Cmd_Print_Click:
Exit Sub

Err_Cmd_Print_Click:
MsgBox Err.Description
Resume Exit_Cmd_Print_Click

End Sub
==== End of Code Snippet ============

My Problem:
~~~~~~~~~~
All the service tickets print.
I presume the parameter (Ser_Tkt_Num) is not visible to the
subreports. How can I accomplish this ?
Thks & Best Rgds,
Prakash.

[Prakash Wadhwani]

Allen ... I opted for the 3rd option viz setting a filter in the Open
Report Event. However I get the following eror msg:

Run Time Eror 2101
The Setting you sntered isn't valid for this property.

Here is my code in the Open Report Event:

Private Sub Report_Open(Cancel As Integer)
'Me.Filter = Forms.Service_Ticket_Master.Ser_Tkt_Num
Me.Filter = "Me.Ser_Tkt_Num = " &
Forms!Service_Ticket_Master!Ser_Tkt_Num
Me.FilterOn = True
End Sub

I tried both the above filter options incl the option rem'med out.
Both give the same eror. Can you please tell me where I'm going wrong
??

BTW, I did finally try your first option & it worked but I'd really
prefer to use the 3rd option.

Thx & Best Rgds,
Prakash.

"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<NA********************@news-server.bigpond.net.au>...

The WhereCondition is applied to the main report.
Since that is unbound, it hs no effect.

The simplest way to do this would be to add a text box to the main report,
and set its Control Source to:
[Forms]![YourForm]![Ser_Tkt_Num]

Then in the LinkMasterFields of your subreports, enter the name of the text
box. In the LinkChildFields, enter the name of the matching field (the
foreign key) in each subreport.

Another way to achieve the same result would be to include
=[Forms]![YourForm]![Ser_Tkt_Num]
in the Critieria of the queries (under the foreign key field).

A third alternative would be to use the Report_Open event of the subreports
to set their Filter property so they only show the desired records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Prakash Wadhwani" <si****@omantel.net.om> wrote in message
news:60**************************@posting.google.c om...

I have a main Report with 3 sub-reports eg:

Report1 (Main Report) ... SubRpt1, SubRpt2, SubRpt3
SubRpt1, SubRpt2 & SubRpt3 are bound to 3 different tables with a
common relation on a NUMERIC field called Service_Ticket_No
(Ser_Tkt_Num).

The Main Report is not bound to any table but I could if required.
I have a similar FORM setup - Main Form + 3 Subforms. This form has a
PRINT button. When the user clicks that print button I would like to
print only the Service ticket he is positioned on. This is my code:

==== Code Snippet ========
Private Sub Cmd_Print_Click()
On Error GoTo Err_Cmd_Print_Click

Dim stDocName As String
Dim where_condition As String

stDocName = "Report1"
where_condition = "Ser_Tkt_Num = " & Me.Ser_Tkt_Num

With DoCmd
.OpenReport stDocName, acPreview, , where_condition
.Maximize
.RunCommand acCmdFitToWindow
End With
Exit_Cmd_Print_Click:
Exit Sub

Err_Cmd_Print_Click:
MsgBox Err.Description
Resume Exit_Cmd_Print_Click

End Sub
==== End of Code Snippet ============

My Problem:
~~~~~~~~~~
All the service tickets print.
I presume the parameter (Ser_Tkt_Num) is not visible to the
subreports. How can I accomplish this ?
Thks & Best Rgds,
Prakash.

[Allen Browne]

What is the data type of the field Ser_Tkt_Num?

If it's Text, you need extra quotes:
Me.Filter = "Me.Ser_Tkt_Num = """ & _
Forms!Service_Ticket_Master!Ser_Tkt_Num & """"

That should be fine for a main report. Not sure about a subreport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prakash Wadhwani" <si****@omantel.net.om> wrote in message
news:60*************************@posting.google.co m...

Allen ... I opted for the 3rd option viz setting a filter in the Open
Report Event. However I get the following eror msg:

Run Time Eror 2101
The Setting you sntered isn't valid for this property.

Here is my code in the Open Report Event:

Private Sub Report_Open(Cancel As Integer)
'Me.Filter = Forms.Service_Ticket_Master.Ser_Tkt_Num
Me.Filter = "Me.Ser_Tkt_Num = " &
Forms!Service_Ticket_Master!Ser_Tkt_Num
Me.FilterOn = True
End Sub

I tried both the above filter options incl the option rem'med out.
Both give the same eror. Can you please tell me where I'm going wrong
??

BTW, I did finally try your first option & it worked but I'd really
prefer to use the 3rd option.

Thx & Best Rgds,
Prakash.

"Allen Browne" <ab***************@bigpond.net.au> wrote in message

news:<NA********************@news-server.bigpond.net.au>...

The WhereCondition is applied to the main report.
Since that is unbound, it hs no effect.

The simplest way to do this would be to add a text box to the main report, and set its Control Source to:
[Forms]![YourForm]![Ser_Tkt_Num]

Then in the LinkMasterFields of your subreports, enter the name of the text box. In the LinkChildFields, enter the name of the matching field (the
foreign key) in each subreport.

Another way to achieve the same result would be to include
=[Forms]![YourForm]![Ser_Tkt_Num]
in the Critieria of the queries (under the foreign key field).

A third alternative would be to use the Report_Open event of the subreports to set their Filter property so they only show the desired records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Prakash Wadhwani" <si****@omantel.net.om> wrote in message
news:60**************************@posting.google.c om...

I have a main Report with 3 sub-reports eg:

Report1 (Main Report) ... SubRpt1, SubRpt2, SubRpt3
SubRpt1, SubRpt2 & SubRpt3 are bound to 3 different tables with a
common relation on a NUMERIC field called Service_Ticket_No
(Ser_Tkt_Num).

The Main Report is not bound to any table but I could if required.
I have a similar FORM setup - Main Form + 3 Subforms. This form has a
PRINT button. When the user clicks that print button I would like to
print only the Service ticket he is positioned on. This is my code:

==== Code Snippet ========
Private Sub Cmd_Print_Click()
On Error GoTo Err_Cmd_Print_Click

Dim stDocName As String
Dim where_condition As String

stDocName = "Report1"
where_condition = "Ser_Tkt_Num = " & Me.Ser_Tkt_Num

With DoCmd
.OpenReport stDocName, acPreview, , where_condition
.Maximize
.RunCommand acCmdFitToWindow
End With
Exit_Cmd_Print_Click:
Exit Sub

Err_Cmd_Print_Click:
MsgBox Err.Description
Resume Exit_Cmd_Print_Click

End Sub
==== End of Code Snippet ============

My Problem:
~~~~~~~~~~
All the service tickets print.
I presume the parameter (Ser_Tkt_Num) is not visible to the
subreports. How can I accomplish this ?
Thks & Best Rgds,
Prakash.

[Prakash Wadhwani]

Allen the field type is numeric. I've even put a msgbox there & it
shows the right value but I still get that Run Time error msg. I guess
it has something to do with sub-reports.

Thx & Best Rgds,
Prakash


"Allen Browne" <al*********@SeeSig.invalid> wrote in message news:<1d********************@news-server.bigpond.net.au>...

What is the data type of the field Ser_Tkt_Num?

If it's Text, you need extra quotes:
Me.Filter = "Me.Ser_Tkt_Num = """ & _
Forms!Service_Ticket_Master!Ser_Tkt_Num & """"

That should be fine for a main report. Not sure about a subreport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prakash Wadhwani" <si****@omantel.net.om> wrote in message
news:60*************************@posting.google.co m...

Allen ... I opted for the 3rd option viz setting a filter in the Open
Report Event. However I get the following eror msg:

Run Time Eror 2101
The Setting you sntered isn't valid for this property.

Here is my code in the Open Report Event:

Private Sub Report_Open(Cancel As Integer)
'Me.Filter = Forms.Service_Ticket_Master.Ser_Tkt_Num
Me.Filter = "Me.Ser_Tkt_Num = " &
Forms!Service_Ticket_Master!Ser_Tkt_Num
Me.FilterOn = True
End Sub

I tried both the above filter options incl the option rem'med out.
Both give the same eror. Can you please tell me where I'm going wrong
??

BTW, I did finally try your first option & it worked but I'd really
prefer to use the 3rd option.

Thx & Best Rgds,
Prakash.

"Allen Browne" <ab***************@bigpond.net.au> wrote in message

news:<NA********************@news-server.bigpond.net.au>...

The WhereCondition is applied to the main report.
Since that is unbound, it hs no effect.

The simplest way to do this would be to add a text box to the main report, and set its Control Source to:
[Forms]![YourForm]![Ser_Tkt_Num]

Then in the LinkMasterFields of your subreports, enter the name of the text box. In the LinkChildFields, enter the name of the matching field (the
foreign key) in each subreport.

Another way to achieve the same result would be to include
=[Forms]![YourForm]![Ser_Tkt_Num]
in the Critieria of the queries (under the foreign key field).

A third alternative would be to use the Report_Open event of the subreports to set their Filter property so they only show the desired records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Prakash Wadhwani" <si****@omantel.net.om> wrote in message
news:60**************************@posting.google.c om...
> I have a main Report with 3 sub-reports eg:
>
> Report1 (Main Report) ... SubRpt1, SubRpt2, SubRpt3
>
>
> SubRpt1, SubRpt2 & SubRpt3 are bound to 3 different tables with a
> common relation on a NUMERIC field called Service_Ticket_No
> (Ser_Tkt_Num).
>
> The Main Report is not bound to any table but I could if required.
>
>
> I have a similar FORM setup - Main Form + 3 Subforms. This form has a
> PRINT button. When the user clicks that print button I would like to
> print only the Service ticket he is positioned on. This is my code:
>
> ==== Code Snippet ========
> Private Sub Cmd_Print_Click()
> On Error GoTo Err_Cmd_Print_Click
>
> Dim stDocName As String
> Dim where_condition As String
>
> stDocName = "Report1"
> where_condition = "Ser_Tkt_Num = " & Me.Ser_Tkt_Num
>
> With DoCmd
> .OpenReport stDocName, acPreview, , where_condition
> .Maximize
> .RunCommand acCmdFitToWindow
> End With
>
>
> Exit_Cmd_Print_Click:
> Exit Sub
>
> Err_Cmd_Print_Click:
> MsgBox Err.Description
> Resume Exit_Cmd_Print_Click
>
> End Sub
> ==== End of Code Snippet ============
>
>
>
> My Problem:
> ~~~~~~~~~~
> All the service tickets print.
> I presume the parameter (Ser_Tkt_Num) is not visible to the
> subreports. How can I accomplish this ?
>
>
> Thks & Best Rgds,
> Prakash.