By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Error 13

P: 8
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim NewSort As Integer
  3.  
  4.     NewSort = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
  5.  
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
Nov 20 '06 #1
Share this Question
Share on Google+
11 Replies


100+
P: 1,646
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim NewSort As Integer
  3.  
  4.     NewSort = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
  5.  
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
Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim NewSort As Integer
  3.     Dim stReturn As String
  4.  
  5.     stReturn = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
  6.     If Len(stReturn) AND IsNumeric(stReturn) Then
  7.         NewSort = CInt(stReturn)
  8.     Else
  9.         MsgBox "Kind user please enter an integral numeric value"
  10.     End If
  11.  
Nov 20 '06 #2

P: 8
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
Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim NewSort As Integer
  3.     Dim stReturn As String
  4.  
  5.     stReturn = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
  6.     If Len(stReturn) AND IsNumeric(stReturn) Then
  7.         NewSort = CInt(stReturn)
  8.     Else
  9.         MsgBox "Kind user please enter an integral numeric value"
  10.     End If
  11.  
Thank you, I will give this a try and let you know if I have any troubles with it.

Lena
Nov 20 '06 #3

P: 8
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.

Expand|Select|Wrap|Line Numbers
  1.     If Not Fix(NewSort) Then
  2.         MsgBox "Please enter a whole number (no decimals allowed)"
  3.         Exit Sub
  4.     End If
  5.  
How do I prevent anything but a whole number from being used?

Thanks!
Lena
Nov 20 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this .. if I understand you correctly?

Expand|Select|Wrap|Line Numbers
  1. If Int(NewSort) <> NewSort Then
  2. MsgBox "Please enter a whole number (no decimals allowed)"
  3. Exit Sub
  4. End If
  5.  
Nov 20 '06 #5

P: 8
Try this .. if I understand you correctly?

Expand|Select|Wrap|Line Numbers
  1. If Int(NewSort) <> NewSort Then
  2. MsgBox "Please enter a whole number (no decimals allowed)"
  3. Exit Sub
  4. End If
  5.  
