Error 13

[LenaWood]

Good Morning,

In my database I allow the user to move questions to a place in a sort order they determine it should be. To find out what number they want to move the question to I am using an input box.

I have the following:

 
    Dim NewSort As Integer
 
    NewSort = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
 

The input box gives them the choices of OK and Cancel (which is what I want). The problem I have is if they click on Cancel I get a Mismatch error (13). I know I can trap this error, but didn't want to put a bandaid on the problem if there was a way to fix the problem.

Is trapping the error my best solution?

Thanks!
Lena

[willakawill]

Good Morning,

In my database I allow the user to move questions to a place in a sort order they determine it should be. To find out what number they want to move the question to I am using an input box.

I have the following:

 
    Dim NewSort As Integer
 
    NewSort = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
 

The input box gives them the choices of OK and Cancel (which is what I want). The problem I have is if they click on Cancel I get a Mismatch error (13). I know I can trap this error, but didn't want to put a bandaid on the problem if there was a way to fix the problem.

Is trapping the error my best solution?

Thanks!
Lena

Hi. It is not an error that you have to trap. The Inputbox returns a string. If the user presses Cancel it returns a zero length string "" which cannot be assigned to an integer. So do this

 
    Dim NewSort As Integer
    Dim stReturn As String
 
    stReturn = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
    If Len(stReturn) AND IsNumeric(stReturn) Then
        NewSort = CInt(stReturn)
    Else
        MsgBox "Kind user please enter an integral numeric value"
    End If
 

[LenaWood]

Hi. It is not an error that you have to trap. The Inputbox returns a string. If the user presses Cancel it returns a zero length string "" which cannot be assigned to an integer. So do this

 
    Dim NewSort As Integer
    Dim stReturn As String
 
    stReturn = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
    If Len(stReturn) AND IsNumeric(stReturn) Then
        NewSort = CInt(stReturn)
    Else
        MsgBox "Kind user please enter an integral numeric value"
    End If
 

Thank you, I will give this a try and let you know if I have any troubles with it.

Lena

[LenaWood]

OK..that worked. Although now the users can enter 1.1 as the sort number (want whole numbers only). I did have a line of code that seemed to have kept that from happening, but if I use it, it catches whole numbers and tells me that I can't use them.

    If Not Fix(NewSort) Then
        MsgBox "Please enter a whole number (no decimals allowed)"
        Exit Sub
    End If
 

How do I prevent anything but a whole number from being used?

Thanks!
Lena

[MMcCarthy]

Try this .. if I understand you correctly?

If Int(NewSort) <> NewSort Then
MsgBox "Please enter a whole number (no decimals allowed)"
Exit Sub
End If
 

[LenaWood]

Try this .. if I understand you correctly?

If Int(NewSort) <> NewSort Then
MsgBox "Please enter a whole number (no decimals allowed)"
Exit Sub
End If
 

Still allows me to enter numbers like 2.1 and etc. Here is all the code for this that I am using:

Private Sub cmdSortOrder_Click()
 
    Dim db As DAO.Database
    Dim mySQL As String
    Dim OldSort As String
    Dim NewSort As Integer
    Dim mySQLRecordCount As String
    Dim rs As DAO.Recordset
    Dim TheCount As Integer
    Dim MaxNum As Integer
    Dim stReturn As String
 
    Set db = CurrentDb
    OldSort = Me!FunctionalQuestionOrder
 
    mySQLRecordCount = "SELECT * FROM tblQuestions" _
        & " WHERE MarkAsDeleted = False" _
        & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
        & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
 
    Set rs = db.OpenRecordset(mySQLRecordCount)
 
    With rs
        .MoveLast
        .MoveFirst
    End With
 
    TheCount = rs.RecordCount
 
    rs.Close
    Set rs = Nothing
 
    If TheCount = 1 Then
        MsgBox "There is only one question in this area and no need to change the sort order."
        Exit Sub
    End If
 
    stReturn = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
    If Len(stReturn) And IsNumeric(stReturn) Then
        NewSort = CInt(stReturn)
    Else
        Exit Sub
    End If
 
    If Not IsNumeric(NewSort) Then
        MsgBox "Please enter a number"
        Exit Sub
    End If
 
    If NewSort = OldSort Then
        MsgBox "Same Number Entered"
        Exit Sub
    End If
 
