Forms With No Records

[saross]

I have several forms based on queries which rely on user input to return relevant records. E.g. FrmInputLName requires the user to enter a lastname. FrmLastNames is then opened which displays the results, a query based on the name entered in the txtbox in FrmInputLName.
This is the code I use:

If IsNull(Me.txtInputLName) Then
MsgBox "Please enter a surname", vbOKOnly
Me.txtInputLName.SetFocus
Else
DoCmd.OpenForm "FrmLastNames"
DoCmd.Close acForm, "FrmInputLName"
DoCmd.Close acForm, "FrmMain"
DoCmd.Maximize
End If

The problem is, sometimes no records are returned by the query and the user faces a blank screen with no explanation. How can I get round this so the form only opens if there are records to display and if there aren't then a message displays showing that there are no matching records?

 

[Mile-O]

Use the form's OnOpen event to check if the recordset will return any records and, if the count is 0, use the Cancel = True argument to cancel the opening of the form.

 

[saross]

Erm...

I'm afraid I don't know how to do that...???

 

[Mile-O]

Private Sub Form_Open(Cancel As Integer)
 
    If DCount("Field", "qryRecordset") = 0 Then
        MsgBox "No records found.", vbExclamation, "Example"
        Cancel = True
    End If

End Sub

where the DCount is counting the number of records in the form's recordset.

 

[saross]

Thanks!

That works perfectly!!

 

[SillyMcGilly]

Thanks!

Hey, I needed just this info for the same reason. I have a form that the border style is "Dialog" and there is no "Close" option on the window when the form pops up. The user must click a button "Return to Main Menu" to update the record. The problem I had was that if no records were there the form popped-up...blank and the "Return to Main Menu" button was NOT visible (since there were no records.) The user had no way to close the window! The above code worked perfectly for me...and closed the form automatically if no records. Thanks!!