Display message when records not found

[nithasha]

Hi,

I need to display a message " NO EMPLOYEES TO REPORT" after the Top Title in a SQLPLUS report when no rows are retrieved by a select statement.
So how do i go about?

Regards,
Nithasha.

[nitinnkn]

See Nitasha will this helps to you.
what i does make the connection fired 2 qrys count(*) and select * if found then i get the no. of record found in the rcrdfound variable as shown below and if rcrdfound>0 means i got 1 record atleast else i display record not found message, ignore rest of the things as this is related to my form.



Public Function func_findplcno(theplcno As String) As Integer
Set con = New ADODB.Connection
con.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\database\licdb.mdb;" & _
"Uid=Admin;Pwd=tingtong;"
Set recset = New ADODB.Recordset
recset.Open "Select count(*) from Main where plcno='" & theplcno & "';", con, adOpenDynamic, adLockOptimistic, adCmdText
rcrdfound = recset.Fields(0)
recset.Close
recset.Open "Select * from Main where plcno='" & theplcno & "';", con, adOpenDynamic, adLockOptimistic, adCmdText

If rcrdfound > 0 Then
' if found then iam going to display that record


txttbltrm.Text = dbStr$(recset("tbltrm"))
txtmtdt.Text = dbStr$(recset("mtdt"))
cmbmode.Text = dbStr$(recset("mode"))
txtdob.Text = dbStr$(recset("dob"))
txtplcno.Text = dbStr$(recset("plcno"))
txtprmm.Text = dbStr$(recset("prmm"))
txtsmasrd.Text = dbStr$(recset("smasrd"))
txtname.Text = dbStr$(recset("name"))
txtfname.Text = dbStr$(recset("fname"))
txtcoadrs.Text = dbStr$(recset("coadrs"))
txtemail.Text = dbStr$(recset("email"))
txtmobl.Text = dbStr$(recset("mobl"))
txtteloff.Text = dbStr$(recset("teloff"))
txttelres.Text = dbStr$(recset("telres"))
txtptadrs.Text = dbStr$(recset("ptadrs"))
txtocptn.Text = dbStr$(recset("ocptn"))
txtpost.Text = dbStr$(recset("post"))
txtnmemplyr.Text = dbStr$(recset("nmemplyr"))
txtlosrvc.Text = dbStr$(recset("losrvc"))
txtnomnm.Text = dbStr$(recset("nomnm"))
txtrltn.Text = dbStr$(recset("rltn"))
txtage.Text = dbStr$(recset("age"))
txtdudt.Text = dbStr$(recset("dudt"))
txtpmtdt.Text = dbStr$(recset("pmtdt"))
txtcomsn.Text = dbStr$(recset("comsn"))


' Form1.txttbltrm.SetFocus
Form1.ForeColor = &HFF
Form1.Caption = "Well here you are make the changes you desire (Be cautious in dates!)" & _
"and don't forget to press 'Update' to save the changes done."
Set con = Nothing
Set recset = Nothing
func_findplcno = 1

Else
' if not found then iam going to display this message

MsgBox "Policy No. You entered was Not Found!", vbExclamation, "Check Policy Number"
get_plcno.Text1.SetFocus
func_findplcno = 0
End If
End Function