By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

recordsets

P: n/a
I have used recordsets in my query but only referenceing an sql string and
not a query. I tried to reference a query that was created but it falls
over.

I have that works:
dim Rst2 as DAO.Recordset
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This doesnt:
Dim Rst3 as DAO.Recordset
Set Rst3 = db.OpenRecordset("Myexistingquery")

it falls over on the last line by saying object not found.

Your help would be much appreciated.

Regards
Peter
Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Peter,

You haven't defined the object variable "db". Add the following as lines 2
and 3:
Dim Db As DAO.Database
Set Db = CurrentDb()

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@newsg3.svr.pol.co.uk...
I have used recordsets in my query but only referenceing an sql string and
not a query. I tried to reference a query that was created but it falls
over.

I have that works:
dim Rst2 as DAO.Recordset
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This doesnt:
Dim Rst3 as DAO.Recordset
Set Rst3 = db.OpenRecordset("Myexistingquery")

it falls over on the last line by saying object not found.

Your help would be much appreciated.

Regards
Peter

Nov 13 '05 #2

P: n/a
sorry I didnt mention those they are defined as you set out below.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:e7*****************@newsread3.news.atl.earthl ink.net...
Peter,

You haven't defined the object variable "db". Add the following as lines 2
and 3:
Dim Db As DAO.Database
Set Db = CurrentDb()

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@newsg3.svr.pol.co.uk...
I have used recordsets in my query but only referenceing an sql string and not a query. I tried to reference a query that was created but it falls
over.

I have that works:
dim Rst2 as DAO.Recordset
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This doesnt:
Dim Rst3 as DAO.Recordset
Set Rst3 = db.OpenRecordset("Myexistingquery")

it falls over on the last line by saying object not found.

Your help would be much appreciated.

Regards
Peter


Nov 13 '05 #3

P: n/a
Double Check! Do you have "DAO" in front of database? Do you have the Set Db
statement as I showed?

Steve
PC Datasheet
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news8.svr.pol.co.uk...
sorry I didnt mention those they are defined as you set out below.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:e7*****************@newsread3.news.atl.earthl ink.net...
Peter,

You haven't defined the object variable "db". Add the following as lines 2
and 3:
Dim Db As DAO.Database
Set Db = CurrentDb()

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@newsg3.svr.pol.co.uk...
I have used recordsets in my query but only referenceing an sql string

and not a query. I tried to reference a query that was created but it falls over.

I have that works:
dim Rst2 as DAO.Recordset
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This doesnt:
Dim Rst3 as DAO.Recordset
Set Rst3 = db.OpenRecordset("Myexistingquery")

it falls over on the last line by saying object not found.

Your help would be much appreciated.

Regards
Peter



Nov 13 '05 #4

P: n/a
here it is just to clarify

Dim db As DAO.Database
Dim Rst3 As DAO.Recordset
Set db = CurrentDb

******************
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This section works
***********************
this doesnt
Set Rst3 = db.OpenRecordset("Myqueryindatabase")
the reference section has the dao reference checked.
kind regards
Peter
"PC Datasheet" <no****@nospam.spam> wrote in message
news:1u****************@newsread2.news.atl.earthli nk.net...
Double Check! Do you have "DAO" in front of database? Do you have the Set Db statement as I showed?

Steve
PC Datasheet
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news8.svr.pol.co.uk...
sorry I didnt mention those they are defined as you set out below.
"PC Datasheet" <no****@nospam.spam> wrote in message
news:e7*****************@newsread3.news.atl.earthl ink.net...
Peter,

You haven't defined the object variable "db". Add the following as
lines
2 and 3:
Dim Db As DAO.Database
Set Db = CurrentDb()

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@newsg3.svr.pol.co.uk...
> I have used recordsets in my query but only referenceing an sql
string
and
> not a query. I tried to reference a query that was created but it

falls > over.
>
> I have that works:
> dim Rst2 as DAO.Recordset
> Set Rst2 = db.OpenRecordset(StrSelectPrior)
> Rst2.MoveLast
> Me.TxtPrior.Value = Rst2.RecordCount
>
> This doesnt:
> Dim Rst3 as DAO.Recordset
> Set Rst3 = db.OpenRecordset("Myexistingquery")
>
> it falls over on the last line by saying object not found.
>
> Your help would be much appreciated.
>
> Regards
> Peter
>
>



Nov 13 '05 #5

P: n/a

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news5.svr.pol.co.uk...
here it is just to clarify

Dim db As DAO.Database
Dim Rst3 As DAO.Recordset
Set db = CurrentDb

******************
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This section works
***********************
this doesnt
Set Rst3 = db.OpenRecordset("Myqueryindatabase")
the reference section has the dao reference checked.
kind regards
Peter

Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)

If you leave the second argument blank it assumes that the source is a table
which is why you're getting the error. Have a look in the VBA help file for
OpenRecordset.

Jeff
Nov 13 '05 #6

P: n/a
Interestingly Jeff it now give :
Object variable or With block variable not set (Error 91)

the help section wasnt very helpful on this error.

"Jeff Smith" <No***@Not.This.Address> wrote in message
news:ci**********@lust.ihug.co.nz...

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news5.svr.pol.co.uk...
here it is just to clarify

Dim db As DAO.Database
Dim Rst3 As DAO.Recordset
Set db = CurrentDb

******************
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This section works
***********************
this doesnt
Set Rst3 = db.OpenRecordset("Myqueryindatabase")
the reference section has the dao reference checked.
kind regards
Peter

Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)

If you leave the second argument blank it assumes that the source is a

table which is why you're getting the error. Have a look in the VBA help file for OpenRecordset.

Jeff

Nov 13 '05 #7

P: n/a
Interestingly Jeff it now give :
Object variable or With block variable not set (Error 91)

the help section wasnt very helpful on this error.

"Jeff Smith" <No***@Not.This.Address> wrote in message
news:ci**********@lust.ihug.co.nz...

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news5.svr.pol.co.uk...
here it is just to clarify

Dim db As DAO.Database
Dim Rst3 As DAO.Recordset
Set db = CurrentDb

******************
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This section works
***********************
this doesnt
Set Rst3 = db.OpenRecordset("Myqueryindatabase")
the reference section has the dao reference checked.
kind regards
Peter

Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)

If you leave the second argument blank it assumes that the source is a

table which is why you're getting the error. Have a look in the VBA help file for OpenRecordset.

Jeff

Nov 13 '05 #8

P: n/a
Interestingly Jeff it now give :
Object variable or With block variable not set (Error 91)

the help section wasnt very helpful on this error.

"Jeff Smith" <No***@Not.This.Address> wrote in message
news:ci**********@lust.ihug.co.nz...

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news5.svr.pol.co.uk...
here it is just to clarify

Dim db As DAO.Database
Dim Rst3 As DAO.Recordset
Set db = CurrentDb

******************
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This section works
***********************
this doesnt
Set Rst3 = db.OpenRecordset("Myqueryindatabase")
the reference section has the dao reference checked.
kind regards
Peter

Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)

If you leave the second argument blank it assumes that the source is a

table which is why you're getting the error. Have a look in the VBA help file for OpenRecordset.

Jeff

Nov 13 '05 #9

P: n/a
Something just went wrong on my server isp end hence the three identical
posts my apologies this WAS NOT an attempt to get attention.

regards

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news6.svr.pol.co.uk...
Interestingly Jeff it now give :
Object variable or With block variable not set (Error 91)

the help section wasnt very helpful on this error.

"Jeff Smith" <No***@Not.This.Address> wrote in message
news:ci**********@lust.ihug.co.nz...

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news5.svr.pol.co.uk...
here it is just to clarify

Dim db As DAO.Database
Dim Rst3 As DAO.Recordset
Set db = CurrentDb

******************
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This section works
***********************
this doesnt
Set Rst3 = db.OpenRecordset("Myqueryindatabase")
the reference section has the dao reference checked.
kind regards
Peter

Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)

