vb6 query only returning 1 result from ms access database

[cindy s]

hi guys, im having some trouble here and cant figure out what im doing wrong.

i have a vb6 program linked to a ms database and im running queries and returing the results in a listbox on the same form. here is what i have:

Private Sub cmdSearchauthor_Click()
Dim strSearchFor As String, foundFlag As Boolean
'Search for the Author specified by the user
strSearchFor = UCase(InputBox("Enter the Author to find:"))
If Len(strSearchFor) > 0 Then
  datBooks.Recordset.MoveFirst
  foundFlag = False
  Do While (Not foundFlag) And (Not datBooks.Recordset.EOF)
    If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
      foundFlag = True
      lstBooks.AddItem datBooks.Recordset.Fields("Title").Value

      Else
      datBooks.Recordset.MoveNext
    End If
  Loop
  If Not foundFlag Then
    MsgBox "Unable to locate requested Author.", , "Not Found"
    datBooks.Recordset.MoveLast  'move so that EOF is no longer true
  End If
Else
  MsgBox "Must enter an Author.", , ""
End If
End Sub

when i run this query i want it to return all of the book titles written by a certain author in the database, this query works but only returns one of the book titles by the author instead of all of them. i think there may be something wrong with my loop or something but i cant see it

can someone please tell me where im going wrong?

thanks in advance guys

Cindy

[Jx_Man]

because you set Flag to True.
After first looping program will check the flag state is true or false, but program get your flag is true then program will stop to read data..
pick your flag out of looping.

Do While (Not foundFlag) And (Not datBooks.Recordset.EOF)
    If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
     
      lstBooks.AddItem datBooks.Recordset.Fields("Title").Value

      Else
      datBooks.Recordset.MoveNext
    End If
  Loop
  foundFlag = True

[P.manidas]

Place the move next line(14 nos. line on your codes) once again for going to the next record after matching your query. See the modification codes

Do While (Not foundFlag) And (Not datBooks.Recordset.EOF)
    If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
      foundFlag = True
      lstBooks.AddItem datBooks.Recordset.Fields("Title").Value
      'Here also place move next for go to next record after matching your query
      datBooks.Recordset.MoveNext 'add new line
      Else
      datBooks.Recordset.MoveNext
    End If
  Loop

[cindy s]

thank you both for your replies, jx man here is the code after i implemented your suggestions:

Private Sub cmdSearchauthor_Click()
Dim strSearchFor As String, foundFlag As Boolean
'Search for the Author specified by the user
strSearchFor = UCase(InputBox("Enter the Author to find:"))
If Len(strSearchFor) > 0 Then
datBooks.Recordset.MoveFirst
foundFlag = False
Do While (Not foundFlag) And (Not datBooks.Recordset.EOF)
If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
lstBooks.AddItem datBooks.Recordset.Fields("Title").Value
Else
datBooks.Recordset.MoveNext
End If
Loop
foundFlag = True
If Not foundFlag Then
MsgBox "Unable to locate requested Author.", , "Not Found"
datBooks.Recordset.MoveLast 'move so that EOF is no longer true
End If
Else
MsgBox "Must enter an Author.", , ""
End If
End Sub

when i try this, i get no results at all and a scroll bar appears in the listbox as if theres a load of results in there but its just blank.

p.manidas here is my code after i implemented your suggestions:

Private Sub cmdSearchauthor_Click()
Dim strSearchFor As String, foundFlag As Boolean
'Search for the Author specified by the user
strSearchFor = UCase(InputBox("Enter the Author to find:"))
If Len(strSearchFor) > 0 Then
datBooks.Recordset.MoveFirst
foundFlag = False
Do While (Not foundFlag) And (Not datBooks.Recordset.EOF)
If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
foundFlag = True
lstBooks.AddItem datBooks.Recordset.Fields("Title").Value
'Here also place move next for go to next record after matching your query
datBooks.Recordset.MoveNext 'add new line
Else
datBooks.Recordset.MoveNext
End If
Loop
If Not foundFlag Then
MsgBox "Unable to locate requested Author.", , "Not Found"
datBooks.Recordset.MoveLast 'move so that EOF is no longer true
End If
Else
MsgBox "Must enter an Author.", , ""
End If
End Sub

when i try this, it still only lists 1 book title by the author instead of all the authors books.

any idea guys? i just cant see whats wrong

thanks for taking the time to reply

hope you can help

cindy

[P.manidas]

In addition to my previous reply. Try changing the 9 nos. line of your original post as below....

If Trim(UCase(datBooks.Recordset.Fields("Author").Value)) = Trim(UCase(strSearchFor)) Then

[cindy s]

