FindFirst not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Below is the code which I have against a command button on a form and for
some reason even though the data is in the table it checks it comes back as
not found.

Can anyone please help me with what I have done wrong.

Set rs = CurrentDb.OpenRecordset("tbl_IssuesError", 2)
Set rs2 = CurrentDb.OpenRecordset("tbl_Head_Office_Stock", 2)
Set rs3 = CurrentDb.OpenRecordset("tbl_Issues", 2)

rs.MoveFirst
Do
stStore = rs.Fields("StoreNo")
stStore = rs.Fields("StoreNo")
stDenom = rs.Fields("Denom")
stVoucher = rs.Fields("VoucherID")
stDate = rs.Fields("IssueDate")
stWhere = "[Denom]=""" & stDenom & """ And [Voucher]=""" & stVoucher
& """"
If rs.Fields("Investigate") = True And rs.Fields("Fixed") = False Then
rs.Edit
rs.Fields("Fixed") = True
rs.Update
If rs.Fields("MissingHeadOffice") = True Then
'''Check Head Office Stock table'''
rs2.MoveFirst
rs2.FindFirst stWhere
If rs2.NoMatch Then '''''''''''''THIS IS WHERE
IT COMES UP AS NOMATCH HOWEVER IT IS IN THE DATABASE'''''''
Call Update_Issued_Error
Else
Call Update_Issued_Table
End If
ElseIf rs.Fields("AlreadySent") = True Then
'''Check if already issued to another store in Head Office
table'''
rs2.MoveFirst
rs2.FindFirst stWhere
If rs2.NoMatch Then
Call Update_Issued_Error
Else
Call Update_Issued_Table
End If
ElseIf rs.Fields("Duplicate") = True Then
'''Check Issued table for duplication'''
rs3.MoveFirst
rs3.FindFirst stWhere
If rs3.NoMatch Then
Call Update_Issued_Error
Else
Call Update_Issued_Table
End If
End If
End If
rs.MoveNext
Loop Until rs.EOF
 
2 approaches:
1) put a temporary messagebox in your code that will display the contents of
stWhere before FindFirst tries to use it:
msgbox stWhere
2) put a breakpoint on the FindFirst line. Then, in the Immediate window
(VBE>View>Immediate), type "?stWhere" (without quotes)

In both cases look at the results. Do they make sense? Correct
number/placement of spaces and quotes? Edit as necessary.

Note that if [Denom] or [Voucher] are numeric fields you do NOT want the
quotes around the criteria values. You only need the quotes when the field
being searched is a text field. This would be my first guess as to what the
current problem is.

HTH,
 
Hi

Below is the code which I have against a command button on a form and for
some reason even though the data is in the table it checks it comes back as
not found.

Can anyone please help me with what I have done wrong.

Set rs = CurrentDb.OpenRecordset("tbl_IssuesError", 2)
Set rs2 = CurrentDb.OpenRecordset("tbl_Head_Office_Stock", 2)
Set rs3 = CurrentDb.OpenRecordset("tbl_Issues", 2)

rs.MoveFirst
Do
stStore = rs.Fields("StoreNo")
stStore = rs.Fields("StoreNo")
stDenom = rs.Fields("Denom")
stVoucher = rs.Fields("VoucherID")
stDate = rs.Fields("IssueDate")
stWhere = "[Denom]=""" & stDenom & """ And [Voucher]=""" & stVoucher
& """"
If rs.Fields("Investigate") = True And rs.Fields("Fixed") = False Then
rs.Edit
rs.Fields("Fixed") = True
rs.Update
If rs.Fields("MissingHeadOffice") = True Then
'''Check Head Office Stock table'''
rs2.MoveFirst
rs2.FindFirst stWhere
If rs2.NoMatch Then '''''''''''''THIS IS WHERE
IT COMES UP AS NOMATCH HOWEVER IT IS IN THE DATABASE'''''''
Click to expand...

Could you post the actual value of stWhere? What are the datatypes of Voucher
and Denom? Are either of them Lookup Fields (in which case this emphatically
will NOT work)?

John W. Vinson [MVP]
 
Back
Top