Using Option Buttons, select case and strcriteria. How do I show a message BOX.

[MAX via AccessMonster.com]

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]

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

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

 

[John Vinson]

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

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]

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

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.