Informed message when no record found

[Jeff]

Hi,
I have a page to let user type in a"key" and search my database. Then
another page is opened to display the result. My code is as follow:

Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click

Dim strSQL As String
strSQL = "[tbPatient].[Name]=Forms![frmSearch].[Name]"

DoCmd.OpenForm "frmSearchResult", , , strSQL
Exit_btnSearch_Click:
Exit Sub

Err_btnSearch_Click:
MsgBox Err.Description
Resume Exit_btnSearch_Click

End Sub

My problem is when no record is found after search, the display page
"frmSearchResult" seems to partly open. It only has background and all the
controls on it disappeared. I'd like to let it display an informed message
like "No record is found, please search again". How can I do that? Thank you.

 

[RonaldoOneNil]

First of all 'Name' is a bad field name to use in your table because it is a
reserved word in access. Also, I suspect Name is a text field so it should be
enclosed by single quotes. However, you can use a DLookup function with the
same criteria before you open your form. Dlookup will return Null if no
record is found.

strSQL = "[tbPatient].[Name]='" & Forms![frmSearch].[Name] & "'"
If IsNull(Dlookup("[Name]","tbPatient","[Name] = '" &
Forms![frmSearch].[Name] & "'")) Then
Msgbox "No record is found, please search again"
Else
DoCmd.OpenForm "frmSearchResult", , , strSQL
End If

 

[Jeff]

Hi Ronaldo

Thank you very much. It works.
--
Jeff


"RonaldoOneNil" 來函：

First of all 'Name' is a bad field name to use in your table because it is a
reserved word in access. Also, I suspect Name is a text field so it should be
enclosed by single quotes. However, you can use a DLookup function with the
same criteria before you open your form. Dlookup will return Null if no
record is found.

strSQL = "[tbPatient].[Name]='" & Forms![frmSearch].[Name] & "'"
If IsNull(Dlookup("[Name]","tbPatient","[Name] = '" &
Forms![frmSearch].[Name] & "'")) Then
Msgbox "No record is found, please search again"
Else
DoCmd.OpenForm "frmSearchResult", , , strSQL
End If

Hi,
I have a page to let user type in a"key" and search my database. Then
another page is opened to display the result. My code is as follow:

Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click

Dim strSQL As String
strSQL = "[tbPatient].[Name]=Forms![frmSearch].[Name]"

DoCmd.OpenForm "frmSearchResult", , , strSQL
Exit_btnSearch_Click:
Exit Sub

Err_btnSearch_Click:
MsgBox Err.Description
Resume Exit_btnSearch_Click

End Sub

My problem is when no record is found after search, the display page
"frmSearchResult" seems to partly open. It only has background and all the
controls on it disappeared. I'd like to let it display an informed message
like "No record is found, please search again". How can I do that? Thank you.