If records all filtered out - give me a message

[Guest]

Hi ya'll,

I am opening a form from a switchboard button (running a macro) that opens a
form with the records filtered. Works fine but I would like to pop up a
message box that says "No records" and then close the form (or never open it)
if there are no records. Any one have an idea of the best way to test for
any records before running the open form procedure?

Thanks in advance.

 

[Jeff Boyce]

Bonnie

One approach would be to put code behind the button click that opens a
recordset and counts the number of rows returned. If there aren't any, show
the message box and close the form (or skip opening it).

Another approach would be to open the form without any records. Add a combo
box (unbound) on the form to allow the user to do the filtering. Requery
the form in the combo box's AfterUpdate event. The query that feeds the
form needs to "point" to the form's combo box for the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff, thanks for responding.

I have been trying to do what you suggested in option one but I'm having
trouble with my code. Can you give me a snippet of code for an example?

Thanks.

 

[Jeff Boyce]

Bonnie

What have you tried that hasn't worked?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Van T. Dinh]

Another approach is to use the Form_Open Event of the Form being opened to
check whether the Form's Recordset is empty or not. If it is empty, post a
simple MsgBox and then set the Cancel argument to True (which will cancel
the opeing of the Form)

The code should be something like:

****
Private Sub Form_Open(Cancel As Integer)

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No data. Cancelling ..."
Cancel = True
End If
End Sub
****


*However*, this will return the error 2501 (action cancelled) to the
CommandButton_Click Event so you need to trap & ignore error 2501. The code
should be something like:

****
Private Sub cmdSave_Click()

On Error GoTo cmdSave_Click_Err
'{Your code here}

cmdSave_Click_Exit:
Exit Sub

cmdSave_Click_Err:
Select Case Err.Number
Case 2501
' Error cause by Cancel set to true. Ignore.

Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: Form_frmProdFactWeight.cmdSave_Click)", _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume cmdSave_Click_Exit
End Sub
****

 

[Guest]

Thank you Van. I actually figured it out. I used the DCount function and
put in a similar little IF ENDIF routine.

Appreciate your help.

 

[Van T. Dinh]

Slightly more efficient to use the Recordset of the Form being opened but
hey, it works either way ...