If No Records found show message 1

[Samulayo]

I am creating a timesheet entry database for my company, which will allow users to enter their times worked each week, view previous timesheets, rejected timesheets etc. If we take rejected timesheets as an example, if there are no rejected timesheets, how do i get a message to be shown instead of opening the form (or as well as, but closes form once butter pressed). Standard SQL used atm and show below.
(where "*" = all fields used)

SELECT * FROM [Add TimeSheet] INNER JOIN Personnel ON [Add TimeSheet].Name = Personnel.UserName
WHERE ((([Add TimeSheet].Name)=Environ("username&quot) AND (([Add TimeSheet].STATUS)="REJECTED&quot);

Any help will be greatly recieved

Regards
Samulayo

 

[danvlas]

thread702-559352

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[chickey]

DAO method:

--

Dim Db as database
Dim Rs as recordset

set Db = CurrentDb()

SQLStr = "SELECT * FROM [Add TimeSheet] INNER JOIN Personnel ON [Add TimeSheet].Name = Personnel.UserName
WHERE ((([Add TimeSheet].Name)=Environ("username&quot) AND (([Add TimeSheet].STATUS)="REJECTED&quot);"

set Rs = Db.openrecordset(SQlstr, dbOpenSnapshot)
if not (Rs.eof and Rs.bof) then
docmd.openform ....
else
msgbox "No rejected records exist - cancelling ..."
endif

Rs.close

--

Regards...

 

[Samulayo]

Thank You both,

I am using Daniel Vlas' Sub as this is the most compact and is exactly what i was looking for.

Regards

Samulayo

Appendix:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "Closing - incorrect parameter"
End If
End Sub