recordsets

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,

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
resource@pcdatasheet.com
www.pcdatasheet.com


"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cihtaq$rh7$1@newsg3.svr.pol.co.uk...[color=blue]
> 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
>
>[/color]

sorry I didnt mention those they are defined as you set out below.
"PC Datasheet" <nospam@nospam.spam> wrote in message
news:e7%2d.6012$mb6.2386@newsread3.news.atl.earthl ink.net...[color=blue]
> 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
> resource@pcdatasheet.com
> www.pcdatasheet.com
>
>
> "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> news:cihtaq$rh7$1@newsg3.svr.pol.co.uk...[color=green]
> > I have used recordsets in my query but only referenceing an sql string[/color][/color]
and[color=blue][color=green]
> > 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
> >
> >[/color]
>
>[/color]

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" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cii1j2$sab$1@news8.svr.pol.co.uk...[color=blue]
> sorry I didnt mention those they are defined as you set out below.
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:e7%2d.6012$mb6.2386@newsread3.news.atl.earthl ink.net...[color=green]
> > Peter,
> >
> > You haven't defined the object variable "db". Add the following as lines[/color][/color]
2[color=blue][color=green]
> > and 3:
> > Dim Db As DAO.Database
> > Set Db = CurrentDb()
> >
> > --
> > PC Datasheet
> > Your Resource For Help With Access, Excel And Word Applications
> > resource@pcdatasheet.com
> > www.pcdatasheet.com
> >
> >
> > "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> > news:cihtaq$rh7$1@newsg3.svr.pol.co.uk...[color=darkred]
> > > I have used recordsets in my query but only referenceing an sql string[/color][/color]
> and[color=green][color=darkred]
> > > not a query. I tried to reference a query that was created but it[/color][/color][/color]
falls[color=blue][color=green][color=darkred]
> > > 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
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <nospam@nospam.spam> wrote in message
news:1u03d.5900$n16.703@newsread2.news.atl.earthli nk.net...[color=blue]
> Double Check! Do you have "DAO" in front of database? Do you have the Set[/color]
Db[color=blue]
> statement as I showed?
>
> Steve
> PC Datasheet
>
>
> "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> news:cii1j2$sab$1@news8.svr.pol.co.uk...[color=green]
> > sorry I didnt mention those they are defined as you set out below.
> > "PC Datasheet" <nospam@nospam.spam> wrote in message
> > news:e7%2d.6012$mb6.2386@newsread3.news.atl.earthl ink.net...[color=darkred]
> > > Peter,
> > >
> > > You haven't defined the object variable "db". Add the following as[/color][/color][/color]
lines[color=blue]
> 2[color=green][color=darkred]
> > > and 3:
> > > Dim Db As DAO.Database
> > > Set Db = CurrentDb()
> > >
> > > --
> > > PC Datasheet
> > > Your Resource For Help With Access, Excel And Word Applications
> > > resource@pcdatasheet.com
> > > www.pcdatasheet.com
> > >
> > >
> > > "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> > > news:cihtaq$rh7$1@newsg3.svr.pol.co.uk...
> > > > I have used recordsets in my query but only referenceing an sql[/color][/color][/color]
string[color=blue][color=green]
> > and[color=darkred]
> > > > not a query. I tried to reference a query that was created but it[/color][/color]
> falls[color=green][color=darkred]
> > > > 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
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cij2t1$hoe$1@news5.svr.pol.co.uk...[color=blue]
> 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[/color]


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

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" <NoWay@Not.This.Address> wrote in message
news:cij3rv$hvt$1@lust.ihug.co.nz...[color=blue]
>
> "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> news:cij2t1$hoe$1@news5.svr.pol.co.uk...[color=green]
> > 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[/color]
>
>
> Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)
>
> If you leave the second argument blank it assumes that the source is a[/color]
table[color=blue]
> which is why you're getting the error. Have a look in the VBA help file[/color]
for[color=blue]
> OpenRecordset.
>
> Jeff
>
>[/color]

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" <NoWay@Not.This.Address> wrote in message
news:cij3rv$hvt$1@lust.ihug.co.nz...[color=blue]
>
> "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> news:cij2t1$hoe$1@news5.svr.pol.co.uk...[color=green]
> > 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[/color]
>
>
> Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)
>
> If you leave the second argument blank it assumes that the source is a[/color]
table[color=blue]
> which is why you're getting the error. Have a look in the VBA help file[/color]
for[color=blue]
> OpenRecordset.
>
> Jeff
>
>[/color]

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" <NoWay@Not.This.Address> wrote in message
news:cij3rv$hvt$1@lust.ihug.co.nz...[color=blue]
>
> "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> news:cij2t1$hoe$1@news5.svr.pol.co.uk...[color=green]
> > 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[/color]
>
>
> Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)
>
> If you leave the second argument blank it assumes that the source is a[/color]
table[color=blue]
> which is why you're getting the error. Have a look in the VBA help file[/color]
for[color=blue]
> OpenRecordset.
>
> Jeff
>
>[/color]

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" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cij6bn$ncb$1@news6.svr.pol.co.uk...[color=blue]
> 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" <NoWay@Not.This.Address> wrote in message
> news:cij3rv$hvt$1@lust.ihug.co.nz...[color=green]
> >
> > "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> > news:cij2t1$hoe$1@news5.svr.pol.co.uk...[color=darkred]
> > > 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[/color]
> >
> >
> > Try Set Rst3 = db.OpenRecordset("Myqueryindatabase", dbOpenDynaset)
> >
> > If you leave the second argument blank it assumes that the source is a[/color]
> table[color=green]
> > which is why you're getting the error. Have a look in the VBA help file[/color]
> for[color=green]
> > OpenRecordset.
> >
> > Jeff
> >
> >[/color]
>
>[/color]

Try this:

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

HTH -Linda


"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cihtaq$rh7$1@newsg3.svr.pol.co.uk...[color=blue]
> 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
>
>[/color]

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" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cij6k4$rtm$1@newsg3.svr.pol.co.uk...[color=blue]
> 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" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> news:cij6bn$ncb$1@news6.svr.pol.co.uk...[color=green]
>> 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" <NoWay@Not.This.Address> wrote in message
>> news:cij3rv$hvt$1@lust.ihug.co.nz...[color=darkred]
>> >
>> > "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
>> > news:cij2t1$hoe$1@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[/color]
>> table[color=darkred]
>> > which is why you're getting the error. Have a look in the VBA help file[/color]
>> for[color=darkred]
>> > OpenRecordset.
>> >
>> > Jeff
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

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" <wiseowl@covad.net> wrote in message
news:88b85$414d3e86$44a50369$8209@msgid.meganewsse rvers.com...[color=blue]
> Try this:
>
> Set rst = CurrentDb.QueryDefs("qryInvoiceEmployee").OpenReco rdset
>
> HTH -Linda
>
>
> "Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
> news:cihtaq$rh7$1@newsg3.svr.pol.co.uk...[color=green]
> > I have used recordsets in my query but only referenceing an sql string[/color][/color]
and[color=blue][color=green]
> > 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
> >
> >[/color]
>
>[/color]

"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cijlmi$4h0$1@news8.svr.pol.co.uk...[color=blue]
> 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?[/color]

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