FindFirst with multiple fields in criteria

[compooper]

Hi,
Whats wrong with my code?


I want to check if a record exists that has the same coName,InvType,InvDate & Biweekly value. If it does then I'll go ahead and delete it, if it doesn't then it doesn't exist.

Function DltInvoice(CoName As Integer, InvType As Integer, InvDate As Date, Biweekly As String)
 
Dim logRec As Recordset
Set logRec = CurrentDb.OpenRecordset("logImportedFiles")
Dim dbs As Database
Set dbs = CurrentDb

strMsg = "This invoice does not exist, please check again"

logRec.FindFirst ("[CompanyID] = " & CoName & " AND [InvTypeID] = " & InvName & " AND [InvoiceDate] = " & InvDate & " AND [Biweekly] = " & Biweekly & "")
If logRec.NoMatch Then
    MsgBox strMsg
Else: dbs.Execute "qryDLTInvoice"
End If

End Function

[pbaldy]

You're treating every field as numeric; are they all? As a general rule, text fields need to be surrounded by a single quote, and date fields by #. Though doing a different thing, this demonstrates the syntax:

BaldyWeb wherecondition

A more detailed explanation, again not doing the same thing but again the syntax regarding delimiters is the same:

http://www.baldyweb.com/BuildSQL.htm

[compooper]

I'm getting an error: Operation is not supported for this type of object
& it highlights logRec.FindFirst strCriteria

CoName, InvType are Integers
InvDate is a Date value
Biweekly is a String

I'm trying to do it a few at a time now, and the two integers are not working

Function DltInvoice(CoName As Integer, InvType As Integer, InvDate As Date, Biweekly As String)
 
Dim logRec As Recordset
Dim strCriteria As String
Set logRec = CurrentDb.OpenRecordset("logImportedFiles")
Dim dbs As Database
Set dbs = CurrentDb
 
strCriteria = "([CompanyID] = " & CoName & ") AND ([InvTypeID] = " & InvType & ")"
strMsg = "This invoice does not exist, please check again"

logRec.FindFirst strCriteria
If logRec.NoMatch Then
    MsgBox strMsg
Else: dbs.Execute "qryDLTInvoice"
End If

End Function

[pbaldy]

Define "not working"? Are you getting an error, if so which one? Wrong result?

A couple of general points. You should disambiguate your declarations of the recordset and database, and both should be closed or set to nothing at the end. Both points demonstrated here:

http://www.baldyweb.com/ErrorTrap.htm

Also, it would be much more efficient to open the recordset on an SQL statement that included a WHERE clause like your FindFirst criteria. Then you test for EOF. The way you're doing it, the entire table gets brought over the wire. This type of thing:

  strSQL = "SELECT Zone FROM tblLabels WHERE CarNum = " & lngCarNum
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

  If Not rs.EOF Then
    'there was at least one record
  Else
    'no records
  End If

[compooper]

Thanks so much! Figured it out using SQL statements like your mentioned

[pbaldy]

Ah good, happy to help!