Help with trapping an error. (VB6) & Access2000

[webstaff]

Hi Guys,

I have a textbox(txtkcnamesearch2) that has an auto search sub attached to it, This auto search uses a datacontrol(Data2) to find the records i want to compare to bring up the closest match letter by letter as the user types in ther the textbox, the results are sent to a list box when you hit a command button(cmdFind2).
This all works fine, in fact it works realy well, Im a newbie to VB and was amazed I got it running

The thing thats puzzling me is if you type in a sequence of letters that dont match any of the names in the field being searched it crashes the program.

I have tried to add "On Error Resume Next" but it doesnt help???

Can anyone advice me on a work around for this issue?

Many thanks
Les

'start of autocomplete peds search
Private Sub txtkcnamesearch2_KeyUp(KeyCode As Integer, shift As Integer)
On Error Resume Next
Dim LengthText As Integer
Dim NewText As Integer
Dim Answer As String
Dim LeftText As String
'Get the length of the typed text.
LengthText = Len(Replace$(txtkcnamesearch2, "'", "''&quot)
'Handle Backspace and Delete keys.
If KeyCode = vbKeyBack Or KeyCode = vbKeyDelete Or KeyCode = vbKeyReturn Then
Exit Sub
Else
On Error Resume Next
'Set variable to the typed text.
LeftText = Replace(txtkcnamesearch2, "'", "''&quot
'Query database for items in desired
'field that begin with your typed charac
' ters.
Data2.RecordSource = "Select [field_kcname] From " & _
"[sbt] " & _
"Where [field_kcname] Like '" & LeftText & "*'"
Data2.Refresh
'If no match, leave blank.
If LeftText = "" Then
Answer = ""

Else
'If match, set variable to result.
Answer = Data2.Recordset.Fields("field_kcname&quot

End If
End If
'Get length of result.
NewText = Len(Answer)
'If no match, fill Text box with
'typed characters only.
If NewText = 0 Then
txtkcnamesearch2 = LeftText
Else
'If query returns result,
'fill Text box with result.
txtkcnamesearch2 = Answer
'Set cursor to end of typed characters
txtkcnamesearch2.SelStart = LengthText
'Select and highlight portion of
'result text that wasn't typed.

txtkcnamesearch2.SelLength = Len(Answer) - LengthText

End If
'Close connection so you can
'connect to another text box.

Data2.RecordSource = "Select * From [qry_sbt_FULL] "
Data2.Refresh

End Sub
' end of auto complete peds search
Private Sub cmdFind2_Click()
Dim DB As Database
Dim rs As Recordset
staffylist2.Clear
Set DB = OpenDatabase("C:\Documents and Settings\webstaff\Desktop\sbtProgramme\sbt.mdb&quot

Set rs = DB.OpenRecordset("select * FROM sbt " & _
" Where field_kcname " & _
" LIKE '*" & Replace$(txtkcnamesearch2.Text, "'", "''&quot & "*'&quot

Do Until rs.EOF
staffylist2.AddItem rs.Fields("field_title&quot & ", " & _
rs.Fields("field_kcname&quot & _
" [" & rs.Fields("field_sex&quot & "]"
staffylist2.ItemData(staffylist2.NewIndex) = rs.Fields("field_id&quot

rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

 

[Dunx]

Without knowing the error and line which the error occurs i'm guessing but...

Presumably the error you get refers to BOF/EOF. Try trapping the error after the line Data2.Refresh. (You should not need On error resume next). You could use Data2.RecordCount=0 also

ie.

Data2.Refresh
If Data2.EOF then
'la-de-da
Else
'do ther stuff
end if

 

[CajunCenturion]

webstaff - Considering that you've asked over twenty questions, and have yet to indicate any answer was helpful, nor have you tried to help anyone else in the forum, its not very likely that people are lining up to give up their time to help you. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[webstaff]

Thanx Dunx,
With your suggestion i got there like this,
Thanks again.


Else
'Set variable to the typed text.
LeftText = txtkcnamesearch2
'Query database for items in desired
'field that begin with your typed charac
' ters.
Data2.RecordSource = "Select [field_kcname] From " & _
"[sbt] " & _
"Where [field_kcname] Like '" & LeftText & "*'"
Data2.Refresh
If Data2.Recordset.EOF Then
MsgBox "No Records Matching"
txtkcnamesearch2.Refresh
Exit Sub
Else
'If no match, leave blank.
If LeftText = "" Then
Answer = ""

 

[webstaff]

Hey CC,

Im sorry m8ty,

I just realised that ive been hitting the preview post when relpying, and for some reason I think I must have done that nearly every time,

So I do appologise Guys, I will make a point of checking.

Im not sure if you have read my posts m8, but im a pure novice at VB and as much as id like to help or advise folks im just not up to it yet?

I went out and bought a few books and am just getting my head around the syntax etc but still struggle looking for a soloution when its usualy a simple answer.

Rest assured i will help anyone if its ever possible.

I do appologise if I have appeared to be rude or disrepectfull to any of you guys.
Regards as ever
Les

 

[CajunCenturion]

That's great webstaff, and look forward to your active participation in the various threads inside this forum. There is no doubt a wide variety in skill levels and experience within this forum, but we all try to do what we can. We give a little help, we get a little help, and we appropriate mark those posts which we have found particularly helpful, or expert posts. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein