I have two recordsets open and wish to use the FindFirst command to get to the record where the "match" is ProductID in both tables.

My code is: (after opening all the Dim statements)

Set rst = dbs.OpenRecordset("My casa", dbOpenDynaset, dbReadOnly
Set rstUpdate = dbs.OpenRecordset("Products", dbOpenTable)

With rst

strProductID = ![ProductID]
strFilter = rstUpdate![ProductID] = " & strProductID & "
rstUpdate.FindFirst strfilter <----- error here "operation is not supported for this type of object"


etc ... - the next bit which Edits the recordset works. i/c the End With statement

Clearly a major error somewhere in the FindFirst statement or the string filter associated with it.

Any help as ever would be appreciated.

Try:
Set rstUpdate = dbs.OpenRecordset("Products", dbOpenDynaset)

Thanks - that has fixed the error - but now the FindFirst is not going to the correct record. I guess the string is still not right. Thanks so far.

Try:
strFilter = "[ProductID] = " & strProductID

Is there a reason you don't open the recordsets on an SQL string that only brings up the desired record? It would be more efficient than bringing in the whole table.

The whole story - I have a linked table which relates to the rst recordset. Because some of the field names are different, I want to either Append or Update the rstUpdate recordset depending on whether there is a ProductID match with the rstUpdate recordset. In other words, the external linked table is a master web-based record and the internal rstupdate table must contain all the linked records i/c any new ones plus retain any internal additional records.

I am using the varX = DLookup("[ProductID]", "Products", "[ProductID] = '" & strProductID & "'")

If IsNull(varX) Then ' No Match = APPEND approach to decide whether to Append the new record or Update the exisiting record. The DlookUP is working fine and directs to the right section of the code; I just can't get the Update to work as the FindFirst is not working and so the pointer in not going to the matched record.

Have you tried RG's suggestion in post 4, since that looks more like the correct format?

BTW, I still might open with an SQL statement. If the target recordset opens EOF then you do your append, otherwise update. I would always avoid opening a recordset on an entire table unless absolutely necessary.

GOT IT - trial and error but RG's suggestion - prompted also by PB - that within the FindFirst syntax you do not have to reference the recordset put me in the right area. The solution (without the use of a variable) is:

rstProducts.FindFirst "[ProductID]= '" & ![ProductID] & "'"

Thanks Guys - appreciated as ever. Not straightforward when you have two recordsets open.

In that case try:
strFilter = "[ProductID] = '" & strProductID & "'"

Paul's suggestions are for a speedier application and just a good coding practice. It saves trying to speed up the application later on when it runs on a network and the tables have grown considerably. Glad to hear you got it sorted.