Paramater value cancel button errors

REVNED · September 1st, 2008, 05:39 AM

hello every1!

I am really new to Access 2003....
Hoping somebody out there to help me with my codes...
i have the following codes, want code do exactly i need so that everytime i click the cancel in parameter value it will not debug or creates errors.

Private Sub Command110_Click()
Dim Answer As String
Dim MyNote As String

    'Prompt this message to user
    MyNote = "Sort records by Discipline." & _
              vbCrLf & "" & _
             vbCrLf & "Refer to SAUDI ARAMCO STANDARD List " & _
             "if you are not sure of the Drawing Type you want to sort." & _
             vbCrLf & "Please do not attempt to debug the codes if encountered errors." & _
             vbCrLf & "Database will display plain form if no records found, " & _
             "just right click and click on Remove Filter."

    'User response
    Answer = MsgBox(MyNote, vbOKCancel + vbInformation, "Sort Records")

     If Answer = vbOK Then
        'User should enter site name
        Answer = MsgBox("Enter Discipline", vbOKCancel + vbInformation, "Aindar")

     Else
        'Cancel event.
        DoCmd.CancelEvent
        End If

        If Answer = vbCancel Then
        'Cancel Event
        DoCmd.CancelEvent

        Else
        DoCmd.ApplyFilter , "[Discipline]= [Enter Discipline]"
        End If
End Sub

BrianWren · September 2nd, 2008, 01:23 PM

Not "codes," code. It is not "they are codes," but "it is code."

For readability, it is a good idea to indent everything that is part of a conceptual block. The creation of variables within a routine fit that descritpion. So rather than:

Code:

Private Sub Command110_Click()
On Error GoTo xxx
Dim Answer As String
Dim MyNote As String
...

it is better, and easier to read to have:

Code:

Private Sub Command110_Click()

    On Error GoTo xxx

    Dim Answer As String
    Dim MyNote As String
    ...

Conceptually, carriage returns are at the end of the line. Your string concatenations will be easier to read if you lay them out that way:

Code:

    MyNote = "Sort records by Discipline." & vbCrLf & vbCrLf & _
             "Refer to SAUDI ARAMCO STANDARD List if you " & _
             "are not sure of the Drawing Type you want to sort." & vbCrLf & _
             "Please do not attempt to debug the code if you encounter errors." & vbCrLf & _
             "Database will display plain form if no records found." & vbcrlf & _
             "Just right click and click on Remove Filter."

This indentation of the End If is dangerous. It hides the fact that the If block is ending:

Code:

     If Answer = vbOK Then
        'User should enter site name
        Answer = MsgBox("Enter Discipline", vbOKCancel + vbInformation, "Aindar")

     Else
        'Cancel event.
        DoCmd.CancelEvent
        End If

Should be:

Code:

     If Answer = vbOK Then
        ' User should enter site name
        Answer = MsgBox("Enter Discipline", vbOKCancel OR vbInformation, "Aindar")

     Else
        ' Cancel event.
        DoCmd.CancelEvent
     End If

This is also bad, because you are gathering the variable to use in the next block within this block. That is confusing. Additionally, there doesn't appear to be any current event to cancel. You are in a sub routine, so you should just leave the routine without taking action if they have clicked the Cancel button on the message box (or closed the message box with the [x] in the corner:

Code:

     If Answer <> vbOK Then
        ' Cancel event.
        Exit Sub
     End If

Then follow that with the next test, and action.

Code:

Private Sub Command110_Click()

Code:

    Dim Answer As String
    Dim MyNote As String

    ' Prompt this message to user
    MyNote = "Sort records by Discipline." & vbCrLf & vbCrLf & _
             "Refer to SAUDI ARAMCO STANDARD List if you are " & _
             "not sure of the Drawing Type you want to sort." & vbCrLf & _
             "Please do not attempt to debug the code if you encounter errors." & vbCrLf & _
             "Database will display plain form if no records found." & vbCrLf & _
             "Just right click and click on Remove Filter."

    ' User response
    Answer = MsgBox(MyNote, vbOKCancel Or vbInformation, "Sort Records")

    If Answer <> vbOK Then
        Exit Sub
    End If

    ' If we get to here, user should enter site name
    Answer = MsgBox("Enter Discipline", vbOKCancel Or vbInformation, "Aindar")

    If Answer <> vbOK Then
        Exit Sub
    End If

    DoCmd.ApplyFilter, "[Discipline]= [Enter Discipline]"

End Sub