No Data Selected for Form

[Rillo]

I have a form based on a query.
In the event that the query does not find any matching records, I want to
inform the user that this is the case.
At the moment all that happens is that a blank form appears.

I want to avoid using code if possible, so I have created a macro as
follows.....

IsNull([Forms]![Enquiry by Loan Expiry Date]![Description]) MsgBox

IsNull([Forms]![Enquiry by Loan Expiry Date]![Description]) Close Form

The intention is that a message box will be displayed to the effect that
there are no matching records and the form will then be closed.

I can't seem to work out into which event on my form I should insert the
macro.

Please advise.

 

[Tom Wickerath]

Hi Rillo,

I can't seem to work out into which event on my form I should insert the
macro.

The following VBA code works in the Form_Open event. Not sure if it is
possible to cancle opening a form using a macro, as I pretty much avoid the
use of macros like the plague.

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

Dim strMessage As String
Dim strTitle As String

strMessage = "There are no records for the selected criteria."
strTitle = Me.Name & " form. No Records Available..."

If Me.Recordset.RecordCount = 0 Then
MsgBox strMessage, vbInformation, strTitle
Cancel = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Open Procedure..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I have a form based on a query.
In the event that the query does not find any matching records, I want to
inform the user that this is the case.
At the moment all that happens is that a blank form appears.

I want to avoid using code if possible, so I have created a macro as
follows.....

IsNull([Forms]![Enquiry by Loan Expiry Date]![Description]) MsgBox

IsNull([Forms]![Enquiry by Loan Expiry Date]![Description]) Close Form

The intention is that a message box will be displayed to the effect that
there are no matching records and the form will then be closed.

I can't seem to work out into which event on my form I should insert the
macro.

Please advise.

 

[Theresa]

Hi Tom and Rillo,
I simply want to close my form if it has no data. I have tried your code
below in the Form - On Open event but the form does not close.

If Me.Recordset.RecordCount = 0 Then
MsgBox strMessage, vbInformation, strTitle
Cancel = True
End If

Should the code be placed on another event?
Note: the form is linked to an underlying query.

 

[Tom Wickerath]

Hi Theresa,

I'm not understanding the issue that you are experiencing, because the line
of code that reads:
Cancel = True

should cancel opening of the form. Therefore, it should not have even opened
successfully...

Should the code be placed on another event?

No.

Is the message box statement being displayed?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[javablood]

Tom,

Thanks, that is just what I needed to know. Now, how do I surpress the
Error 3021: No Current Record. message?

I commented out the ProcError but the MS error (above) keeps coming up after
the first message box comes up and after the program returns me to the form
from which I called the other form to open. I also set warnings off until I
leave the sub.

perplexed.