Open report automaticallty after query is set

[fishtek]

Hello:
I have a switchboard with a button that opens a query in design view to be altered as needed by the user. Then the user saves and closes the query and clicks another button to open a report that runs off the query. Is there a method to open the report automatically after the query is altered and/or saved without having to close and click additional buttons. Thanks for your help.

 

[dhookom]

I don't believe there is any way to do this. I can't imagine why you need to allow a user to go into the design view of the query. The generally accepted method of filtering reports is to use controls on forms to allow the user to enter criteria values.

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]

 

[fishtek]

Thanks for your reply:
This query contains quite a few fields where users may enter all combinations of criteria. If I use an unbound form and place [forms]![someform]![txtcriteria] in the query how do I allow for criteria left blank on the form. When doing this in the past and using "or is Null" in the query it becomes complex and bogs down. Thanks again.

 

[dhookom]

For 99% of my reports, I don't use dynamic criteria in the query/record source. I prefer to use code to build a where condition that is used in the DoCmd.OpenReport method. If a user doesn't enter a value into txtEndDate then the where condition will ignore the txtEndDate.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
  strWhere = strWhere & " AND [DateField]>=#" & _
      Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
  strWhere = strWhere & " AND [DateField]<=#" & _
      Me.txtEndDate & "# "
End If
[green]'... more similar lines[/green]
DoCmd.OpenReport "rptName", acPreview, , strWhere

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]

 

[fishtek]

Thanks again.. So I assume the report continues to run off the original query and that I leave the criteria all blank in the query grid. Also.. can you please clarify your code syntax for a non -date field, i keep getting a syntax error.
Thanks for your patience.

 

[dhookom]

Yes, you would leave the criteria clear of any dynamic values.

Dim strWhere as String
strWhere = "1=1 "
'Dept is a text field
If Not IsNull(Me.cboDept) Then
  strWhere = strWhere & " AND [Dept]= """ & _
      Me.cboDept & """ "
End If
'EmployeeID is numeric
If Not IsNull(Me.cboEmployeeID) Then
  strWhere = strWhere & " AND [EmployeeID]= " & _
      Me.cboEmployeeID & " " 
End If
'txtSearchFor is a text field
If Not IsNull(Me.txtSearchFor) Then
  strWhere = strWhere & " AND [Description] Like  ""*" & _
      Me.txtSearchFor & "*"" "
End If

'... more similar lines
DoCmd.OpenReport "rptName", acPreview, , strWhere

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]

 

[fishtek]

Thanks dhookom, your suggestion worked fantastic! One last question. Can I use this where condition to set criteria and open just the query from the form.. Thanks for all your help.

 

[dhookom]

I don't know why you would want to do this but I can't see how it would work. The DoCmd.OpenForm and DoCmd.OpenReport methods accept a where condition. Opening a query doesn't.

You could create a form that defaults to datasheet view and open the ds form with similar code.

This type of code will not work for all forms and reports. For instance, your report may be based on a totals query that groups by Year and Month so there isn't a full date field in the report's record source. Only fields in the report's/form's field list can be referenced with this solution.

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]