If you leave the second argument blank it assumes that the source is a

table
which is why you're getting the error. Have a look in the VBA help file

for
OpenRecordset.

Jeff


Nov 13 '05 #10

P: n/a
Try this:

Set rst = CurrentDb.QueryDefs("qryInvoiceEmployee").OpenReco rdset

HTH -Linda
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@newsg3.svr.pol.co.uk...
I have used recordsets in my query but only referenceing an sql string and
not a query. I tried to reference a query that was created but it falls
over.

I have that works:
dim Rst2 as DAO.Recordset
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This doesnt:
Dim Rst3 as DAO.Recordset
Set Rst3 = db.OpenRecordset("Myexistingquery")

it falls over on the last line by saying object not found.

Your help would be much appreciated.

Regards
Peter

Nov 13 '05 #11

P: n/a
Couple things. Make sure the query name is spelled right and that you can open it manually. Also,
if the query is using parameters this will not work. And if it's an
Action query (INSERT INTO, UPDATE or DELETE) you will need to either use the docmd.OpenQuery or
execute via a QueryDef

Dim db as DAO.Database
Dim qdfChange As DAO.QueryDef
Set db = CurrentDb()
Set qd = db.OpenQueryDef(queryname)

qd.Execute
qd.Close

--
Reggie

----------
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@newsg3.svr.pol.co.uk...
Something just went wrong on my server isp end hence the three identical
posts my apologies this WAS NOT an attempt to get attention.

regards

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news6.svr.pol.co.uk...
Interestingly Jeff it now give :
Object variable or With block variable not set (Error 91)

the help section wasnt very helpful on this error.

"Jeff Smith" <No***@Not.This.Address> wrote in message
news:ci**********@lust.ihug.co.nz...
>
> "Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
> news:ci**********@news5.svr.pol.co.uk...
> > here it is just to clarify
> >
> > Dim db As DAO.Database
> > Dim Rst3 As DAO.Recordset
> > Set db = CurrentDb
> >
> > ******************
> > Set Rst2 = db.OpenRecordset(StrSelectPrior)
> > Rst2.MoveLast
> > Me.TxtPrior.Value = Rst2.RecordCount
> >
> > This section works
> > ***********************
> > this doesnt
> > Set Rst3 = db.OpenRecordset("Myqueryindatabase")
> >
> >
> > the reference section has the dao reference checked.
> >
> >
> > kind regards
> > Peter
>
>
> Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)
>
> If you leave the second argument blank it assumes that the source is a

table
> which is why you're getting the error. Have a look in the VBA help file

for
> OpenRecordset.
>
> Jeff
>
>



Nov 13 '05 #12

P: n/a
Thanks guys Linda solution worked perfectly. Iam finding it difficult not to
write the vba but find the help for things like this any pointers?

regards
Peter
"Squirrel" <wi*****@covad.net> wrote in message
news:88**************************@msgid.meganewsse rvers.com...
Try this:

Set rst = CurrentDb.QueryDefs("qryInvoiceEmployee").OpenReco rdset

HTH -Linda
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@newsg3.svr.pol.co.uk...
I have used recordsets in my query but only referenceing an sql string and not a query. I tried to reference a query that was created but it falls
over.

I have that works:
dim Rst2 as DAO.Recordset
Set Rst2 = db.OpenRecordset(StrSelectPrior)
Rst2.MoveLast
Me.TxtPrior.Value = Rst2.RecordCount

This doesnt:
Dim Rst3 as DAO.Recordset
Set Rst3 = db.OpenRecordset("Myexistingquery")

it falls over on the last line by saying object not found.

Your help would be much appreciated.

Regards
Peter


Nov 13 '05 #13

P: n/a
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ci**********@news8.svr.pol.co.uk...
Thanks guys Linda solution worked perfectly. Iam finding it difficult not to
write the vba but find the help for things like this any pointers?


The fact that your previous statement (correct BTW) gave the error it did and
that Linda's suggestion worked indicates that your database variable (db) had
gone out of scope and was no longer pointing at a database object.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.