Error 13

**willakawill** · Nov 20 '06, 06:16 PM

Originally posted by 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:

Code:

    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

Code:

    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** · Nov 20 '06, 08:28 PM

Originally posted by willakawill

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

Code:

    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** · Nov 20 '06, 08:43 PM

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.

Code:

    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** · Nov 20 '06, 10:27 PM

Try this .. if I understand you correctly?

Code:

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

**LenaWood** · Nov 20 '06, 11:08 PM

Originally posted by mmccarthy

Try this .. if I understand you correctly?

Code:

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:

Code:

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** · Nov 20 '06, 11:25 PM

Try something like this ...

Code:

 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** · Nov 21 '06, 12:10 AM

Thanks that works GREAT!

Lena

**MMcCarthy** · Nov 21 '06, 12:13 AM

Originally posted by LenaWood

Thanks that works GREAT!

Lena

You're welcome.

**willakawill** · Nov 21 '06, 03:30 AM

Originally posted by mmccarthy

You're welcome.

You can also do this a little more simply with:

Code:

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

or words to that effect :)

**MMcCarthy** · Nov 21 '06, 03:33 AM

Originally posted by willakawill

You can also do this a little more simply with:

Code:

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** · Nov 21 '06, 02:21 PM

Originally posted by willakawill

You can also do this a little more simply with:

Code:

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

Error 13

Error 13

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment