Switch Query on Report using VBA 3

[jabz]

I have a report where I would like to have a toggle switch that when pressed it switches the query to a different query. Is this possible?, I hope I explained myself clearly enough. Thanks in advance.

 

[CosmoKramer]

If you mean: assign a report's query at open, then yes you can. Look at the OpenReport method:

DoCmd.OpenReport "YourReport", acViewNormal, "YourQuery"

 

[Trendsetter]

If you mean that you want to vary the report query dependant upon a condition, then you need to qualify the opening command.
Dim strLink as String
If !some condition! = True Then
strLink = "qryFirst"
Else
strLink = "qrySecond"
End if
DoCmd.OpenReport "YourReport", acViewNormal, strLink

 

[jabz]

What I would like to have is a form with two buttons one says ON one says OFF(Which I already have), when ON is pressed I want it to change the current query assigned to the report to a different query and when OFF is pressed I want it changed back to the original query.

Maybe that explains it better. Is there a way to change a report query with out opening it?

 

[Trendsetter]

In the On_Click event of the ON button enter the code:

Dim strLink as String
strLink = "qryOriginal"
DoCmd.OpenReport "YourReport", acViewNormal, strLink

In the On_Click event of the OFF button enter the code:

Dim strLink as String
strLink = "qryDifferent"
DoCmd.OpenReport "YourReport", acViewNormal, strLink

 

[jabz]

I know how to do that, but I was wondering if it was possible to do it with out having to open the report. If there was someway to set RecordSource outside of the Report.
but thanks for the suggestions, any more would be most appreciated

 

[CosmoKramer]

Why would you want to change the RecordSource of a report without actually opening it? What benefit would there be to that?

 

[jabz]

I have a report in which I'm showing "Incomplete" rather then the company name if specific information is not in the table...but I want to be able to press a button and have it switch to a query that has company name and not the "incomplete" even if they dont have the information. I have the report and the two queries and they work fine, I just want to find a way to toggle the Record source through On_Click event of a button.

Hope I explained it well enough!

 

[CosmoKramer]

OK, I think I understand what you're trying to say.

I would add an option group to your form with two radio buttons, one for each option. Label them appropriately. Leave the default values for the radio buttons to be 1 and 2.

The On Click event for the command button to print your report would essentially be Trendsetter's first post above:

Dim strLink as String
If Me.YourFrameName.Value = 1 Then
  strLink = "qryFirst"
Else
  strLink = "qrySecond"
End if
DoCmd.OpenReport "YourReport", acViewNormal, strLink

Radio buttons are a good control for this function because you want to have one of the options selected.

Let me know if this helps.....

 

[jabz]

When you say radio buttons are you talking about the "Toggle buttons" or where can I find these radio buttons

 

[CosmoKramer]

Sorry about the confusion....they're actually called "option buttons". If you add an option group to your form you are asked by the Option Group wizard for a choice of what kind of control you want in the group. At that point select option buttons......

 

[jabz]

No, that doesnt seem to do what i'm looking for, sorry its my fault for not explaining my self clear enough.

Okay on my Menu screen I have a hidden button that when you double click it brings up this Manager options screen and this is where I want to have the switch that changes the query from one to another. After making a selection on this screen and exiting the screen I want the selection that has been made to stick until the manager goes in there again and flips the opposite switch, kind of a permenant switch until changed if that makes since. Sorry for not being clear enough.

Hope you understand more clearly, its so hard to describe want!!!

 

[CosmoKramer]

Does it have to be a "hidden" screen that controls this function?? Couldn't you have an option group next to the command button to run the report that the user can select the option they want from?? This way, they can change the option on the same screen they will run the report...

If not, it will be a little more involved.

Let me know...

 

[jabz]

Well were trying to make it to where we set the option on whether they see all of the information or if we hide the company name to try to force them to fill in the information that is missing.

 

[jabz]

To answer your question Yes!

 

[CosmoKramer]

Well, here goes.....

If it has to be a separate form that controls this that is closed when the report is actually run, then you have to go a different route.

On this "manager screen" I would use the option buttons, not the toggle button. I think the option button is easier to interpret, you can label the options with exactly the text you want. Leave the values to be 1 and 2.

Add a new table (tblReportFlag) that will contain a single integer field (ReportFlag), and only one record.

Add an update query that will update that new integer field based on the selection of the option group. The SQL for that update query would look something like this:

UPDATE tblReportFlag SET tblReportFlag.ReportFlag = [Forms]![YourFormName]![YourFrameName].[value];

In the On Click event of the option frame put code like this to run the update query:

DoCmd.OpenQuery "YourUpdateQueryName"

This will update the ReportFlag field with the 1 or 2 value.

In the On Click event of the command button to run the report, put code like this:

Dim intPrint as Integer
intPrint = DLookup("[ReportFlag]", "YourTable")

If intPrint = 1 Then
  strLink = "qryFirst"
Else
  strLink = "qrySecond"
End if
DoCmd.OpenReport "YourReport", acViewNormal, strLink

I'm sure I left a step out, or slightly suffled them around, but basically does this make sense??

You will have to use the "hidden" form to update a field in another table that you will interrogate at report run-time.

Let me know if this helps, or hurts.....

 

[jabz]

Now all this sounds good and the update query is doing as it should, the only problem I have is what should I put into the record source of the Report as default. Because if I put one of the queries it always goes off of that and if I leave it blank it does not open the Report??

Let me know if this makes since, but thank you very much for getting me this far, you have been alot of help.

 

[CosmoKramer]

See, I told you I would leave something out. I forgot that to change the RecordSource at run-time you have to open the report in design view first.

Set the report's RecordSource to the first query "qryFirst".

Now, the OnClick event of the command button to print the report should look something like this:

Dim intPrint as Integer
intPrint = DLookup("[ReportFlag]", "YourTable")
DoCmd.OpenReport "YourReport", acViewDesign

If intPrint = 1 Then
  Reports![YourReport].RecordSource = "qryFirst"
Else
  Reports![YourReport].RecordSource = "qrySecond"
End if
DoCmd.OpenReport "YourReport", acViewNormal
DoCmd.Close acReport, "YourReport", acSaveNo

The acSaveNo is important because if that's not there the user will be asked if they want to save the report.

So basically you have to open the report in design view, change the RecordSource, print it, then close it without saving it.

 

[jabz]

What if I want to acViewPreview??

 

[CosmoKramer]

If you want to preview, and you're only talking about two reports, I would clone the reports, assign their respective queries and based on the user selection, run either report. Otherwise the user will be prompted to save the report at close.

Dim intPrint as Integer
intPrint = DLookup("[ReportFlag]", "YourTable")

If intPrint = 1 Then
  DoCmd.OpenReport "YourReport1", acViewPreview
Else
  DoCmd.OpenReport "YourReport2", acViewPreview
End If