To display an error message when there's no data found

[Kitana]

Hi guys

I need some help here. It may be a simple problem to some of u but I don't have much experience in Access

I have a dialog box that ask user to enter the full name of student so that it can display a form that have the details of the student.

I would like an error message to pops up when the user entered a non-existing student. Under which event should I put the code for displaying the error message?
I'm planning to put these code here in the event (it has the same purpose with what I want but this is an error msg of non-existing student for a report of student's record)


Private Sub Report_NoData(Cancel As Integer)

' Display a message if user enters a name of student for which there are no records,
' and don't preview or print report.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "You must enter a valid student's name"
intStyle = vbOKOnly
strTitle = "No Data for Student"

MsgBox strMsg, intStyle, strTitle
Cancel = True

End Sub


Can I use the same code for the form as well?

Thanks in advance

 

[bjackson]

i think you would have to use a query
substitute tablename with your table name
and field name with your field name


Dim Dbs As DAO.Database
Dim Qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set Dbs = CurrentDb()
Set Qdf = Dbs.CreateQueryDef("", "SELECT tablename.fieldname,FROM tablename" _
& " " & "WHERE (((tablename.fieldname)=" & [Forms]![formname]![textboxname] & "));")
Set rst = Qdf.OpenRecordset
With rst
If not rst.BOF And not rst.EOF Then
'that means theres a match

rst.Close
Set rst= Nothing
Set Qdf = Nothing
Set Dbs = Nothing
Exit sub

Else
beep
msgbox" You must enter a valid student's name",vbokonly,"No data For student"

rst.Close
Set rst = Nothing
Set Qdf = Nothing
Set Dbs = Nothing
AddRun
End If
End With

 

[bjackson]

sorry i left a variable name of mine in the code
delete the word ADDRUN at the bot

 

[Rich]

Private Sub Form_Open(Cancel As Integer)
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "Please enter a valid Student name.", vbInformation, "No Data"
End If

End Sub
However if you use a combo box to select Students the problem shouldn't arise in the first place

 

[Kitana]

thanks bjackson and Rich

At first, i wanted to use a combo box for the student's name to avoid user's typing error, but it seems inappropriate because there's hundreds of students records.

I'll try the codes this evening. Gotta run to class.

 

[Kitana]

Hey Rich
Your code works. Thanks