Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / March 2005

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MAX - 02 Mar 2005 16:20 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
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)

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.