-
August 24th, 2013, 06:38 PM
#1
[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
-
August 24th, 2013, 06:52 PM
#2
Re: ListBox
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 *
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
August 24th, 2013, 07:03 PM
#3
Re: ListBox
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")
-
August 24th, 2013, 07:15 PM
#4
Re: ListBox
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")
-
August 24th, 2013, 07:17 PM
#5
Re: ListBox
Same error number... and in which line stop the execution?
Did you change the wildcard characters from % to * ?
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
August 24th, 2013, 07:23 PM
#6
Re: ListBox
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.
-
August 24th, 2013, 09:58 PM
#7
Re: ListBox
Post the code where you add items to List1
...and insert this line of code before rs.Open...
execute
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
August 25th, 2013, 06:46 AM
#8
Re: ListBox
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
Last edited by Probie887; August 25th, 2013 at 07:05 AM.
Reason: typo
-
August 25th, 2013, 08:32 AM
#9
Re: ListBox
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
Last edited by Probie887; August 25th, 2013 at 01:06 PM.
Reason: added image
-
August 25th, 2013, 02:39 PM
#10
Re: ListBox
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
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
August 25th, 2013, 03:34 PM
#11
Re: ListBox
I get a syntax error on this line:
Code:
rs.Open strSQL Database, adOpenStatic, adLockPessimistic
-
August 25th, 2013, 04:05 PM
#12
Re: ListBox
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
-
August 25th, 2013, 04:11 PM
#13
Re: ListBox
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
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
August 25th, 2013, 05:00 PM
#14
Re: ListBox
The word viper is just a word entered to search for in the database.
-
August 25th, 2013, 06:25 PM
#15
Re: ListBox
Last edited by Probie887; August 25th, 2013 at 06:27 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|