Search!!!

[Beacon]

Ok again sorry for putting it here!!

Now this piece of code searches a database and returns a result in msgboxes!
Problem is it stops as soon as it has found 1 result. If there are 2 or more results it won't display them!

Can someone add to this and tell me how i can get it to continue searching for other results then display them all at the end?

cheers

[crptcblade]

Add to what? Where's your code?

[Lethal]

Just taking a stab, since there is no code to go along with it, you may be using the find function, so just use a loop eof = true. After it finds whatever your searching for, dump it in an array or whatever you want, and start the search from where it left off. You may have to use a bookmark.

[Beacon]

Dont mind me off in my own little dream world!!

Here we go!! Sorry

Private Sub Command2_Click()
Dim res1 As String
Dim res2 As String
Dim res3 As String

Set rs = db.OpenRecordset("SELECT * FROM mail")
NameQuery = InputBox("Enter A Date To Search For", "Date Query")


rs.MoveFirst
Do Until rs.EOF

If rs.Fields("Date") Like "*" & LCase(NameQuery) & "*" Then
res1 = rs.Fields("Date")
res2 = rs.Fields("Addressee")
res3 = rs.Fields("Description")

MsgBox res1
MsgBox res2
MsgBox res3



Exit Sub
Else
rs.MoveNext
End If


Loop
End Sub

[JamesM]

Changes to make are bolded.

Private Sub Command2_Click()
Dim res1 As String
Dim res2 As String
Dim res3 As String

Set rs = db.OpenRecordset("SELECT * FROM mail")
NameQuery = InputBox("Enter A Date To Search For", "Date Query")


rs.MoveFirst
Do Until rs.EOF

If rs.Fields("Date") Like "*" & LCase(NameQuery) & "*" Then
res1 = rs.Fields("Date")
res2 = rs.Fields("Addressee")
res3 = rs.Fields("Description")

MsgBox res1
MsgBox res2
MsgBox res3



Exit Sub ' remove this line
end if
rs.MoveNext
End If


Loop
End Sub

[Beacon]

Cheers
Actually only needed to remove "Exit Sub" and add another rs.movenext!

Cheers once again

[JamesM]

Why an extra rs.movenext? By putting the rs.movenext after the end if, you make a second rs.movenext statement redundant.

[Beacon]

it works so hey!!

[JamesM]

I guess so.

[ExtremePimpness]

Originally posted by Beacon
Dim res1 As String
Dim res2 As String
Dim res3 As String

this can be shortened to Dim res1,res2,res3 as string

Just a helpful hint

Regards
E.P.

[Lethal]

Extreme Pimpness, your code declared only the last variable as a string, while the others are declared as variants...just a helpful hint..

[JamesM]

E.P, I don't think so.

Dim res1,res2,res3 as string: will declare res1 and res2 as variants and res3 as a string.

[ExtremePimpness]

i am sorry it is early or late i can't tell the differance anymore but the point was you can ommit the other two dim statements

[Lethal]

Hey james, 2 great minds think alike eh.....

[JamesM]

Yep, I hate variants, they chew up my resources hungry little bastards.

[Harddisk]

Originally posted by Beacon
Dont mind me off in my own little dream world!!

Here we go!! Sorry

Private Sub Command2_Click()
  Dim res1 As String
<snipped>

  Do Until rs.EOF
    If rs.Fields("Date") Like "*" & LCase(NameQuery) & "*" Then
      res1 = rs.Fields("Date")
<snipped>
      MsgBox res1
<snipped>

      Exit Sub 'Remove
      Else 'Remove
      rs.MoveNext 'Remove
    End If
    rs.MoveNext 'Add this line
  Loop
End Sub

ExtremePimpness, so early you are into .NET already

Anyway, it just help reduce some time for conversion when porting the code over to .NET in the future.....hehe. Variant? Let it be now, as it will no longer be....

Harddisk