Display a msgbox rather then a blank form

[shaunacol]

I am setting up a message taking system and have a qyuery behind a form where all the messages for a particular person are listed.

However, if there are no messages (records) for that person then the form opens up and is blank. Is there any way I can put in an IF statement saying - if form (or query) is blank then do not open form but display message box saying "There are no messages for this person".

I hope this makes sense, I am really struggling with the syntaxfor this one.

Thanks

 

[egapogi]

Just check the query first before openiong the form....

Maybe a simple dcount will do.

I mean the query which is bounded to your form.

cheers,

 

[a9]

In this situation - assuming you are using a form to list the people then opening a second form I usually do the following.

Dim myDb As Database, MyRst As Recordset, Recs As Integer, SQLString As String
Dim stDocName As String, stLinkCriteria As String
Set myDb = CurrentDb
SQLString = "SELECT Count(*) AS RECORDS FROM tabSalesDetails WHERE (((tabSalesDetails.Payroll_Number)='" & Me![Payroll_Number] & "'));"
Set MyRst = myDb.OpenRecordset(SQLString, dbOpenDynaset)
MyRst.MoveFirst
Recs = MyRst!records
MyRst.close
Set myDb = Nothing
If Recs > 0 Then
stDocName = "Sales Personal Details Form"

stLinkCriteria = "[Payroll_Number]=" & "'" & Me![Payroll_Number] & "'"
Screen.PreviousControl.SetFocus

DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
End If

In this case the form either opens showing the data in the system for the selected person, or opens in add mode so a new record can be added.

 

[LeighW]

Maybe This would be easier:

Private Sub Form_Load()
If RecordsetClone.RecordCount = 0 Then
MsgBox "No Records"
DoCmd.Close
End If
End Sub