Trouble with a report and it's dialog box 1

[RacerGirl117]

I have a report "rptPGStatus" that, when opened, opens a subsequent dialog box "frmPGStatusDialog". The dialog box prompts the user for a vendor number and then they can click OK or Cancel. If they click OK I want the dialog box to close/go away.

Right now, if I run the report by double-clicking on the report itself, the dialog box comes up and I am able to enter a vendor number but once I click OK nothing happens. If I open the dialog box first, enter the vendor number, and click OK the report opens, but the dialog box does not go away unless I click on the "X".

I have other dialog boxes and reports that work in much the same fashion as this should and cannot figure out why this is not working. I don't care which way it gets handled because the person who will be running this report will be doing so from a menu.

Here is the code I have in the report:
Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Show Detail if Prop. Ord. = "No"
If PropOrd = "No" Then
Detail.Visible = False
Else
Detail.Visible = True
End If

End Sub

Private Sub Report_Activate()

'Maximize the report and hide the PG Status Dialog form.
DoCmd.Maximize
'DoCmd.Close acDefault, Form_frmPGStatusDialog

End Sub

Private Sub Report_Close()

Dim strFormName As String
strFormName = "frmPGStatusDialog"

Dim acReport As String
acReport = "rptPGStatus"

If IsLoaded(strFormName) = False Then
DoCmd.Close acReport = "rptPGStatus", acSavePrompt
DoCmd.OpenForm "Main Menu", acNormal
End If

End Sub

Private Sub Report_Deactivate()

'Close the PG Status Dialog form.

Dim strDocName As String
strDocName = "frmPGStatusDialog"

DoCmd.Close acForm, strDocName

End Sub

Private Sub Report_NoData(Cancel As Integer)

Dim strFormName As String
strFormName = "frmPGStatusDialog"

Dim acReport As String
acReport = "rptPGStatus"

If IsLoaded(strFormName) = False Then
DoCmd.Close acReport = "rptPGStatus", acSavePrompt
DoCmd.OpenForm "Main Menu", acNormal
End If

End Sub

Private Sub Report_Open(Cancel As Integer)

' Open "PG Status Dialog" form.
' IsLoaded function (defined in Utility Functions module)
' determines if specified form is open.

Dim strDocName As String
strDocName = "frmPGStatusDialog"

' Set public variable to True so PG Status Dialog knows
' report is in its Open event.
blnOpening = True

'Open form.
DoCmd.OpenForm "frmPGStatusDialog", acNormal, , , , acDialog

' If PG Status Dialog form isn't loaded, don't preview or print
' report. (User clicked Cancel button on form.)
If IsLoaded(strDocName) = False Then Cancel = True

'Set public variable to False, signifying that Open event is
'finished.
blnOpening = False

End Sub


Here is the code I have in the dialog box:
Option Compare Database

Private Sub OK_Click()

On Error GoTo Err_OK_Click

Dim stDocName As String
stDocName = "rptPGStatus"

DoCmd.OpenReport stDocName, acPreview

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub
Private Sub Cancel_Click()

Dim strFormName As String
strFormName = "Main Menu"

On Error GoTo Err_Cancel_Click

If IsLoaded(frmPGStatusDialog) = True Then
DoCmd.Close
End If

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

The Vendor No field on the dialog box is based on a two field query. Once the vendor number is selected it sets the Vendor Name field (in a macro in the After Update property).

The Utility Functions module referred to in the On Open code of the report is as follows:
Option Compare Database

Global blnOpening As Boolean

Option Explicit

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function

The only difference between this dialog box and others I currently have/use is that this one does not have any date fields. But I can't think why that would make a difference.

The tables used in the query that runs the report are linked tables that are linked in another database which is pulled from our accounting software. Could that have anything to do with it?

I'm at my wits end with this. I've been working on it for a few hours for a couple of days and am going crazy trying to figure it out. Any help would be greatly appreciated.

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[mp9]

Hi Jessica,

When you open the report first and then the dialog box, nothing happens when click OK becuase all that does is fire code to open the report. Since the report is already open, Access just switches focus back to the report rather than re-opening it from scratch.

When you open the form first, the form doesn't close after clicking OK because your OK_Click event procedure doesn't tell it to - you need a line something like

   DoCmd.Close acForm, "frmPGStatusDialog", acSaveNo

after your

DoCmd.OpenReport...

statement to achieve this.

...I think!

http://www22.brinkster.com/accessory/

 

[RacerGirl117]

I'll give that a shot and let you know if it works. Thanks for taking the time to help. Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[RacerGirl117]

MP9,

That worked! Thank you VERY much! I don't know why I didn't think of that myself. I tried that from every other angle I could think of in the report. Just didn't think to do it from the form. I marked your post as helpful.

Thanks again, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[mp9]

You're entirely welcome.

http://www22.brinkster.com/accessory/