recordsets

[Peter Bailey]

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

[PC Datasheet]

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

[Peter Bailey]

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

[PC Datasheet]

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

[Peter Bailey]

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
>
>

[Jeff Smith]

"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

[Peter Bailey]

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

[Peter Bailey]

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

[Peter Bailey]

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

[Peter Bailey]

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

[Squirrel]

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

[Reggie]

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
>
>

[Peter Bailey]

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

[Rick Brandt]

"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