Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Unhappy Unanswered: loops back to the beginning... cancel button problem

    Hi Folks

    Im using Access 2003

    I have got a procedure as detailed below that searches for a string, if it cannot find a result it displays an error message and returnc you to the beginning.

    The problem is I can't get the cancel button to work:- could someone take a look to see whats wrong.... thanks in advance.

    Donald

    /////////////////////////////////////////////////////////////////

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Dim MatterNo As String
    Dim SubNo As String

    Dim Msg As String
    Dim Icon As Integer
    Dim Title As String
    Dim Buttons As Integer

    Start:
    MatterNo = InputBox("Enter The Matter ID")

    If InStr(MatterNo, ".") <= 2 Then
    MsgBox "That is not a matter number, please try again!"
    GoTo Start
    ElseIf InStr(MatterNo, ".") = vbCancel Then
    MsgBox "Cancelled"
    Exit Sub
    End If
    Last edited by donaldt; 11-23-05 at 11:46.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yikes! It is considered horrible practice to use loops in this manner. Also, I have no idea what you're trying to do with that ElseIf statement. I'm assuming you want to see a string with a decimal point in the 3rd or greater position, and force the user to re-enter if they input an invalid string. If that's the case, you could go this route:
    Code:
    MatterNo = InputBox("Enter The Matter ID")
    
    If Nz(MatterNo, "") <> "" Then
       While InStr(MatterNo, ".") <= 2
            MsgBox "That is not a matter number, please try again!"
            MatterNo = InputBox("Enter The Matter ID")
            If Nz(MatterNo, "") = "" Then
                MsgBox "Cancelled"
                Exit Sub
            End If
       Wend
    Else
        MsgBox "Cancelled"
        Exit Sub
    End If

    For the record, InStr() returns 0 if the string is not found. If the user did not provide a value, InStr(MatterNo, ".") <= 2 would be true and your second statement will never execute.
    Last edited by Teddy; 11-23-05 at 11:57.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you are checking for a full stop in a string at character postion 2 or earlier and do seomthing
    failing that if it equals the intrinsic constant vbcancel which happens to be 2

    so by defintion your elseif will never be called as it is handled in the first if statement

    so the solution is to either put in a catchall else after the elseif or posibly read up on the instr function in the help system. Im guess ing that what you want is to delete the elseif clause and merely use an else

    what makes a valid matterno?

    it may make more sense to move the validation of the matterno to a function (especailly if it is going to be used elsewhere) and retrun true or false.

  4. #4
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Teddy !!

    That worked - many thanks for the fix, fantastic !

    Your a star

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •