Report Events to Trigger Update Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Henry Stockbridge - 31 Mar 2006 15:18 GMT

Hi,

I need a way of bypassing the Report_Close procedure (or come up with
another event to handle the Update Query.) Right now, if I set the
value of Report_NoData to Cancel=True, the Report_Close event fires,
and the message prompt is not applicable.

Here is the code:

Private Sub Report_Close()
Dim varResponse As Variant
Dim strSQL As String

strSQL = "UPDATE tblOpenAccts INNER JOIN tblDFU_Hospitals ON " _
& "tblOpenAccts.hosCustomerID = tblDFU_Hospitals.PrimInstID SET
" _
& "tblOpenAccts.OpenedAccount = -1,tblOpenAccts.ModifiedDate =
Now(), " _
& "tblOpenAccts.ModifiedBy = Environ('Username');"

varResponse = MsgBox("These accounts are new." & vbCrLf & vbCrLf _
& "Do you wish to flag these accounts as opened?", vbQuestion +
vbYesNo)

If varResponse = vbYes Then
' Flag the accounts
DoCmd.RunSQL strSQL
End If

End Sub
'===========================
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No accounts to update"
'Cancel = True
'Need to close form without running Report_Close
procedure
End Sub
'=======================

Any help you can lend would be appreciated.

Henry

Reply to this Message

Tom van Stiphout - 31 Mar 2006 15:30 GMT

Seems to me you should set a flag in Report_NoData:
At the report module level:
Dim m_blnClosedByNoData as Boolean

In Report_NoData:
m_blnClosedByNoData = True

Test it in Report_Close:
if m_blnClosedByNoData then
'Nothing to do
else
'Update query goes here
end if

-Tom.

>Hi,
>
[quoted text clipped - 38 lines]
>
>Henry

Reply to this Message

Arno R - 31 Mar 2006 15:42 GMT

> Hi,
>
[quoted text clipped - 38 lines]
>
> Henry

You can't close the report without firing the close event...
(would be very weird if this was possible...)
Use another var to determine if you will ask for flagging the accounts.

In your reports module add:
Dim bolDoNotAsk as boolean

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No accounts to update"
bolDoNotAsk=True
Cancel = True
End Sub

Private Sub Report_Close()
Dim varResponse As Variant
Dim strSQL As String
If bolDoNotAsk=True then exit sub

<your other code here>
End sub

Arno R

Reply to this Message

Henry Stockbridge - 31 Mar 2006 16:09 GMT

Thanks. I'm all set now.

Reply to this Message