Opening many recordsets

[samer22]

Hello VbForums
Please moderrators keep my post here.
I'm using IntelliSense in text change event (3 textboxes)
When retreiving data from database, there are of course three recordsets which are open.
This is causing a problem.
I get object variable or with block variable not set
Is there a way to overcome this issue please?
I'm using Sqlite (VbRichClient as platform)
This is the IntelliSense Sub I use:

Public Sub IntelliSense(sTextBox As TextBox, sTable As String, sField As String, Optional sDBPass As String)
    
    Dim lLen As Long, StrSql As String
    If bExit = True Or sTextBox = "" Then Exit Sub
    lLen = Len(sTextBox)
cnn.OpenDB MyDBPath

    StrSql = "SELECT * FROM " & sTable & " WHERE " & sField & " LIKE '" & sTextBox & "%'"
    
    Set RS = Nothing
   Set RS = cnn.OpenRecordset(StrSql)
    If RS.EOF And RS.BOF Then Exit Sub
    sTextBox.Text = RS(sField)
    If sTextBox.SelText = "" Then
        sTextBox.SelStart = lLen
    Else
        sTextBox.SelStart = InStr(sTextBox.Text, sTextBox.SelText)
    End If
    sTextBox.SelLength = Len(sTextBox.Text)
End Sub

thank you

[DataMiser]

Where are you getting the error and what is it you are trying to do?

[samer22]

I'm using IntelliSense in Text1 , 2 and 3
IntelliSense Text1, "Tbl1", "FirstName"
IntelliSense Text2, "Tbl1", "LastName"
IntelliSense Text3, "Tbl1", "MiddleName"

When retieving data from database:
Text1.text = RS("FirstName").Value
The error occurs here:
object variable or with block variable not set
Text2.text = RS("LastName").Value
Text3.text = RS("MiddleName").Value

[DataMiser]

I'm using IntelliSense in Text1 , 2 and 3
IntelliSense Text1, "Tbl1", "FirstName"
IntelliSense Text2, "Tbl1", "LastName"
IntelliSense Text3, "Tbl1", "MiddleName"

When retieving data from database:
Text1.text = RS("FirstName").Value
The error occurs here:
object variable or with block variable not set
Text2.text = RS("LastName").Value
Text3.text = RS("MiddleName").Value

That code is not in what you have shown so I do not know in what context it applies.
Have you hovered over the line with the mouse to see which of those two objects is throwing the error? It could be the textbox or the RS.

Not really sure where you get multiple recordsets. There is only one shown.

[westconn1]

i guess you are calling this from a keydown or keypress event, but i think the way you are doing it, with the 3 text boxes, you could have mismatched name groups, like the firstname could be from a different record than the last name etc

[samer22]

Have you hovered over the line with the mouse to see which of those two objects is throwing the error? It could be the textbox or the RS.

The Rs is throwing the error.

I do not know in what context it applies.

When searching for a record, I have to open the recordset.(Rs.Open) for searching.
Then, while populating the textboxes, at the Text change event, there is another Rs.open for auto completion(or IntelliSense ).
And because the initial Rs is not closed,this is why it is throwing the error.
I hope you'll understand me

[samer22]

westconn1
I'm calling from text change event

[Phill.W]

Make the RecordSet variable, RS, local to the Sub.
That way, it doesn't matter how many events you trigger, how many times you call this code, you cannot get one invocation nullifying the [shared] variable that another one is trying to use. OK, it shouldn't happen but it's the only way I can think of to get the error you describe.

' If you're going to use Hungarian Notation, 
' differentiate between sTring variables and TextBox Controls. 

Public Sub IntelliSense( txtTextBox As TextBox, sTable As String, sField As String, Optional sDBPass As String)
    Dim RS as RecordSet : Set RS = Nothing     
    
    Dim lLen As Long, sSql As String
    If bExit = True Or txtTextBox.Text = "" Then Exit Sub ' bExit not defined here. 
 Are you using Option Explicit?  
    lLen = Len(txt.sTextBox.Text)
    
    cnn.OpenDB MyDBPath
    sSql = "SELECT * FROM " & sTable & " WHERE " & sField & " LIKE '" & txtTextBox.Text & "%'"
    
    Set RS = cnn.OpenRecordset(sSql)
    If RS.EOF And RS.BOF Then Exit Sub
    txtTextBox.Text = RS(sField)
    If sTextBox.SelText = "" Then
        txtTextBox.SelStart = lLen
    Else
        txtTextBox.SelStart = InStr(txtTextBox.Text, txtTextBox.SelText)
    End If
    txtTextBox.SelLength = Len(txtTextBox.Text)
    
    RS.Close
End Sub

You might also want to think about Parameterised Queries to avoid SQL Injection attacks, even in a VB6, desktop application!

Regards, Phill W.

[samer22]

I could overcome the issue by using IntelliSense in KeyUp event instead of change event