just tried that and its still only returning 1 result :(

[Jx_Man]

you need to search it..using sql statment.

[cindy s]

Is there no other way?

thanks for all your help

[Jx_Man]

try This..

Private Sub cmdSearchauthor_Click()
Dim strSearchFor As String, foundFlag As Boolean
'Search for the Author specified by the user
strSearchFor = UCase(InputBox("Enter the Author to find:"))
If Len(strSearchFor) > 0 Then
datBooks.Recordset.Open "SELECT * from Authors", Conn, adOpenStatic, adLockOptimistic ' Authors is your table name
datBooks.Recordset.MoveFirst
Do While (Not datBooks.Recordset.EOF)
lstBooks.AddItem datBooks.Recordset!Title
datBooks.Recordset.MoveNext
Loop
If Not foundFlag Then
MsgBox "Unable to locate requested Author.", , "Not Found"
datBooks.Recordset.MoveLast 'move so that EOF is no longer true
End If
Else
MsgBox "Must enter an Author.", , ""
End If
End Sub

[Jx_Man]

ups..i forgot to add condition.
change this part :

rs.Open "SELECT * from Authors where Author = '" & strsearchfor & "'", Conn, adOpenStatic, adLockOptimistic ' Author is your table name

[cindy s]

thank you so much for your replies, when i try both of them i get

"run-time error 438, object doesnt support this propery or method"

im running out of ideas, i have every other part of the program except this

[Jx_Man]

you can post your project..
i'll try to fix it..

[Jx_Man]

you can post your project..
i'll try to fix it..

[cindy s]

do you mean post the whole project including all the forms etc?

[cindy s]

i have posted my posted my project here

thank you again

[Jx_Man]

Try this following code for author searhing :

Private Sub cmdSearchauthor_Click()
    Dim strSearchFor, strCriteria As String, foundFlag As Boolean
    'Search for the Author specified by the user
    strSearchFor = UCase(InputBox("Enter the Author to find:"))
    If Len(strSearchFor) > 0 Then
        datBooks.Recordset.MoveFirst
        If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
            foundFlag = False
            Do While (Not foundFlag) And (Not datBooks.Recordset.EOF)
                If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
                    lstBooks.AddItem datBooks.Recordset("Title").Value
                    datBooks.Recordset.MoveNext
                Else
                    datBooks.Recordset.MoveNext
                End If
            Loop
            foundFlag = True
        Else
            If Not foundFlag Then
                MsgBox "Unable to locate requested Author.", , "Not Found"
                datBooks.Recordset.MoveLast 'move so that EOF is no longer true
            End If
        End If
    Else
        MsgBox "Must enter an Author.", , ""
    End If
End Sub

[cindy s]

ok i just tried your code. it's strange because i tried many of the authors and only 1 of the authors books are returned, this particular search returns 2 of the authors books which is correct because he only has 2 in the database.

when i try any of the other authors it says "unable to locate requested author" even though im entering the right author name.

so only 1 of the authors book listings are returned but its a step in the right direction

thank you so much for your help, really do appreciate it

[Jx_Man]

Try this :

Private Sub cmdSearchauthor_Click()
    Dim strSearchFor, strCriteria As String, foundFlag As Boolean
    'Search for the Author specified by the user
    strSearchFor = UCase(InputBox("Enter the Author to find:"))
    If Len(strSearchFor) > 0 Then
        datBooks.Recordset.MoveFirst
        lstBooks.Clear
            Do While (Not datBooks.Recordset.EOF)
                If UCase(datBooks.Recordset.Fields("Author").Value) = strSearchFor Then
                    lstBooks.AddItem datBooks.Recordset("Title").Value
                    datBooks.Recordset.MoveNext
                Else
                    datBooks.Recordset.MoveNext
                End If
            Loop
            
            If lstBooks.ListCount < 1 Then
                MsgBox "Unable to locate requested Author.", , "Not Found"
                datBooks.Recordset.MoveLast
            End If
        
    Else
        MsgBox "Must enter an Author.", , ""
    End If
End Sub

[P.manidas]

Cyndy S,

I have tested the above(JX Man) codes with my own database using little change and its return right answer. However, i have noticed in the line no. 10, "Fields" is missing. I am not sure, it may be worked or may not. But the logic is right. if that code line will not work just change the line as below. it will work 100%.

stBooks.AddItem datBooks.Recordset.Fields("Title").Value

And one thing i want to tell you whenever you go for comparing string always use Trim as well as UCase function on the both side. If you don't use Trim function and if the user will enter the data giving one or more space before or after the data, it will not return right answer even if user entered the right data.

[dspnhn]

i have a question here ... why do you rely on user input..why not limit user to selection from a list or combo... this way your code can be efficient and bang on target.

lemme explain:

1) get all your authors in a combo using DISTINCT clause so even if he/she has written >1 books only one entry be done
2) Take user input from selection and search the database...and add to list the books written by the author.

simple...