Running a report from a form 1

[k9handler]

I've created a form [JobTrans] with a subform [JobTransDetail] to record transactions. The linked field is [Reference]. I'm trying to add a button that runs a report based on the current record on the form including items from subform. I designed a report "JobTrans-Ref" based on a query with a parameter [Reference ?] in the [JobTrans]![Reference] field.

When I run the report I get the standard Access parameter dialog box and the report runs just fine after typing (or copy & paste)in the [Reference ?].

How do I get the [Reference] that is on the current form to pass to the query parameter.

Thanks

 

[PHV]

Have a look here:
faq701-6763

In your query, replace this:
[Reference ?]
with this:
[Forms]![JobTrans]![Reference]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[k9handler]

Thanks for the information. The suggestion is what I had already used in my query. I still get the same "standard MS Access" parameter box instead of my form. It's almost as if Access does not know the form is out there. Any ideas? I'm not even sure what additional information about my database to share. Thanks again.

 

[dhookom]

What is the SQL view of the query?
Is the form open when you run the query/report?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya rhyust . . .

C'mon rhyust ... [blue]we can't help you if we can't see the SQL! ...[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[k9handler]

This is the SQL. The query works when I request the report. But uses the standard parameter query dialog box.

SELECT JobMaster.JobID, JobMaster.JobDesc, JobTrans.TranDate, JobTrans.Reference, JobTrans.TranType, JobTransDetail.ItemID, JobTransDetail.Quantity, JobTransDetail.Cost, [quantity]*[Cost] AS [Value]
FROM (JobMaster INNER JOIN JobTrans ON JobMaster.JobID = JobTrans.JobID) INNER JOIN JobTransDetail ON JobTrans.Reference = JobTransDetail.Reference
WHERE (((JobTrans.Reference)=[Reference ?]));

What I changed it to is:

SELECT JobMaster.JobID, JobMaster.JobDesc, JobTrans.TranDate, JobTrans.Reference, JobTrans.TranType, JobTransDetail.ItemID, JobTransDetail.Quantity, JobTransDetail.Cost, [quantity]*[Cost] AS [Value]
FROM (JobMaster INNER JOIN JobTrans ON JobMaster.JobID = JobTrans.JobID) INNER JOIN JobTransDetail ON JobTrans.Reference = JobTransDetail.Reference
WHERE (((JobTrans.Reference)=[Forms]![FindReferences]![SelectedReference]));

The result is the same. It does not seem to see my form [FindReferences]. It still brings up the standard parameter query dialog.

Thanks for the help

 

[dhookom]

This query

SELECT JobMaster.JobID, JobMaster.JobDesc, JobTrans.TranDate,
 JobTrans.Reference, JobTrans.TranType, JobTransDetail.ItemID,
 JobTransDetail.Quantity, JobTransDetail.Cost, [quantity]*[Cost] AS [Value]
FROM (JobMaster
 INNER JOIN JobTrans ON JobMaster.JobID = JobTrans.JobID)
 INNER JOIN JobTransDetail ON JobTrans.Reference = JobTransDetail.Reference
WHERE (((JobTrans.Reference)=[Forms]![FindReferences]![SelectedReference]));

will not ask for [Reference ?]. If is does, you are not providing all the correct information.

Is the form "FindReferences" open?

Duane
Hook'D on Access
MS Access MVP

 

[k9handler]

The form not being open looks like the problem. I opened the form, selected my reference and then ran the report. Worked fine. So, the question is - How do I open the form "FindReferences" before I click on the report?

 

[dhookom]

I open the form first and have a button on the form to open the report.

Duane
Hook'D on Access
MS Access MVP

 

[k9handler]

Got things working - thanks all for the help