Forms - Which event?

[Garry]

I have a form based on a query. It is possible that the form may be opened
when the recordset of the query is empty. In order to avoid an ungly error
message I would like to trap this by opening a recordset based on the query
and check the recordset's recordCount. If empty, display a message, then
close the form. I have tried several form events to do this, but can't seem
to find the right combination.
Any help greatly appreciated.
Thanks -- Garry Gross

 

[Dirk Goldgar]

I have a form based on a query. It is possible that the form may
be opened when the recordset of the query is empty. In order to
avoid an ungly error message I would like to trap this by opening a
recordset based on the query and check the recordset's recordCount.
If empty, display a message, then close the form. I have tried
several form events to do this, but can't seem to find the right
combination. Any help greatly appreciated.
Thanks -- Garry Gross

How about using code like this in the form's Load event?

'----- start of code -----
Private Sub Form_Load()

If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close acForm, Me.Name, acSaveNo
MsgBox "There are no records to display!", _
vbInformation, "No Records"
End If

End Sub

'----- end of code -----

 

[Garry]

Dirk,
Tried that & get the msgBox the form closes OK, but then get an error
msg "Run Time Error 3021 - No current records." The code the debugger points
to is in the forms OnCurrent event
recClone.Bookmark = Me.Bookmark
where I normally chec to see whether to enable or disable command buttons
for "NEXT" and "PREVIOUS" based on where in the recordset the bookmark is -
ie, if recClone.MoveNext results in an EOF, then I disable the "NEXT" button.

Garry

 

[Garry]

Additional information. Does not work if I put the code only in the OnLoad
event (see previous reply), However, if I put it in BOTH the OnLoad and
OnCurrent events (leaving out one of the msgBox's) OR if I put the code in
BOT the OnOpen and OnCurrent events, it works fine. I thought the OnLoad
event occurred before the OnCurrent, any ideas why it would continue on to
the OnCurrent code when the OnLoad event closes the form?

Garry

 

[Dirk Goldgar]

Additional information. Does not work if I put the code only in
the OnLoad event (see previous reply), However, if I put it in BOTH
the OnLoad and OnCurrent events (leaving out one of the msgBox's) OR
if I put the code in BOT the OnOpen and OnCurrent events, it works
fine. I thought the OnLoad event occurred before the OnCurrent, any
ideas why it would continue on to the OnCurrent code when the OnLoad
event closes the form?

It doesn't work that way for my test form, but I have no doubt that
you're experiencing what you say. When you open the form, various
things happen: opening the form, loading its recordset, making the
first record of the recordset current on the form, and so on. When you
call DoCmd.Close, that sends a request to access to close the form, but
that request appears to be processed in a separate thread. It seems
that in your case, the sequence of form events proceeds all the way up
to the Current event before the request to close is honored.

If I were you, I'd work around this by having a module-level flag
"fClosing" in the form's module, setting it in the Load event if the
form is being closed, and testing it in the form's Current event before
going any farther. Like ths:

'----- start of code for form's module -----
Option Compare Database
Option Explicit

Dim fClosing As Boolean

Private Sub Form_Current()

If Not fClosing Then

' ... code here to set the buttons ...

End If

End Sub


Private Sub Form_Load()

If Me.RecordsetClone.RecordCount = 0 Then
fClosing = True
DoCmd.Close acForm, Me.Name, acSaveNo
MsgBox "There are no records to display!", _
vbInformation, "No Records"
End If

End Sub
'----- end of code for form's module -----

 

[Garry]

Dirk -- Thanks
I'm beginning to think that Access is 'not for us mere mortals"
gARRY gROSS

 

[Garry]

Dirk,

FYI - I discovered a VERY CLEAN way to accomplish this. In the forms OnOpen
event :

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close acForm, "frmRtnShip_Received-2", acSaveNo
MsgBox "There are no records to display!", _
vbInformation, "No Records"
Cancel = True
End If
End Sub

Doing it this way does not require the second test of the forms recordcount
in the OnCurrent event.

---- Garry

 

[Dirk Goldgar]

Dirk,

FYI - I discovered a VERY CLEAN way to accomplish this. In the forms
OnOpen event :

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close acForm, "frmRtnShip_Received-2", acSaveNo
MsgBox "There are no records to display!", _
vbInformation, "No Records"
Cancel = True
End If
End Sub

The only reason I didn't recommend that before is that, if you cancel
the form's open event, that cancel will be reflected back as an error --
run-time error 2501 -- to whatever code opened the form. So then you
have to trap or explicitly ignore that error in the calling code. That
is in fact the way I would do it, but I thought you were looking for a
way to avoid having to modify the calling code in any way.

If this way works for you, all the better. But if
"frmRtnShip_Received-2" is the form being opened, with the code you
posted in its Open event, you don't need the line

DoCmd.Close acForm, "frmRtnShip_Received-2", acSaveNo

Just cancelling the Open event will keep the form from opening, so you
don't need to close it.

Doing it this way does not require the second test of the forms
recordcount in the OnCurrent event.

As I said before, you didn't need to test the record count again in the
Current event; all you would have to do is set a flag, as I described
in my previous message. But I agree completely that cancelling the Open
event is simpler.

 

[Garry]

Dirk,
I probably should have mentioned that the form is opened by a
macro not code, so I don't get the run time error.

Garry

 

[Dirk Goldgar]

Dirk,
I probably should have mentioned that the form is opened by
a macro not code, so I don't get the run time error.

Ah, that explains it. I'm afraid that didn't even occur to me.