[RESOLVED] ListBox

Hi, I am new to coding so I am having a problem. I have a list box with bible verses loaded from a database. When I click on one of the verses to load it to a text window I get and error saying:

Run-time error '-2147217900 (80040e14)':

Syntax error (missing operstor) in query expression '[TextData] '%And now art thou cursed from the earth, which hath opened her mouth to recieve thy brothers blood from thy hand'.

Here is the code for that:

Code:

---

Private Sub List1_Click()
Dim Database As New ADODB.Connection
Dim txtName As String

        txtName = List1
        MousePointer = vbHourglass
                   
        With Database

            Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
   
      End With
    'On Error Resume Next
      Set rs = New ADODB.Recordset 'set the recordset
         
                rs.Open "SELECT * from BibleTable WHERE [TextData] '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select location to search and cursor
                   
        Text2.Text = rs("BookTitle")
        Text3.Text = rs("Chapter")
        Text4.Text = rs("Verse")
        Text5.Text = rs("TextData")
       
        Me.MousePointer = vbNormal
        MousePointer = 0

               
                '  rs.Close
                  '  Set rs = Nothing  'set them to nothing
                  '  Set cn = Nothing
       
    End Sub

---

The select query need the LIKE or = operator, as:

Code:

---

rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly

---

...and you need to change the wildcard character to *

Thanks for your reply.
Sorry...still get the same error, driving me crazy. Here is the code:

Code:

---

  End With
    'On Error Resume Next
      Set rs = New ADODB.Recordset 'set the recordset
         
               
                  rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly
                   
                    'On Error Resume Next
                   
        Text2.Text = rs.Fields("BookTitle")
        Text3.Text = rs.Fields("Chapter")
        Text4.Text = rs.Fields("Verse")
        Text5.Text = rs.Fields("TextData")

---

Thanks for your reply.
Sorry...still get the same error, driving me crazy. Here is the code:

Code:

---

End With
    'On Error Resume Next
      Set rs = New ADODB.Recordset 'set the recordset
         
               
                  rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '*" & txtName & "*'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly
                   
                    'On Error Resume Next
                   
        Text2.Text = rs.Fields("BookTitle")

---

Same error number... and in which line stop the execution?
Did you change the wildcard characters from % to * ?

Yes I changed the wildcards and yes it is the same error. Once the list box is full, I click on one of the lines in the listbox to have it open in a textbox on the same page and I get the error. I also tried using the "=" in the code...doesn't help.

Post the code where you add items to List1

...and insert this line of code before rs.Open...

Code:

---

MsgBox txtName

---

execute

I hope this helps...I highlighted where the error occurs in red, and the load list box event is in blue. As you can see I changed the wildcard * but still nothing I tried "=" in the place of "LIKE" but still I get the error. I even tried messing with adOpenStatic, adLockPessimistic, adLockReadOnly stuff but nothing works. I tried to eliminated 3 of the text boxes I wanted filled but I still get the error.

Thanks again for your assistance in this matter.

Code:

---

Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
 'couple o global vars to track the form minimum size
Dim MinHeight As Long
Dim MinWidth As Long

Private Sub cmdClose_Click()

If MsgBox("Are you sure you want to exit the Search Database ?", vbYesNo + vbQuestion, "Exit DataBase?") = vbNo Then      'check if you really want to exit

Exit Sub 'exit the command
         
      Else
     
      If rs.State = adStateClosed Then  'if rs.close then resume next
                    On Error Resume Next
       
                    rs.Close
               
                    Set rs = Nothing  'set them to nothing
                    Set cn = Nothing
                 
 
        End If
        End If
       
        Unload Me
End Sub




