Form Load Issues

[RyanB]

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

 

[WayneRyan]

Ryan,

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

New turf for me.

Try:

Cancel = True

instead of:

DoCmd.Close

Wayne

 

[RyanB]

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

 

[pono1]

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...

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

 

[RyanB]

Hey Tim,

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

Thanks anyways

 

[ghudson]

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...

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