Error Msg Macro that pops up when no records found (1 Viewer)

[Marbinski]

I have a form that a user chooses criteria to query the data in a table. After the user chooses the data they click on a button that opens a form based on a query that pulls from the criteria the user picked. I want to display a msg box saying "no records found" if the query has no values in it. Would I have to program this in a macro or is there a way to do it by using VB? Any help would be great. Thanks

 

[ecniv]

Stay away from macros if you can help it.

Open a recordset using an Sql statement and check for recordsetobject.EOF (End of file) which indicates no records returned if its true when opening. If its false then there is at least one record. So you could then set the sub form/listbox/control as required to display.


Vince

 

[Rich]

Private Sub Form_Open(Cancel As Integer)

With CodeContextObject
If (.RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There are no invoices recorded for this period.Please check the dates you have entered.", vbOKOnly, "No Records"

End If
End With

Exit Sub

 

[Marbinski]

I'm not so sure where to place that code that was supplied. In my situation I have a button that is supposed to open a form. Here is the code:
***************************************
Private Sub Net7Results_Click()
On Error GoTo Err_Net7Results_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FRMNet7"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Net7Results_Click:
Exit Sub

Err_Net7Results_Click:
MsgBox Err.Description
Resume Exit_Net7Results_Click

End Sub

****************************************

The form is named "FRMNet7" that is based on the query. Where would i put the "no records found" code with reference to the button. Any help would be great. Thanks so much in advance!!

 

[Rich]

In the OnOpen event of form FRMNet7