Private Sub cmdSearch_Click() 'THIS LOAD LIST1
 
  Dim sSearchText As String
  Dim Clientname As String
  Dim Database As New ADODB.Connection

          txtSearch.SetFocus
          List1.Clear
          sSearchText = txtSearch
          MousePointer = vbHourglass
         
         
                         
    With Database

            Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
   
          End With
   
            Set rs = New ADODB.Recordset 'set the recordset
                    rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '%" & sSearchText & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor
                   
            If rs.EOF Then
                  MsgBox "No records found matching " & txtSearch.Text, vbInformation, "DataBase"
                  MousePointer = 0

            Else
                      rs.MoveFirst

                  End If
                      List1.Clear

          Do Until rs.EOF
                      List1.AddItem (rs.Fields("TextData"))
                      rs.MoveNext
                Loop

                If Not (rs.BOF = True) Then
                        rs.MoveFirst
                        MsgBox List1.ListCount - 1 + 1 & " Records found matching " & txtSearch.Text, vbInformation, "DataBase"
               
                If rs.BOF = True Then
                           
                Else
                      display
                      Me.MousePointer = vbNormal
                End If
                      MousePointer = 0
                End If

End Sub





Public Sub display()

                'display text
                      Text2.Text = rs("BookTitle")
                      Text3.Text = rs("Chapter")
                      Text4.Text = rs("Verse")
                      Text5.Text = rs("TextData")

End Sub





Private Sub cmdFirst_Click()

                If Not rs.BOF Then
                        rs.MoveFirst
                        display

                End If

End Sub




 Private Sub cmdLast_Click()

          If Not rs.EOF Then
                        rs.MoveLast
                        display
          Else
                        MsgBox "last"
          End If
End Sub




Private Sub cmdNext_Click()

                      rs.MoveNext
       
        If Not rs.EOF Then
                      'ClearFields
                      display
           
        Else
           
                    rs.MoveLast
                    If Not rs.BOF Then
                    'ClearFields
                    display
                    MsgBox "You are at the last record..", vbInformation, "Database"
                   
             
        End If
        End If
       
End Sub




Private Sub cmdPrevious_Click()

rs.MovePrevious
       
                    If Not rs.BOF Then
                    'ClearFields
                    display
           
        Else
           
                  '  rs.MovePrevious
                    If rs.BOF Then
                    'ClearFields
                    'display
                    MsgBox "You are at the First Record in your Search..", vbInformation, "Database"
                   
             
        End If
        End If
       


End Sub





Private Sub Form_Load()

'center the form
                Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2

                Text2.Text = vbNullString
                Text3.Text = vbNullString
                Text4.Text = vbNullString
                Text5.Text = vbNullString
                txtSearch.Text = "Please Enter Search Criteria"

End Sub





Private Sub List1_Click()  'THIS IS THE LIST1 CLICK EVENT
Dim Database As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim txtName As String

                txtName = List1
                MousePointer = vbHourglass
                   
        With Database

                Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
   
      End With
    'On Error Resume Next
        Set rs = New ADODB.Recordset 'set the recordset

                'THIS IS WHERE THE ERROR OCCURS
                rs.Open "SELECT * from BibleTable WHERE [TextData] = '*" & txtName & "*'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor
 
                    On Error Resume Next
                     
                  Text2.Text = rs.Fields("BookTitle")
                  Text3.Text = rs.Fields("Chapter")
                  Text4.Text = rs.Fields("Verse")
                  Text5.Text = rs.Fields("TextData")

                  Me.MousePointer = vbNormal
                  MousePointer = 0

            rs.Close
            Set rs = Nothing  'set them to nothing
            Set cn = Nothing
       
    End Sub





Private Sub txtSearch_Click()

                txtSearch.Text = vbnullstring
End Sub

---

Okay....making progress. In my code I got rid of the error by adding the stuff in BOLD, the one in DARK BLUE loads okay, the three in RED do not load. Now what do I do....any ideas???? I attached a .jpg file of the screen shot so you can see the boxes.

Code:

---

