Basing a report on a parameter query based on a search form

[mmiram]

Hi all:

This is my first post on this forum. This is the problem I have. I created a small form in Access that has 2 unbound controls on it. The values entered in these controls are passed on to the query as parameters and the results are shown in a data sheet view.

Here is the issue. I want to redirect the results of the query into an access report and not just view them in a data sheet view. I created a report based on the query, but when I try to run the report, it is asking me to enter the parameters. Is there any way, that I can redirect the results of the query (after I run it using the search form) into Access Reports. In other words how do I link all threee together so that when I enter the search parameters into the form and run the form, the query is executed and the results are shown in an Access report.

Thanks,
Ram.

 

[traingamer]

Have the query be the recordsource of both the report and the datasheet. The parameter in the query should be based on your other form like [forms]![YourFormName]![YourCountrolName1] and [forms]![YourFormName]![YourCountrolName2]

See the example in the template for Contact Management...

traingamer

 

[mmiram]

I am not sure I understand. The query runs fine when I run it from the search form I created and the parameters in the query are exactly how you described.

I am trying to redirect the output of the query to an Access Report so that I can group it based on a field.

Am I missing something here? And I am not sure about the example you mentioned. Is it an example inbuilt in Access? if not, where can I find it?

Thanks.

 

[traingamer]

It is a built-in template in acesss (at least in 2000 and up).

Your query form MUST be still open when you run the report.

traingamer

 

[mmiram]

Hi:

This is what I tried doing now. I linked the search form to the report and I am trying to pass the value in one unbound control as a parameter to the query on which the report is based. I am pasting the code below...

Private Sub runReport_Click()
DoCmd.OpenReport "ReportDesignDue", acViewPreview, , "Design Due Date=" & Forms!ReportDueDateDesign1!DueDate
End Sub


ReportDesignDue = the report based on a query that accepts the "Design Due Date" field as a parameter.

ReportDueDateDesign1 = the form through which the report is being called.

DueDate = the date field on the form.


When I click on the the button to run the report, I am getting a run time error


Run Time Error 3075
Syntax Error (Missing Operator) in Query Expression '(Design Due Date = 2/5/2005)'

I think this is whats holding up the whole thing. Why am I getting this error.

I am sorry but I am not a programmer. Thanks for your patience.

 

[dhookom]

Private Sub runReport_Click()
DoCmd.OpenReport "ReportDesignDue", acViewPreview, , "Design Due Date=" & Forms!ReportDueDateDesign1!DueDate
End Sub

Several possible issues.
1) the form must be open with a value in the text box
2) The where clause should be:
"[Design Due Date]=#" & Forms!ReportDueDateDesign1!DueDate 7 "#"
Note: surround dates with "#" and if you create names with spaces, you must enclose the name in []s.

Does the report's record source query also have criteria set? Is the DueDate criteria duplicated in the report's record source?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]