Print one record from subform

[Sarah]

To begin with - thank you - you are all so helpful with all of your
knowledge.

I read the answer is out there but I could not find it. I have a form with
a subform. I created a report to print from the form. I want the report to
print the the form information and the selected record in the subform. Below
is the where condition I used. When I run the where condition from the
immediate window I get the results I expect - but when the report runs, it is
blank. I just can't figure out what it is. The DistID field is numeric and
the DatePaid field is a Date. Any help would be appreciated.

DoCmd.OpenReport stDocName, acPreview, ,
"[Qry-DistDistDetail].[Distributors.DistID]=" & Me![DistID] & " and
[Qry-DistDistDetail]![DatePaid]=" & [Form]![frm-DistDetailSubform]![DatePaid]

Results:
[Qry-DistDistDetail].[Distributors.DistID]=1 and
[Qry-DistDistDetail]![DatePaid]= 12/22/2005

Thanks, Sarah

 

[Allen Browne]

Suggestions:

1. Add the # delimiter to the date.

2. Omit the query name from the WhereCondition string.

3. If you need to include the table name, use square brackets around the
table name and the field name separately, i.e.
[Distributors].[DistID]

4. The subform is not open in its own right, i.e. it will not be found in
the Forms collection. If it is a subform of the form where you are writing
this code, you can refer to it as:
Me.[NameOfYourSubformControlHere].Form![NameOfYourControlHere]
If not, use:
Forms![NameOfYourMainFormHere]
in place of the Me.

5. Create a separate string, so you can Debug.Print the results, and check
them in the Immediate Window after it runs. (Press Ctrl+G.)

6. Brackets are optional, but can help.

7. Use IsNull() to check if there is a value in the 2 controls.

8. Save any edits in progress before you open the report.

The result will be something like this (without the null tests):
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
strWhere => "([DistID] = " & Me![DistID] & ") AND ([DatePaid] = " & _
Format(Me.[frm-DistDetailSubform].Form![DatePaid],
"\#mm\/dd\/yyyy\#") & ")"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

 

[Sarah]

Alan,

Thank you for your detailed explanation. I was able to plug the code in and
it worked perfectly (after I removed the > sign after the first =).

Thanks, Sarah

Suggestions:

1. Add the # delimiter to the date.

2. Omit the query name from the WhereCondition string.

3. If you need to include the table name, use square brackets around the
table name and the field name separately, i.e.
[Distributors].[DistID]

4. The subform is not open in its own right, i.e. it will not be found in
the Forms collection. If it is a subform of the form where you are writing
this code, you can refer to it as:
Me.[NameOfYourSubformControlHere].Form![NameOfYourControlHere]
If not, use:
Forms![NameOfYourMainFormHere]
in place of the Me.

5. Create a separate string, so you can Debug.Print the results, and check
them in the Immediate Window after it runs. (Press Ctrl+G.)

6. Brackets are optional, but can help.

7. Use IsNull() to check if there is a value in the 2 controls.

8. Save any edits in progress before you open the report.

The result will be something like this (without the null tests):
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
strWhere => "([DistID] = " & Me![DistID] & ") AND ([DatePaid] = " & _
Format(Me.[frm-DistDetailSubform].Form![DatePaid],
"\#mm\/dd\/yyyy\#") & ")"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

To begin with - thank you - you are all so helpful with all of your
knowledge.

I read the answer is out there but I could not find it. I have a form
with
a subform. I created a report to print from the form. I want the report
to
print the the form information and the selected record in the subform.
Below
is the where condition I used. When I run the where condition from the
immediate window I get the results I expect - but when the report runs, it
is
blank. I just can't figure out what it is. The DistID field is numeric
and
the DatePaid field is a Date. Any help would be appreciated.

DoCmd.OpenReport stDocName, acPreview, ,
"[Qry-DistDistDetail].[Distributors.DistID]=" & Me![DistID] & " and
[Qry-DistDistDetail]![DatePaid]=" &
[Form]![frm-DistDetailSubform]![DatePaid]

Results:
[Qry-DistDistDetail].[Distributors.DistID]=1 and
[Qry-DistDistDetail]![DatePaid]= 12/22/2005

Thanks, Sarah