multiple dao recordsets???

[MissiMaths]

I am trying to append records in one table depending on records in two
other tables. I have tried to do it using DAO, but I keep getting an
error message saying

'The Microsoft Jet database engine could not find the object ". Make
sure that the object exists and that you spell its name and the path
name correctly.'

Can you not have three different recordsets in the DAO recordset? Or
is that a completely stupid question?
My code is

Dim MyDb As Database
Dim MyRecs As DAO.Recordset

Set MyDb = CurrentDb()
Set MyRecs = MyDb.OpenRecordset(Table1,DB_OPEN_TABLE)

Dim MyRecs2 As DAO.Recordset
Set MyRecs2 = MyDb.OpenRecordset(Table2, DB_OPEN_TABLE)

Dim MyRecs3 As DAO.Recordset
Set MyRecs3 = MyDb.OpenRecordset(Table3, DB_OPEN_DYNASET)

The records in Table3 are the ones I wish to add to and alter using
information from and depending on certain entries in Table1 and
Table2. Have I the DB_OPEN_?=(table or dynaset) written wrongly? I
can't find any info in the help as regards exactly what these mean,
but using them in this way in the past has worked. Putting checks into
the above code, the error is thrown up after or during the definition
of MyRecs2.

If this method just won't work...how might I move periodically through
fields and records in both Table1 and Table2, and subsequently alter
Table3? Any suggestions would be greatly appreciated. Thank you in
advance.

[Phil Stanton]

Assuming Table1, Table2 and table3 exist, put them in inverted comas in the
openrecordset statement
Set MyRecs = MyDb.OpenRecordset("Table1") etc

HTH

Phil
"MissiMaths" <mi********@hotmail.com> wrote in message
news:75**************************@posting.google.c om...

I am trying to append records in one table depending on records in two
other tables. I have tried to do it using DAO, but I keep getting an
error message saying

'The Microsoft Jet database engine could not find the object ". Make
sure that the object exists and that you spell its name and the path
name correctly.'

Can you not have three different recordsets in the DAO recordset? Or
is that a completely stupid question?
My code is

Dim MyDb As Database
Dim MyRecs As DAO.Recordset

Set MyDb = CurrentDb()
Set MyRecs = MyDb.OpenRecordset(Table1,DB_OPEN_TABLE)

Dim MyRecs2 As DAO.Recordset
Set MyRecs2 = MyDb.OpenRecordset(Table2, DB_OPEN_TABLE)

Dim MyRecs3 As DAO.Recordset
Set MyRecs3 = MyDb.OpenRecordset(Table3, DB_OPEN_DYNASET)

The records in Table3 are the ones I wish to add to and alter using
information from and depending on certain entries in Table1 and
Table2. Have I the DB_OPEN_?=(table or dynaset) written wrongly? I
can't find any info in the help as regards exactly what these mean,
but using them in this way in the past has worked. Putting checks into
the above code, the error is thrown up after or during the definition
of MyRecs2.

If this method just won't work...how might I move periodically through
fields and records in both Table1 and Table2, and subsequently alter
Table3? Any suggestions would be greatly appreciated. Thank you in
advance.

[Tim Marshall]

MissiMaths wrote:

Dim MyRecs2 As DAO.Recordset
Set MyRecs2 = MyDb.OpenRecordset(Table2, DB_OPEN_TABLE)

Dim MyRecs3 As DAO.Recordset
Set MyRecs3 = MyDb.OpenRecordset(Table3, DB_OPEN_DYNASET)

As Phil suggested, enclose the table name in double quotes.

Also, it's good programming practice todeclare ALL variables at the very
beginning of a procedure, regardless of where the variable will be used.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[MissiMaths]

Thank you! Thought it was something simple but just couldn't see it.
So obvious now. Thanks for your help!