Form Load Issues

RyanB

Registered User.
Local time
Today, 10:42
Joined
Jul 13, 2004
Messages
53
Hi All,

I have a form that will sometime contain no records for different users and I want a mesage to appear when there are no records waiting for them.

I have code in place to close the form when they run out of records to approve, so I copied that code thinking it would work on the form load if there were no records available, but nothing is different it just loads the blank form.

Here is the code i'm using:

Private Sub Form_Open(Cancel As Integer)
If Form.RecordsetClone.RecordCount > 0 Then
MsgBox "There are no requests awaiting your approval"
DoCmd.Close
End If
End Sub

Any ideas anyone??

Cheers,

Ryan
 
Ryan,

Oh, there's no "NoData" event for a form.

New turf for me.

Try:

Cancel = True

instead of:

DoCmd.Close

Wayne
 
Hi Wayne,

Tried that, still the same as before.

Updated code:

Private Sub Form_Open(Cancel As Integer)
If Form.RecordsetClone.RecordCount > 0 Then
MsgBox "There are no requests awaiting your approval"
Cancel = True
End If
End Sub
 
Ryan,

Another option is to check for records before opening the form, using the same criteria the form uses to get its records in, say, a DCount function.

Roughly...

Code:
Dim MyCriteria as Long
MyCriteria = 007     'or whatever...

IF DCount("Fieldname","TableorQueryName","FieldName=" & MyCriteria) > 0 Then
    DoCmd.OpenForm "FormName"
Else
   MsgBox "There are no requests awaiting your approval " & cstr(MyCriteria)
End if

Regards,
Tim
 
Hey Tim,

I couldn't get that code to work, not familiar with that stuff yet...

Thanks anyways
 
RyanB said:
Updated code:

Private Sub Form_Open(Cancel As Integer)
If Form.RecordsetClone.RecordCount > 0 Then
MsgBox "There are no requests awaiting your approval"
Cancel = True
End If
End Sub
That would only be true if there were records!

Try this...
Code:
Private Sub Form_Open(Cancel As Integer)
        
    If [COLOR=Blue]Me.RecordsetClone.RecordCount = 0[/COLOR] Then
        MsgBox "There are no requests awaiting your approval.", vbInformation, "No Requests Found"
        DoCmd.Close acForm, Me.Name
        [COLOR=Green]'Do not cancel the event, close the form![/COLOR]
    End If
        
End Sub
 

Users who are viewing this thread

Back
Top Bottom