Private Sub List1_Click()
  Dim Database As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim sSearchText As String
       
        txtTextData.Text = List1
        MousePointer = vbHourglass
                   
        With Database

            Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
   
      End With
   
      Set rs = New ADODB.Recordset 'set the recordset
              rs.Open "SELECT * from BibleTable WHERE [TextData] = '%" & sSearchText & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor

               
If Not rs.BOF And rs.EOF Then
                   
                      txtBookTitle.Text = rs("BookTitle")
                      txtChapter.Text = rs("Chapter")
                      txtVerse.Text = rs("Verse")
                      txtTextData.Text = rs("TextData")

  Else
  'MsgBox "not found"
  End If
                     
               

        Me.MousePointer = vbNormal
        MousePointer = 0
       

               
              rs.Close
            '  Set rs = Nothing  'set them to nothing
            '  Set cn = Nothing
       
    End Sub

---

Attachment 31703

Try next code:
(replace yours)

Code:

---

Private Sub List1_Click()  'THIS IS THE LIST1 CLICK EVENT

  Dim Database As New ADODB.Connection
  Dim rs      As New ADODB.Recordset
  Dim txtName  As String
  Dim strSQL  As String

    txtName = List1.Text
    MousePointer = vbHourglass
                   
    Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database

    strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
   
    MsgBox strSQL

    Set rs = New ADODB.Recordset 'set the recordset
        rs.Open strSQL Database, adOpenStatic, adLockPessimistic
        If Not rs.BOF And rs.EOF Then
          Text2.Text = rs.Fields("BookTitle")
          Text3.Text = rs.Fields("Chapter")
          Text4.Text = rs.Fields("Verse")
          Text5.Text = rs.Fields("TextData")
        End If
        rs.Close
        Set rs = Nothing
        Database.Close
        Set Database = Nothing

    Me.MousePointer = vbDefault
     
End Sub

---

I get a syntax error on this line:

Code:

---

        rs.Open strSQL Database, adOpenStatic, adLockPessimistic

---

I put a " , " after strSQL and the syntax error went away. I ran the program and end up with other stuff as in image below. After I clicked on the OKAY I received the Run-time error '-2147217900 (80040e14)' (same as before) It stops at the line rs.Open strSQL, Database, adOpenStatic, adLockPessimistic

Attachment 31705

The syntax error is because of the ' characters in the text (single quotes)

What's the name of the textbox control where says "viper" in your screenshot
Maybe that's the word that you have to use in the search and not the whole paragraph

The word viper is just a word entered to search for in the database.

