Starting a recordset different in Acc2000 from Acc97?

[MikiH]

Hello all

I'm creating a recordset in access2000 and I'm having a problem starting it. The code I'm using is

Dim dbmydb As Database
Dim rsmyRS As Recordset
Set dbmydb = CurrentDb
Set rsmyRS = dbmydb.OpenRecordset("QRY Email select")

I tried to sort the problem by writing the code in access97 then converting to access2000 on open. This worked fine in both the original acc97 and the converted acc200 file. When I copied the code to the original acc2000 file it crashes on the first line (dim dbmydb as database). I can't even find "Database" in the dropdown list that appears.


(Suppose is what happens when you buy an access97 book and use it to program access2000).


Thanks inadvance.


MikiH

 

[PHV]

Have you referenced the Microsoft DAO library ?
while in VBE, menu Tools -> References ...
And remove ambiguity about recordset object:
Dim rsmyRS As [highlight]DAO.[/highlight]Recordset


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikiH]

Hi

No it wasn't, added it but still no luck,
I've turned if on and the error i'm getting now is "Item not in collection" changed start of code to.

Dim rsmyRS As DAO.Recordset
Set dbmydb = CurrentDb
Set rsmyRS = dbmydb.OpenRecordset("QRY Email select")

Do I need to start my pc?

thanks again

 

[PHV]

No spelling error ?
Are you sure you have a saved query named "QRY Email select" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikiH]

I'd left the set database line below in.

Set dbmydb = CurrentDb.

Removed it and now getting "object variable or with block variable not set"

the code i'm using is as follows.




Dim dbmydb As Database
Dim rsmyRS As DAO.Recordset

'Set dbmydb = CurrentDb
Set rsmyRS = dbmydb.OpenRecordset("QRY Email select")

Dim sendTO As Variant
Dim sendCC As Variant
Dim sendBCC As Variant

sendTO = Null
sendCC = Null
sendBCC = Null

rsmyRS.MoveFirst

Do Until rsmyRS.EOF

If rsmyRS("to") = True Then
sendTO = sendTO & rsmyRS("email address") & ";"

ElseIf rsmyRS("CC") = True Then
sendCC = sendCC & rsmyRS("email address") & ";"

ElseIf rsmyRS("BCC") = True Then
sendBCC = sendBCC & rsmyRS("email address") & ";"

Else

End If

rsmyRS.MoveNext

Loop

DoCmd.SendObject acReport, "RPT 8D", "SnapshotFormat_(*.snp)", sendTO, sendCC, sendBCC, "subject", "message text", False, ""

rsmyRS.Close



Once again thanks

 

[randy700]

Try changing this...
Dim dbmydb As Database
Dim rsmyRS As DAO.Recordset

'Set dbmydb = CurrentDb
Set rsmyRS = dbmydb.OpenRecordset("QRY Email select")

to this...
Dim dbmydb As DAO.Database
Dim rsmyRS As DAO.Recordset

Set dbmydb = CurrentDb
Set rsmyRS = dbmydb.OpenRecordset("[QRY Email select]")


Randy

 

[MikiH]

Gone back to Item not in this collection

(




Are there any other settings I could check against the converted acc97 database. It works in that one?


Thanks

 

[PHV]

You haven't answer my question:
Are you sure you have a saved query named "QRY Email select" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikiH]

Query is there. Has two record when run

Thanks

 

[randy700]

What line of code is highlited?


Randy

 

[MikiH]

None

It's just a message box

 

[MikiH]

Started again with a new sub, adding bits at a time and it crashes when I add the If then else into the do until.

thanks for your help
mick

 

[randy700]

The first thing I'd try is changing the variables to As String instead of As Variant and setting them equal to "" rather than Null.

You also have an extra Else in your If/Then/Else.



Randy

 

[MikiH]

Thanks for all your help I've now sorted the problem. It was the rsmyrs("to") was incorrect, the query I was basing it on was "send to".

Thanks again.
Mick