I am using the code below on [Event Procedure]of the option Buttons. This
opens my form correctly BUT it also opens a blank form when No job number
matches the criteria. Is there a way I can create a message box to show
there is know match.
Private Sub Value_Click()
Select Case Me!Value
Case 1
stDocName = "1 master cis"
stLinkCriteria = "[job number]=" & Me![Job Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
Case 2
stDocName = "1 master cis"
stLinkCriteria = "[job number]=" & Me![Job Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
Case 3
stDocName = "1 master cis"
stLinkCriteria = "[job number]=" & Me![Job Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
Case Else
strFilterSQL = strSQL & ";"
End Select
End Sub
Thanks
Max
Dirk Goldgar - 02 Mar 2005 18:29 GMT
> I am using the code below on [Event Procedure]of the option Buttons.
> This opens my form correctly BUT it also opens a blank form when No
[quoted text clipped - 23 lines]
>
> Max
I trust this is code that is in development, as otherwise I don't see
why you'd be opening the same form, in the same mode, with the same
criteria, for all three of those options. By the way, "Value" is a
terrible name for a control, as most controls and many other objects
have a Value property, and it's easy to confuse Access and VB that way.
Anyway, to get to your question: I prefer to handle this by having the
form itself check, in its Open event, whether there are any records. If
there aren't any records, the form can display a message box and cancel
the Open event. Like this:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records that meet your criteria"
Cancel = True
End If
End Sub
Cancelling the form's Open event will raise error 2501 in the code
procedure that opens it, though, so that procedure has to have
error-handling in place to trap and ignore that particular error:
On Error GoTo Err_Handler
' ...
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
' ...
Exit_Point:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point
End Sub

Signature
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
John Vinson - 02 Mar 2005 19:40 GMT
>I am using the code below on [Event Procedure]of the option Buttons. This
>opens my form correctly BUT it also opens a blank form when No job number
[quoted text clipped - 19 lines]
>End Select
>End Sub
This is rather odd code: all three options do exactly the same thing,
open the form named [1 master cis] using the job number!
What's the point??
Without knowing the recordsource of this form (or these forms, if
you're intending to open three different forms in the real app), it's
hard to say - but you could use DLookUp to see if the job number
exists in the Form's table, and only open the form if it does. Or, you
could put code in the Form's Load event:
Private Sub Form_Load(Cancel as Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No data found"
Cancel = True
End If
End Sub
John W. Vinson[MVP]
Dirk Goldgar - 02 Mar 2005 19:57 GMT
> Without knowing the recordsource of this form (or these forms, if
> you're intending to open three different forms in the real app), it's
[quoted text clipped - 8 lines]
> End If
> End Sub
I see we think along the same lines. But you can't cancel the form's
Load event. You have to use the Open event for this. Although it now
occurs to me that if you want to avoid having error 2501 raised at all,
you could put the test in the Load event and just close the form if
there are no records:
'----- start of example code (Access 2000+) -----
Private Sub Form_Load()
If Me.Recordset.RecordCount = 0 Then
MsgBox "No records to display"
DoCmd.Close acForm, Me.Name
End If
End Sub
'----- end of example code -----
I never thought of that before, but it seems to work in my quick test.

Signature
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)