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
error.
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
.AddNew
!Type = cboSpellType.Text
.Update
End With
cboSpellType.AddItem cboSpellType.Text
Else
cboSpellType.SetFocus
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 = ""
Else
If cboCreatureType.TabStop = False Then
cboCreatureType.TabStop = True
txtPower.TabStop = True
txtDefense.TabStop = True
cboCreatureType.SetFocus
End If
End If
' get out - bypass error handle
Exit Sub
'error handle
fnderror:
MsgBox (Err.Description)
retries = retries + 1
If retries > 5 Then
End
Else
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))
Else
If Mid(str$, i - 1, 1) = " " Then
Mcase = Mcase & UCase(Mid(str$, i, 1))
Else
Mcase = Mcase & LCase(Mid(str$, i, 1))
End If
End If
Next i
End Function
Questions:
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....
Thanks,
--
Dave