Subform & Main Form Control

[PHisaw]

I have a main form (fGeneralInfo) with control (JobNumber (auto number)).
There is a subform (fStatus) with control (SlspNotice (date field)). When
the date is filled in, on exit, a report is prompted to print asking for job
number. The report's underlying query asks for job number. Using the
following code, which doesn't work, is there a way to make the report print
reading the job number from the control on the main form without the dialog
box prompting for job number?
As always, any help is greatly appreciated!
Thanks, Phisaw

Private Sub SalesmanNotice_Exit(Cancel As Integer)
On Error GoTo Err_SalesmanNotice_Click

If [Forms]![fGeneralInformation]![CustomerName] = "Plaquemine" Then
MsgBox "Send Status From TempEmails to Slsp"
Else
Set Application.Printer = Application.Printers("CutePDF Writer")
DoCmd.OpenReport "rRepairAdvisement", , , "JobNumber=" &
[Forms]![fGeneralInformation]![JobNumber]
Set Application.Printer = Nothing
End If

Exit_SalesmanNotice_Click:
Exit Sub

Err_SalesmanNotice_Click:
MsgBox Err.Description
Resume Exit_SalesmanNotice_Click



End Sub

 

[Wayne Morgan]

It appears that your code should open the report filtered on the JobNumber
on the main form. If you're still being prompted, I would suspect either the
query or something in the report itself that is looking for the value and it
is mistyped. If the latter, it is most likely in the Grouping and Sorting
dialog.

Please post the SQL of the query used as the report's Record Source.

 

[Klatuu]

You are passing the job number to the report as a Where condition. This
should filter your data without your having to have the parameter in the
query. Try removing the parameter from the query and let the report do the
filtering.

 

[PHisaw]

Thank you for the prompt reply and your help. The reports query is the same
as the query for my main form and it is set up to [Enter Job Number] as
criteria for JobNumber field so the user is prompted before opening main form
to view info needed. Should I copy the query, remove the criteria prompt,
and rename it and use it just for the report? Do all these queries cause the
db to grow too large? Or, is there a way to remove the prompt from the query
and still have user prompted to enter job number needed when opening form?
This would be ideal, because I've ran into problems with this before. We've
used this db for several years and now I'm trying to streamline procedures
using code where I can and get away from so many macros.

You are passing the job number to the report as a Where condition. This
should filter your data without your having to have the parameter in the
query. Try removing the parameter from the query and let the report do the
filtering.

I have a main form (fGeneralInfo) with control (JobNumber (auto number)).
There is a subform (fStatus) with control (SlspNotice (date field)). When
the date is filled in, on exit, a report is prompted to print asking for job
number. The report's underlying query asks for job number. Using the
following code, which doesn't work, is there a way to make the report print
reading the job number from the control on the main form without the dialog
box prompting for job number?
As always, any help is greatly appreciated!
Thanks, Phisaw

Private Sub SalesmanNotice_Exit(Cancel As Integer)
On Error GoTo Err_SalesmanNotice_Click

If [Forms]![fGeneralInformation]![CustomerName] = "Plaquemine" Then
MsgBox "Send Status From TempEmails to Slsp"
Else
Set Application.Printer = Application.Printers("CutePDF Writer")
DoCmd.OpenReport "rRepairAdvisement", , , "JobNumber=" &
[Forms]![fGeneralInformation]![JobNumber]
Set Application.Printer = Nothing
End If

Exit_SalesmanNotice_Click:
Exit Sub

Err_SalesmanNotice_Click:
MsgBox Err.Description
Resume Exit_SalesmanNotice_Click



End Sub

 

[Wayne Morgan]

You could copy the query, as you mention, or you could filter the form after
it is opened. This would allow you to continue using the same query for
both.

 

[PHisaw]

Thanks to both Wayne and Klatuu for replies and help. Wayne you mentioned
filtering the form after it's opened, is this the same as entering a job
number as a parameter value from the query or is there a way to do it with
code and not have to enter the job number again?
Thanks, Phisaw

You could copy the query, as you mention, or you could filter the form after
it is opened. This would allow you to continue using the same query for
both.

--
Wayne Morgan
MS Access MVP

Thank you for the prompt reply and your help. The reports query is the
same
as the query for my main form and it is set up to [Enter Job Number] as
criteria for JobNumber field so the user is prompted before opening main
form
to view info needed. Should I copy the query, remove the criteria prompt,
and rename it and use it just for the report? Do all these queries cause
the
db to grow too large? Or, is there a way to remove the prompt from the
query
and still have user prompted to enter job number needed when opening form?
This would be ideal, because I've ran into problems with this before.
We've
used this db for several years and now I'm trying to streamline procedures
using code where I can and get away from so many macros.

 

[Wayne Morgan]

You would remove the parameter from the query. This will cause the query to
return all records (unless you have other filters on it, of course). Next,
you would have a control on the form to make the selection. In the
AfterUpdate event of the control, you would set the form's Filter and
FilterOn properties. When you click the print button on the form, you would
pass this filter value to the report so that it will also be filtered to
just the desired record. To pass the filter, you would use the "Where"
argument in the "DoCmd.OpenReport" command.

Example (filter form):
Me.Filter = "FieldName = " & Me.ControlName
Me.FilterOn = True

Example (filter report):
DoCmd.OpenReport "ReportName", acViewPreview,, "FieldName=" & Me.ControlName

The syntax in both of the above assume a Number data type for the field
"FieldName". If it is a text field, the syntax would be:

"FieldName=""" & Me.ControlName & """"