No Record Found

[Mike from Moriches]

I have a simple form for updating records. I do a DoCmd.Openform with a
WHERE clause that works great when there is a record that matches the WHERE
clause. The form opens populated with data from the record. My problem is
when the User keys in wrong information and no record is returned. The form
opens with no data. Is there something similiar to the "No Data" event in a
report that I can use to display an error message rather than the blank
form?
Thanks
Mike N.

 

[Marshall Barton]

I have a simple form for updating records. I do a DoCmd.Openform with a
WHERE clause that works great when there is a record that matches the WHERE
clause. The form opens populated with data from the record. My problem is
when the User keys in wrong information and no record is returned. The form
opens with no data. Is there something similiar to the "No Data" event in a
report that I can use to display an error message rather than the blank
form?

In the form's Load event:

If Me.NewRecord Then
MsgBox "No matching record"
DoCmd.Close acForm, Me.Name, acSaveNo
End If

 

[Arvin Meyer [MVP]]

You can build a recordset that displays the message before you open the form
like (untested):

Private Sub cmdOpenForm_Click()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL as String

strSQL = "Select * From tblEmployees Where EmpID = & Me.txtEmpID

Set db = CurrentDb
Set rst = db.OpenRecordset (strSQL)

With rst
.MoveLast
If .RecordCount > 0 Then
DoCmd.OpenForm "frmEmployees", , , "[EmpID]=" & Me.txtEmpID
Else
MsgBox "Sorry, no records", vbOKOnly, "No Records"
End If
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

 

[Mike from Moriches]

Thank you all for the help. I went with Chris's DCount approach which works
perfectly.