recordset.findfirst problem

[smig]

hi

I'm trying to use a code that used many times before but here it won't work.
here is the code, I have problem with

set MyRS = CurrentDB.OpenRecordset("MyTable")
 
for each Index in me.MyList.itemsSelected
  MyRS.FindFirst "MyTableKey = " & MyList.itemsSelected.column(0)
  MyRS.Edit
  ...
next Index

it's not the complete code.
all varaibles are decalred

problem:
for the .FindFirst line I get a message "The Object does not support this action" (Or something similar)
If I go over the .FindFirst line everything works fine, including editing and updating the recordset.

is it possible the .FindFirst won't work if MyTable is open on a form, either directly or using a query ?

 

[boblarson]

I don't know for sure but this line:
set MyRS as CurrentDB.OpenRecordset("MyTable")
should be
set MyRS = CurrentDB.OpenRecordset("MyTable")

 

[smig]

you are right. fixed.
only a tipo mistake when I copied the code.

this isn't the problem

 

[boblarson]

Have you tried

Dim MyRS As DAO.Recordset

instead of either Object or just Recordset? If you are on a version where the ADO reference is checked and is higher than the DAO reference or you don't have a DAO reference then it can fail.

 

[smig]

as I said it's not the full code.
MyRS is declared as DAO.Recordset.

.movefirst, movenext ... will work
even .edit and .update will work

only .find won't

 

[boblarson]

Okay, I think you need this:

[COLOR=red][B]Dim varItm As Variant[/B][/COLOR]
 
for each [COLOR=red][B]varItm[/B][/COLOR] in me.MyList.itemsSelected
  MyRS.FindFirst "MyTableKey = " & MyList.[B][COLOR=red]ItemData(varItm)[/COLOR][/B]
  MyRS.Edit
  ...
next Index

 

[DCrake]

Dim varItm As Variant
 
for each varItm in me.MyList.itemsSelected
  MyRS.FindFirst "MyTableKey = " & MyList.ItemData(varItm)
  MyRS.Edit
  ...
next [B][COLOR="Red"]varItm [/COLOR][/B]

 

[smig]

thanks, but believe me this is not the problem
all decalred as required.

again, if I replace the .Find with .Move (any kind of find and any kind of move) it will work perfectly

I use the exact same code, but with a different table on the same module and it also work.
that's why I had the thought an open table might cause the problem

 

[ChrisO]

Try: -

Set MyRS = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

or

Set MyRS = CurrentDb.OpenRecordset("MyTable", 2)

Chris.

 

[gemma-the-husky]

maybe you need to test for nomatch after the find operation.
maybe you don't need column(0) of the listbox.

try some msgboxes to show you the data. or add a breakpoint and step through.

 

[LPurvis]

Chris is right about the cause of this error.
The table in question is, presumably local, where others haven't been in the past?
This would cause the default recordset type to be TableType and hence the FindFirst method not supported.
Simply specifying a Dynaset circumvents that.

The possibility that the recordset might require disambiguation could hold water as the description of the error isn't exact, but for the quote:
"If I go over the .FindFirst line everything works fine, including editing and updating the recordset."
An ADO recordset doesn't support the Edit method. It would fail there. Therefore this is DAO and it's the recordset type.
Disambiguation is always a good idea though IMO.

Of course - those that have pointed out the necessity for the listbox property change are right too. Selecting the Column(0) value each time won't get you very far. ;-)
You could have gone with
MyRS.FindFirst "MyTableKey = " & MyList.Column(0, Index)
but as already impled, "Index" really isn't a good choice for a variable name.

Cheers.

 

[smig]

thanks a lot
dbOpenDynaset it is

thanks for the info Purvis
this table is a local TEMP table. and yes, I use DAO.
should I set the dbOpenDynaset for all recordsets (they all sets to default with no type) ?

what's wrong with Selecting the Column(0) value? here I use it because in another recordset, on the same span, I use the Column(1).

Index is not the real name I use.
I use he name Tnua. now you'll ask what the hell this word is actually it's the Hebrew name for move, using English characters.
to clear confusions I'm not copying the exact code.

this is the real code I'm using now:

Dim RS_TEMP_Lekochot_Tnuot As DAO.Recordset
 
Set db = CurrentDb()
 
Set RS_TEMP_Lekochot_Tnuot = db.OpenRecordset("TEMP_Lekochot_Tnuot", dbOpenDynaset)
    For Each Tnua In Me.SelectTnuotForHeshbonit.ItemsSelected
        RS_TEMP_Lekochot_Tnuot.MoveFirst
        RS_TEMP_Lekochot_Tnuot.FindFirst "TEMP_Lakoach_TnuaID = " & Me.SelectTnuotForHeshbonit.Column(0, Tnua)
        RS_TEMP_Lekochot_Tnuot.Edit
        RS_TEMP_Lekochot_Tnuot.Fields("HeshbonitID") = pb_HeshbonitID
        RS_TEMP_Lekochot_Tnuot.Update
    Next Tnua
    RS_TEMP_Lekochot_Tnuot.Close