Still allows me to enter numbers like 2.1 and etc. Here is all the code for this that I am using:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSortOrder_Click()
  2.  
  3.     Dim db As DAO.Database
  4.     Dim mySQL As String
  5.     Dim OldSort As String
  6.     Dim NewSort As Integer
  7.     Dim mySQLRecordCount As String
  8.     Dim rs As DAO.Recordset
  9.     Dim TheCount As Integer
  10.     Dim MaxNum As Integer
  11.     Dim stReturn As String
  12.  
  13.     Set db = CurrentDb
  14.     OldSort = Me!FunctionalQuestionOrder
  15.  
  16.     mySQLRecordCount = "SELECT * FROM tblQuestions" _
  17.         & " WHERE MarkAsDeleted = False" _
  18.         & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
  19.         & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
  20.  
  21.     Set rs = db.OpenRecordset(mySQLRecordCount)
  22.  
  23.     With rs
  24.         .MoveLast
  25.         .MoveFirst
  26.     End With
  27.  
  28.     TheCount = rs.RecordCount
  29.  
  30.     rs.Close
  31.     Set rs = Nothing
  32.  
  33.     If TheCount = 1 Then
  34.         MsgBox "There is only one question in this area and no need to change the sort order."
  35.         Exit Sub
  36.     End If
  37.  
  38.     stReturn = InputBox("Please enter a new Sort # between 1 and " & DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
  39.     If Len(stReturn) And IsNumeric(stReturn) Then
  40.         NewSort = CInt(stReturn)
  41.     Else
  42.         Exit Sub
  43.     End If
  44.  
  45.     If Not IsNumeric(NewSort) Then
  46.         MsgBox "Please enter a number"
  47.         Exit Sub
  48.     End If
  49.  
  50.     If NewSort = OldSort Then
  51.         MsgBox "Same Number Entered"
  52.         Exit Sub
  53.     End If
  54.  
  55. '    If Not Fix(NewSort) Then
  56. '        MsgBox "Please enter a whole number - no decimals allowed)"
  57. '        Exit Sub
  58. '    End If
  59.  
  60.     If Not IsNumeric(NewSort) Then
  61.         MsgBox "You didn't enter a number"
  62.         Exit Sub
  63.     End If
  64.  
  65.     If Int(NewSort) <> NewSort Then
  66.         MsgBox "Please enter a whole number (no decimals allowed)"
  67.         Exit Sub
  68.     End If
  69.  
  70.     If NewSort < 1 Then
  71.         MsgBox "Sort Order Can't be less than 1"
  72.         Exit Sub
  73.     End If
  74.  
  75.     MaxNum = CInt(DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
  76.  
  77.     If NewSort > MaxNum Then
  78.         MsgBox "Sort Order number can not be bigger than the number of questions being sorted.  " _
  79.           & vbCrLf & vbCrLf & "If you wish to change the sort order, please try again using a valid " _
  80.           & "number."
  81.         Exit Sub
  82.     End If
  83.  
  84.     If CInt(NewSort) > CInt(OldSort) Then
  85.  
  86.         mySQL = "UPDATE tblQuestions" _
  87.             & " SET FunctionalQuestionOrder = 0 " _
  88.             & " WHERE FunctionalQuestionOrder = " & CInt(OldSort) _
  89.             & " AND MarkAsDeleted = False" _
  90.             & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
  91.             & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
  92.  
  93.         db.Execute mySQL
  94.  
  95.         mySQL = "UPDATE tblQuestions" _
  96.             & " SET FunctionalQuestionOrder = ([FunctionalQuestionOrder] -1)" _
  97.             & " WHERE FunctionalQuestionOrder BETWEEN " & CInt(OldSort) + 1 & " AND " & CInt(NewSort) _
  98.             & " AND MarkAsDeleted = False" _
  99.             & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
  100.             & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
  101.  
  102.         db.Execute mySQL
  103.  
  104.         mySQL = "UPDATE tblQuestions" _
  105.             & " SET FunctionalQuestionOrder =  " & CInt(NewSort) _
  106.             & " WHERE FunctionalQuestionOrder = 0 " _
  107.             & " AND MarkAsDeleted = False" _
  108.             & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
  109.             & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
  110.  
  111.         db.Execute mySQL
  112.     ElseIf CInt(NewSort) < CInt(OldSort) Then
  113.  
  114.         mySQL = "UPDATE tblQuestions" _
  115.             & " SET FunctionalQuestionOrder = 0 " _
  116.             & " WHERE FunctionalQuestionOrder = " & CInt(OldSort) _
  117.             & " AND MarkAsDeleted = False" _
  118.             & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
  119.             & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
  120.  
  121.         db.Execute mySQL
  122.  
  123.         mySQL = "UPDATE tblQuestions" _
  124.             & " SET FunctionalQuestionOrder = ([FunctionalQuestionOrder] +1)" _
  125.             & " WHERE FunctionalQuestionOrder BETWEEN " & CInt(NewSort) & " AND " & CInt(OldSort) - 1 _
  126.             & " AND MarkAsDeleted = False" _
  127.             & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
  128.             & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
  129.  
  130.         db.Execute mySQL
  131.  
  132.         mySQL = "UPDATE tblQuestions" _
  133.             & " SET FunctionalQuestionOrder =  " & CInt(NewSort) _
  134.             & " WHERE FunctionalQuestionOrder = 0 " _
  135.             & " AND MarkAsDeleted = False" _
  136.             & " AND FunctionalAreaID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaID]) _
  137.             & " AND FunctionalAreaCategoryID " & IIf(IsNull([Forms]![frmEditQuestions]![FunctionalAreaCategoryID]), "Is Null", "=" & [Forms]![frmEditQuestions]![FunctionalAreaCategoryID])
  138.  
  139.         db.Execute mySQL
  140.     End If
  141.  
  142.     MyTrackChanges "Sort Order", Me.FunctionQuestionRecID, CInt(OldSort), CInt(NewSort), _
  143.       "Sort Order Changed from " & CInt(OldSort) & " to " & CInt(NewSort), False
  144.  
  145.     Me.txtSort = Me.FunctionalQuestionOrder
  146.  
  147. '    Me.Repaint
  148.     Dim varRec
  149.  
  150.     varRec = Me.FunctionQuestionRecID
  151.  
  152.     Me.Requery
  153.  
  154.     Set rs = Me.RecordsetClone
  155.     rs.FindFirst "[FunctionQuestionRecID] = " & varRec
  156.     Me.Bookmark = rs.Bookmark
  157.  
  158. End Sub
  159.  
Lena
Nov 20 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Try something like this ...

Expand|Select|Wrap|Line Numbers
  1.  Dim i As Integer 
  2.  
  3. stReturn = InputBox("Please enter a new Sort # between 1 and " & _
  4. DLookup("[MaxOfFunctionalQuestionOrder]", "qryMaxSortNumber"))
  5.  
  6. If Len(stReturn) Then
  7.  
  8.     For i=1 to Len(stReturn)
  9.      'Check that each character is numeric
  10.      If Not IsNumeric(Mid(stReturn, i, 1)) Then
  11.         MsgBox "Please enter a whole number (no decimals allowed)"
  12.         Exit Sub
  13.      Else
  14.         NewSort = CInt(stReturn)
  15.      End If
  16.     Next i
  17.  
  18. Else
  19.     MsgBox "Please enter a whole number (no decimals allowed)"
  20.     Exit Sub
  21. End If
  22.  
Nov 20 '06 #7

P: 8
Thanks that works GREAT!

Lena
Nov 21 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks that works GREAT!

Lena
You're welcome.
Nov 21 '06 #9

100+
P: 1,646
You're welcome.
You can also do this a little more simply with:
Expand|Select|Wrap|Line Numbers
  1. If InStr(stReturn, ".") Then
  2.    MsgBox "Fingers off the decimal point button thank you"
  3. End If
  4.  
or words to that effect :)
Nov 21 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
You can also do this a little more simply with:
Expand|Select|Wrap|Line Numbers
  1. If InStr(stReturn, ".") Then
  2. MsgBox "Fingers off the period button thank you"
  3. End If
  4.  
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
Nov 21 '06 #11

P: 8
You can also do this a little more simply with:
Expand|Select|Wrap|Line Numbers
  1. If InStr(stReturn, ".") Then
  2.    MsgBox "Fingers off the decimal point button thank you"
  3. End If
  4.  
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
Nov 21 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.