'    If Not Fix(NewSort) Then
'        MsgBox "Please enter a whole number - no decimals allowed)"
'        Exit Sub
'    End If
 
    If Not IsNumeric(NewSort) Then
        MsgBox "You didn't enter a number"
        Exit Sub
    End If
 
    If Int(NewSort) <> NewSort Then
        MsgBox "Please enter a whole number (no decimals allowed)"
        Exit Sub
    End If
 
    If NewSort < 1 Then
        MsgBox "Sort Order Can't be less than 1"
        Exit Sub
    End If
 
    MaxNum = CInt(DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
 
    If NewSort > MaxNum Then
        MsgBox "Sort Order number can not be bigger than the number of questions being sorted.  " _
          & vbCrLf & vbCrLf & "If you wish to change the sort order, please try again using a valid " _
          & "number."
        Exit Sub
    End If
 
    If CInt(NewSort) > CInt(OldSort) Then
 
        mySQL = "UPDATE tblQuestions" _
            & " SET FunctionalQuestionOrder = 0 " _
            & " WHERE FunctionalQuestionOrder = " & CInt(OldSort) _
            & " AND MarkAsDeleted = False" _
            & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
            & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
 
        db.Execute mySQL
 
        mySQL = "UPDATE tblQuestions" _
            & " SET FunctionalQuestionOrder = ([FunctionalQuestionOrder] -1)" _
            & " WHERE FunctionalQuestionOrder BETWEEN " & CInt(OldSort) + 1 & " AND " & CInt(NewSort) _
            & " AND MarkAsDeleted = False" _
            & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
            & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
 
        db.Execute mySQL
 
        mySQL = "UPDATE tblQuestions" _
            & " SET FunctionalQuestionOrder =  " & CInt(NewSort) _
            & " WHERE FunctionalQuestionOrder = 0 " _
            & " AND MarkAsDeleted = False" _
            & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
            & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
 
        db.Execute mySQL
    ElseIf CInt(NewSort) < CInt(OldSort) Then
 
        mySQL = "UPDATE tblQuestions" _
            & " SET FunctionalQuestionOrder = 0 " _
            & " WHERE FunctionalQuestionOrder = " & CInt(OldSort) _
            & " AND MarkAsDeleted = False" _
            & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
            & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
 
        db.Execute mySQL
 
        mySQL = "UPDATE tblQuestions" _
            & " SET FunctionalQuestionOrder = ([FunctionalQuestionOrder] +1)" _
            & " WHERE FunctionalQuestionOrder BETWEEN " & CInt(NewSort) & " AND " & CInt(OldSort) - 1 _
            & " AND MarkAsDeleted = False" _
            & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
            & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
 
        db.Execute mySQL
 
        mySQL = "UPDATE tblQuestions" _
            & " SET FunctionalQuestionOrder =  " & CInt(NewSort) _
            & " WHERE FunctionalQuestionOrder = 0 " _
            & " AND MarkAsDeleted = False" _
            & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
            & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
 
        db.Execute mySQL
    End If
 
    MyTrackChanges "Sort Order", Me.FunctionQuestionRecID, CInt(OldSort), CInt(NewSort), _
      "Sort Order Changed from " & CInt(OldSort) & " to " & CInt(NewSort), False
 
    Me.txtSort = Me.FunctionalQuestionOrder
 
'    Me.Repaint
    Dim varRec
 
    varRec = Me.FunctionQuestionRecID
 
    Me.Requery
 
    Set rs = Me.RecordsetClone
    rs.FindFirst "[FunctionQuestionRecID] = " & varRec
    Me.Bookmark = rs.Bookmark
 
End Sub
 

Lena

[MMcCarthy]

Try something like this ...

 Dim i As Integer 
 
stReturn = InputBox("Please enter a new Sort # between 1 and " & _
DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
 
If Len(stReturn) Then
 
    For i=1 to Len(stReturn)
     'Check that each character is numeric
     If Not IsNumeric(Mid(stReturn, i, 1)) Then
        MsgBox "Please enter a whole number (no decimals allowed)"
        Exit Sub
     Else
        NewSort = CInt(stReturn)
     End If
    Next i
 
Else
    MsgBox "Please enter a whole number (no decimals allowed)"
    Exit Sub
End If
 

[LenaWood]

Thanks that works GREAT!

Lena

[MMcCarthy]

Thanks that works GREAT!

Lena

You're welcome.

[willakawill]

You're welcome.

You can also do this a little more simply with:

If InStr(stReturn, ".") Then
   MsgBox "Fingers off the decimal point button thank you"
End If
 

or words to that effect :)

[MMcCarthy]

You can also do this a little more simply with:

If InStr(stReturn, ".") Then
MsgBox "Fingers off the period button thank you"
End If
 

or words to that effect :)

I agree based on the original requirement. Just though I'd turn it into a catch all rather than just checking for a decimal point.

Just me being pedantic as usual. ;)

Mary

[LenaWood]

You can also do this a little more simply with:

If InStr(stReturn, ".") Then
   MsgBox "Fingers off the decimal point button thank you"
End If
 

or words to that effect :)

Thanks for showing me a simple way also. It is nice that with Access there are many ways to get the same thing done!

Lena