I tried to upload a zip file of my program but the site says its too big, I imagine that is because of the bible.db I still do not know how to fix this thing. The sql was a good idea but still brings up the error. The error occurs at the text in RED. Images show the errors in order I get the first message box (don't know why) click OKAY and the second error box appears. The images with the line of text highlightd should be listeste in the box above with the book, verse, chapter.

Code:

---

Private Sub List1_Click()

  Dim Database As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim txtName  As String
  Dim strSQL  As String

    txtName = List1.Text
    MousePointer = vbHourglass
                   
    Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database

    strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
   
    MsgBox strSQL

    Set rs = New ADODB.Recordset 'set the recordset
      rs.Open strSQL, Database, adOpenStatic, adLockPessimistic
       
                If Not rs.BOF And rs.EOF Then
          text2.Text = rs.Fields("BookTitle")
          text3.Text = rs.Fields("Chapter")
          text4.Text = rs.Fields("Verse")
          text5.Text = rs.Fields("TextData")
        End If
        rs.Close
        Set rs = Nothing
        Database.Close
        Set Database = Nothing

    Me.MousePointer = vbDefault
     
End Sub

---

Attachment 31729[ATTACH] 31727[/ATTACH]

I posted the explanation in post #13

The first mesage box is because of an instruction I wrote in the code, just to display the content of the sql query,remove it anytime you want...
The instruction to remove is : MsgBox strSQL

The second message is the error
The reason for this error are the single quotes characters ['] that are included in the paragraph that you click in the list box... example ("and weave the spider's web...") the error is caused by the ' character in spider's

Let me search for an alternative to find a record using single quotes in the text

Hello jggtz, this is frustrating I know...so here we go again. First I made a copy of my program so as not to mess with your fixes. In my copy I put code back the way it was if you see in the image I have the box loading with the verse when I click on it and there is no error appearing. Now the reason that is happening is because of this line of code text5.Text = List1 but as you can also see in the image the boxes at the top are NOT filling with the records from the database (the stuff in bold in the code below should be going into those top three boxes).

Code:

---

Dim Database As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim txtName As String
       
        txtName = List1
        MousePointer = vbHourglass
                   
        With Database

            Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
   
      End With
    On Error Resume Next
      Set rs = New ADODB.Recordset 'set the recordset
              rs.Open "SELECT * from BibleTable WHERE [TextData] = '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor
             
                    text5.Text = List1
                      text2.Text = rs.Fields("BookTitle")
                      text3.Text = rs.Fields("Chapter")
                      text4.Text = rs.Fields("Verse")

        Me.MousePointer = vbNormal
        MousePointer = 0
       

 
        rs.Close
        Set rs = Nothing
        Database.Close
        Set Database = Nothing

    Me.MousePointer = vbDefault
     
End Sub

---

Attachment 31733

We are going back... you have several errors in this procedure already corrected...
May we go ahead with the last procedure again, please

You have to duplicate the apostrophes within SQL query.

Quote:

---

Originally Posted by VictorN

You have to duplicate the apostrophes within SQL query.

---

Thanks... I will try to explain how to use the Replace function over txtName

Code:

---

Replace(txtName, "'","''")

---

Quote:

---

May we go ahead with the last procedure again, please

---

Yes we are going ahead...sorry.

I put the REPLACE thing in the code and ran it. I received the Run-time error '-2147217900 (80040e14)': as in post one. It breaks on the sql entry marked in bold red.

Code:

---

Private Sub List1_Click()

  Dim Database As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim txtName  As String
  Dim strSQL  As String

    txtName = List1.Text
    MousePointer = vbHourglass
           
    Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database

    strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
    txtName = Replace(txtName, "'", "''")
   
    'MsgBox strSQL

    Set rs = New ADODB.Recordset 'set the recordset
      rs.Open strSQL, Database, adOpenStatic, adLockPessimistic

                             
If Not rs.BOF And rs.EOF Then

          text2.Text = rs.Fields("BookTitle")
          text3.Text = rs.Fields("Chapter")
          text4.Text = rs.Fields("Verse")
          text5.Text = rs.Fields("TextData")
         
          Else
  'MsgBox "not found"
  End If
 
        rs.Close
        Set rs = Nothing
        Database.Close
        Set Database = Nothing

    Me.MousePointer = vbDefault
     
End Sub

---

And how does txtName look like after Replace(txtName, "'", "''") has been executed?
Just set a brakpoit, start a debugger and you will be able to see all your variables' values in debugger window(s)

I did it twice first with WATCH

Code:

---

Watch :  :    Replace(txtName, "'", "''") : "They hatch cockatrice'''' eggs, and weave the spider''''s web: he that eateth of their eggs dieth, and that which is crushed breaketh out into a viper."

---

Then with CHANGE

Code:

---

Change:  : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click

---

Yes I still get the same errors as well.

Quote:

---

Originally Posted by Probie887

Code:

---

Private Sub List1_Click()
    ...
    strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
    txtName = Replace(txtName, "'", "''")

---

---

But why do you change the txtName after you have already inserted it in the strSQL query? Why not before?

When I place it above like the code below nothing moves from the list box to the textbox when it is click and I do not get the error message. But it skips past the following code.

Code:

---

If Not rs.BOF And rs.EOF Then

          Text2.Text = rs.Fields("BookTitle")
          Text3.Text = rs.Fields("Chapter")
          Text4.Text = rs.Fields("Verse")
          Text5.Text = rs.Fields("TextData")
         
          Else

---

Here you can see I moved the line txtName = Replace(txtName, "'", "''")

Code:

---

  Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
 
      txtName = Replace(txtName, "'", "''")
    strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
 
   
    MsgBox strSQL

---

This is the Watch with it change to above the strSQL line of code.

Code:

---

Break :  : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click

Change:  : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click

Watch :  : txtName : "They hatch cockatrice'' eggs, and weave the spider''s web: he that eateth of their eggs dieth, and that which is crushed breaketh out into a viper." : String : Search.List1_Click

Watch :  : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click

---

Quote:

---

Originally Posted by Probie887

This is the Watch with it change to above the strSQL line of code.

Code:

---

Break :  : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click

Change:  : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click

Watch :  : txtName : "They hatch cockatrice'' eggs, and weave the spider''s web: he that eateth of their eggs dieth, and that which is crushed breaketh out into a viper." : String : Search.List1_Click

Watch :  : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click

---

---

What the sh...t did you post? :confused:
What text does the strSQL contain after this line

Code:

---

    strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"

---

has been executed?
Or IOW, what does MsgBox show you?

I do not know what you are asking.

You said:

Quote:

---

But why do you change the txtName after you have already inserted it in the strSQL query? Why not before?

---

So I moved the **** thing.

the msg strSQL contains the same message as post12

Here is the code for the click event.

Code:

---

Private Sub List1_Click()

  Dim Database As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim txtName  As String
  Dim strSQL  As String

    txtName = List1.Text
    MousePointer = vbHourglass
           
    Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
 
      txtName = Replace(txtName, "'", "''")
    strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
 
   
    MsgBox strSQL

    Set rs = New ADODB.Recordset 'set the recordset
        rs.Open strSQL, Database, adOpenStatic, adLockPessimistic

             
If Not rs.BOF And rs.EOF Then

        Text2.Text = rs.Fields("BookTitle")
        Text3.Text = rs.Fields("Chapter")
        Text4.Text = rs.Fields("Verse")
        Text5.Text = rs.Fields("TextData")
         
        Else
  MsgBox "not found"
  End If
 
        rs.Close
        Set rs = Nothing
        Database.Close
        Set Database = Nothing

    Me.MousePointer = vbDefault
     
End Sub

---

Quote:

---

Originally Posted by Probie887

I do not know what you are asking.

You said:


So I moved the **** thing.

the msg strSQL contains the same message as post12

Here is the code for the click event.

---

It cannot be possible if you poated the real code that you had debugged.
Please, debug it again and show us:

1. txtName after you got it from the listbox
2. txtName after you called the Replace
3. strSQL

I don't understand the debug program. It makes nor sense to me what I am suppose to do.

Code:

---

Watch :  : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click
Watch :  : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click
Watch :  : MsgBox : <Expression not defined in context> : Empty : Search.List1_Click

---

below is an image of the strSQL message box

Attachment 31735

-Remove the MsgBox statement
-Change the * wildcards to %
-Change the line where you ask for rs.BOF and rs.EOF... both must be false
-Try

Quote:

---

Originally Posted by Probie887

I don't understand the debug program.

---

You must!
Otherwise you can forget about programming and just look for some other job.

Good morning jggtz....I got it now the rest of it is okay...I will mark this resolved and thank you so much. Thanks

Code:

---

strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '" & txtName & "%'"
   
   
    MousePointer = vbHourglass
           
    Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;"
   
    'MsgBox strSQL

    rs.Open strSQL, Database, adOpenStatic, adLockPessimistic
    If rs.BOF = False And rs.EOF = False Then
        text2.Text = rs.Fields("BookTitle")
        text3.Text = rs.Fields("Chapter")
        text4.Text = rs.Fields("Verse")
        text5.Text = rs.Fields("TextData")
    Else
        MsgBox "not found"
    End If

---