Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Starting a recordset different in Acc2000 from Acc97?

Status
Not open for further replies.

MikiH

Programmer
Sep 25, 2002
78
GB
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
 
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
 
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
 
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
 
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
 
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
 
Gone back to Item not in this collection

:eek:(




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


Thanks
 
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
 
Query is there. Has two record when run

Thanks
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top