Find.first in recordset

[jctaylor]

I have two recordsets and I am trying to loop through the first and find the record in the second with the same invoice number, but it keeps taking me to the first record in the second recordset instead.



PartialCode

Set rst = CurrentDb.OpenRecordset(StrSql)
Set rst1 = CurrentDb.OpenRecordset("AR")
rst.MoveFirst

Do While Not rst.EOF
With rst1
.FindFirst "[rst1!inv_no]= '" & [rst!invno] & "' "
If rst1.NoMatch Then
MsgBox "Record not found"
Exit Sub
End If
MsgBox rst1!client & " " & rst1!inv_no & " " & rst1!inv_date
End With
rst.MoveNext

Loop

rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing

/Code

I am not sure what I am doing wrong. I have tried several things but nothing works. Any help in leading me in the right direction would be appreciated.

[orange]

Is the invoice number numeric or text datatype?

[jctaylor]

Invoice number is text

[pbaldy]

Try

.FindFirst "[inv_no]= '" & rst!invno & "'"

[jctaylor]

It still brings up the first record. Why doesn't my nomatch work since it didn't find what I was looking for?

[pbaldy]

Can you attach the db here?

[jctaylor]

How do I do that?

[pbaldy]

Compact/repair, then zip, then attach in the Go Advanced area.

[jctaylor]

It doesn't like my zipfile that I keep trying to upload.

[pbaldy]

Is it under the 2mb limit?

[jctaylor]

No, it's greater than 2mb.

[pbaldy]

Can you cut it down to just the objects required to recreate this problem (by exporting to a new db)? If not, you can email it to me.

[June7]

Don't use [ ] around the record set reference in the FindFirst expression. Oh, I see Paul already caught that.

Maybe make a copy and delete data. Be sure to run compact and repair before zipping.

[jctaylor]

I finally got it to work. I had ' " reversed!! Thanks for your help.

[pbaldy]

Glad you got it sorted.