Set RS_TEMP_Lekochot_Tnuot = Nothing

but now I wonder if it won't be better to use this code.
number of updates should not over 20 I think, but the updated table can be big and it grow with time

Dim RS_TEMP_Lekochot_Tnuot As DAO.Recordset
 
Set db = CurrentDb()
 
For Each Tnua In Me.SelectTnuotForHeshbonit.ItemsSelected
    Set RS_TEMP_Lekochot_Tnuot = db.OpenRecordset("SELECT * FROM [TEMP_Lekochot_Tnuot] WHERE [TEMP_Lekochot_Tnuot].[TEMP_Lakoach_TnuaID] = " & Me.SelectTnuotForHeshbonit.Column(0, Tnua) & "", dbOpenDynaset)
        RS_TEMP_Lekochot_Tnuot.MoveFirst
        RS_TEMP_Lekochot_Tnuot.Edit
        RS_TEMP_Lekochot_Tnuot.Fields("HeshbonitID") = pb_HeshbonitID
        RS_TEMP_Lekochot_Tnuot.Update
        RS_TEMP_Lekochot_Tnuot.Close
    Set RS_TEMP_Lekochot_Tnuot = Nothing
Next Tnua

 

[gemma-the-husky]

different tack. it seems counter productive to have to keep opening recordsets.

can't you just have a query joining the items in the list box, with the tables/data you want to modify. then the code won't be as hard to write.

 

[LPurvis]

Well, if you wanted to prevent duplicates, then you would have a unique index on that field in question (TEMP_Lakoach_TnuaID) so you don't need to even check for the record first - which saves more overhead.
As you're matching - you need to select as efficiently as possible.

FWIW I don't think your second option is better unless you have a large recordset. In which case there will be a tipping point. Selecting a few rows from a very large set is preferable - even iteratively. More than a few and it will become painful.
Make the selection first.

Dim db As DAO.Database
Dim RS_TEMP_Lekochot_Tnuot As DAO.Recordset
Dim strList as String
dim strSQL As String
For Each Tnua In Me.SelectTnuotForHeshbonit.ItemsSelected
    strList = strList & "," & Me.SelectTnuotForHeshbonit.Column(0, Tnua)
Next Tnua
strSQL = "SELECT * FROM TEMP_Lekochot_Tnuot TEMP_Lakoach_TnuaID In (" & Mid(strList,2) & ")"
Set db = CurrentDb()
Set RS_TEMP_Lekochot_Tnuot = db.OpenRecordset(strSQL, dbOpenDynaset)
With RS_TEMP_Lekochot_Tnuot 
    Do Until .EOF
        .Edit
        .Fields("HeshbonitID") = pb_HeshbonitID
        .Update
        .MoveNext
    Loop
.Close
End WIth
Set RS_TEMP_Lekochot_Tnuot = Nothing
Set db = Nothing

But the much more preferable method would be

Dim strList as String
dim strSQL As String
For Each Tnua In Me.SelectTnuotForHeshbonit.ItemsSelected
    strList = strList & "," & Me.SelectTnuotForHeshbonit.Column(0, Tnua)
Next Tnua
strSQL = "UPDATE TEMP_Lekochot_Tnuot Set HeshbonitID = " & pb_HeshbonitID & " WHERE TEMP_Lakoach_TnuaID In (" & Mid(strList,2) & ")"
CurrentDb.Execute strSQL, dbFailOnError

Cheers.

 

[smig]

big thanks for the help

when I had problems with the .FindFirst I changed it into a query. seems this is the right way

still I learned a lot here.

TEMP_Lakoach_TnuaID is a unique index. and it is what sit in Column0 of SelectTnuotForHeshbonit

is this the best code I can use or is there an option to improve it more ?

strList as String
dim strSQL As String
For Each Tnua In Me.SelectTnuotForHeshbonit.ItemsSelected
    strList = strList & "," & Me.SelectTnuotForHeshbonit.Column(0, Tnua)
Next Tnua
strSQL = "UPDATE TEMP_Lekochot_Tnuot Set HeshbonitID = " & pb_HeshbonitID & " WHERE TEMP_Lakoach_TnuaID In (" & Mid(strList,2) & ")"
CurrentDb.Execute strSQL, dbFailOnError

 

[LPurvis]

I'd say you're looking at the optimum choice for what you're doing.

 

[smig]

thanks again