Run-time error '5': Invalid procedure call or argument

Run-time error '5': Invalid procedure call or argument

Post by DBuc » Thu, 18 Apr 2002 12:33:01

Almighty VB gurus, I beg of you.  Help, please....

I have written a small program to update an Access database (VB 6.0 -
Learning Edition).  I am accessing a single database with 7 tables in it (2
main tables, and five tables used to validate fields).  I load the
validation tables in combo boxes, then match against them once a value is
entered.  Basically, when the value is entered, I access the database table
with a WHERE condition in the select and check for EOF.  If EOF, I send a
yes/no message box asking if the user wants to enter the new value as valid.
The process works fine for all but one.

The symptoms:

If the value entered is on the table, it works fine.  If the value is not on
the table, I  get a run-time error '5' message.  When I click ok, I see the
message box asking if I want to add the new set name.  Once I respond to it,
the program ends (regardless of whether I answer yes or no).

What I've tried:

This code works fine as long as I am running under debug.  It is only
"broke" after I make it an .exe.
I tried coding On Error in the combo box lostfocus sub.  It did NOT trap the
I tried setting the recordset to nothing before the sql - no effect.
I tried movefirst on the recordset before the sql - no effect.

This identical routine works for my other 4 validation routines.  I have
also already accessed the table (during my load procedure) to load the
combobox list, so I know the table is available.

The lostfocus code:

Private Sub cboSpellType_LostFocus()
' set retries to 0
    retries = 0
    On Error GoTo fnderror

' if spell type, change to ""
    If cboSpellType.Text = "<Spell Type>" Then
        cboSpellType.Text = ""
    End If

' if "" get out
    If cboSpellType.Text = "" Then
        Exit Sub
    End If

'mix case the field
    cboSpellType.Text = Mcase(cboSpellType.Text)

'sql to select the matching table record
    Set rsSpells = dbsMagic.OpenRecordset( _
        "SELECT * FROM Spell_Types WHERE Type = '" _
        & cboSpellType.Text & "'", dbOpenDynaset)

' if eof, then record not found - do we want to add it?
    If rsSpells.EOF Then
        msgstr$ = cboSpellType.Text & " is not on the table.  Add it?"
        response = MsgBox(msgstr$, vbYesNo, "Add new spell type?")
        If response = vbYes Then
            With rsSpells
                !Type = cboSpellType.Text
            End With
            cboSpellType.AddItem cboSpellType.Text
        End If
    End If

' if not summon, bypass creature info
    If cboSpellType.Text <> "Summon" Then
        cboCreatureType.TabStop = False
        txtPower.TabStop = False
        txtDefense.TabStop = False
        cboCreatureType.Text = ""
        txtPower.Text = ""
        txtDefense.Text = ""
        If cboCreatureType.TabStop = False Then
            cboCreatureType.TabStop = True
            txtPower.TabStop = True
            txtDefense.TabStop = True
        End If
    End If

' get out - bypass error handle
    Exit Sub

'error handle
    MsgBox (Err.Description)
    retries = retries + 1

    If retries > 5 Then
        Resume Next
    End If
End Sub

Here is the associated definitions:

Public dbsMagic As Database
Public rsInventory As Recordset
Public rsCards As Recordset
Public rsColors As Recordset
Public rsSpells As Recordset
Public rsCreatures As Recordset
Public rsAbilities As Recordset
Public rsSets As Recordset

Here is the Mcase function:

Public Function Mcase(str$) As String
    Mcase = ""
    If Len(str$) = 0 Then Exit Function

    For i = 1 To Len(str$)
        If i = 1 Then
            Mcase = Mcase & UCase(Mid(str$, i, 1))
        If Mid(str$, i - 1, 1) = " " Then
            Mcase = Mcase & UCase(Mid(str$, i, 1))
            Mcase = Mcase & LCase(Mid(str$, i, 1))
        End If
        End If
    Next i
End Function


1)  How do I find out where the error is?
2)  How do I trap it?
3)  What, exactly, is the order of events when you tab out of a field?

Comments, ideas, suggestions, any response gladly accepted....