VBOkCancel to Close a report/open a form 1

[Catt665]

I have a Report that is designed to respond to a form so I have put a bit of VBA code on the "On Open" property of the form. What I *want* to happen is that a message box appears telling the user that the report is reliant upon the form and give them the option of opening the form.
What I have is:

Private Sub Report_Open(Cancel As Integer)

' Don't open report if MembersDialogBox form isn't loaded.

If Not (IsLoaded("frmSetDate")) Then

Cancel = True

MsgBox "To preview or print this report, you must open " & "frmSelectJoinDate in Form view. Do you wish to open this form?", vbOKCancel, "Must Open Selection form"

If vbOk Then
DoCmd.Close "Listing by Membership Type"
DoCmd.OpenForm "frmSetDate"
Else
DoCmd.Close "Listing by Membership Type"
End If

Exit Sub

End If

End Sub

It brings up the message box, but when you click Ok, it gives the error message "Run-time error 13, Data type mismatch". What am I doing wrong?
Thanks
Catt

 

[jrbarnett]

The Type mismatch is likely to be caused by this line:

DoCmd.Close "Listing by Membership Type"

You need to specify the type of object to close, eg:

DoCmd.Close acForm, "Listing by Membership Type"

if you don't specify a type (acForm/acReport/acModule etc), it will close the current form/report/module etc.

To have the result of the messagebox affect the result, you either have to assign the result of the MsgBox function to a variable and compare that (eg

intResult = MsgBox ("To preview or print this report, you must open " & "frmSelectJoinDate in Form view. Do you wish to open this form?", vbOKCancel, "Must Open Selection form")

Then:

If intResult = vbOk Then ...

or you can call the function in the comparison:

If MsgBox ("To preview or print this report, you must open " & "frmSelectJoinDate in Form view. Do you wish to open this form?", vbOKCancel, "Must Open Selection form") = vbOk Then
...

Oh, and one other thing: if you set "Cancel=True" it will stop the form loading, so you only want to set it if they choose Cancel on the form.

John

 

[Catt665]

Thanks for that (I am still learning).
But it still brings up an error message.
The error message is Run-time error '2585':
This action can't be carried out while processing a form or report event
For the record what I now have is:
Public Sub Report_Open(intResult As Integer)

' Don't open report if MembersDialogBox form isn't loaded.

If Not (IsLoaded("frmSetDate")) Then

intResult = MsgBox("To preview or print this report, you must open " & "frmSelectJoinDate in Form view. Do you wish to open this form?", vbOKCancel, "Must Open Selection form")

If intResult = vbOK Then
DoCmd.Close acReport, "Listing by Membership Type"
DoCmd.OpenForm "frmSetDate"
Else
DoCmd.Close "Listing by Membership Type"
End If

Exit Sub

End If

End Sub