Query by Form Issues

[xxx]

INTRO:
Relatively new to all of this, but really enjoying the learning process. Great fun for me. Interested in learning VBA, so despite being a "newbie" don't be afraid to provide VBA solutions. Though if time and inclination permit, please, explain what is going on!

ISSUES/QUESTIONS:
I have created a simple form which is intended to generate and display a print preview of a report containing data specific to a list box selection (i.e. User selects an item from the list box -> After "Search/Verify" button is pressed a report print preview is displayed in separate tab which is based on a query whose criteria is based on the list box selection.

2 Issues, or rather preferences:

(1) I submit my initial query after opening the form. My report print preview opens in a separate tab. Say that I now return to my form and submit a different query. If the previous print preview was not closed before submitting a new query, it will continue to display the print preview associated with the previous query instead of generating a new print preview based on the new list box selection.

I would prefer that the user doesn't necessarily have to close one report print preview to view another. I want the report preview to be updated regardless.

Not sure how to do this. Have tried a few things, but have been unsuccessful.

(2) Preferrably... what I really wanted... was for the report/data preview to be refreshed and displayed on the same form in which the user submits their query. Then, include a "Print" button that allows the user to print the report if desired.

Not sure if displaying report data in a query form is even possible. Most of what I've found suggests that it is not.

I will be satisfied with a solution for (1) or (2) though! Thank you in advance for all of your help.

 

[jonno_g]

Try loading this code in a module:

Function IsLoadedRPT(ByVal strReportName As String) As Boolean
' Returns True if the specified Report is open.
    Const conObjStateClosed = 0
 
    If SysCmd(acSysCmdGetObjectState, acReport, strReportName) <> conObjStateClosed Then
        IsLoadedRPT = True
    End If
 
End Function

and then put this at the start of your code behind the button that loads the report:

If IsLoadedRPT("YOUR REPORT NAME HERE") Then
    DoCmd.Close acReport, "YOUR REPORT NAME HERE"", acSaveNo
End If

The function at the top is to check to see if the report is loaded, then the code at the bottom uses that function and if the report is loaded, then it is closed without saving. Your existing code should then go on to reopen the report with the new data displayed.

From a users' perspective, they should not see anything other than maybe a brief flicker then the new data should be displayed, and they'll only see that if they happen to be viewing in separate windows rather than tabbed view.

Let us know how you go.

Cheers,...Jon.