QueryDef

[Brad]

Thanks for taking the time to read my question.

I am trying to create a record set from a query. The
query returns 1 record.

This works:
Set qdf = dbs.QueryDefs("qryTest")

But I don't want to make another query in my database,
but rather use SQL in the code.

Shouldn't this work?

Set qdf = dbs.QueryDefs("SELECT
tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType " & _
"FROM tblGeneticsDefaults " & _
"WHERE
(((tblGeneticsDefaults.Source)=[Forms]![frmMBarn]!
[LivestockSource]));")


I keep getting an error. 3265 Item not found in this
collection.

what am I doing wrong?

Thanks,

Brad

 

[Randy]

If you're working in frmMBarn, then try this idea:

Dim strA as string
strA = "SELECT Source, GeneticType " & _
"FROM tblGeneticsDefaults " & _
"WHERE (Source)= '" & Me.LivestockSource & "'"

Then use strA as your query statement of a recordset in
DAO or ADO, and process from there. Whenever you have
problems like this, the Debug window is very helpful.

Good Luck!

 

[Douglas J. Steele]

No, it won't work.

The first one works because you have a query named qryTest. You don't,
however, have a query named SELECT tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType etc.

Try:

Set qdf = dbs.CreateQueryDef("", "SELECT
tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType " & _
"FROM tblGeneticsDefaults " & _
"WHERE
(((tblGeneticsDefaults.Source)=[Forms]![frmMBarn]!
[LivestockSource]));")

 

[Paul]

If all that is required is the recordset - why create the querydef?

Why not just do:

Set rs = Currentdb.OpenRecordset("Select...

--
Paul
Visit my website www.pdtech.co.uk for developer resources.

No, it won't work.

The first one works because you have a query named qryTest. You don't,
however, have a query named SELECT tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType etc.

Try:

Set qdf = dbs.CreateQueryDef("", "SELECT
tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType " & _
"FROM tblGeneticsDefaults " & _
"WHERE
(((tblGeneticsDefaults.Source)=[Forms]![frmMBarn]!
[LivestockSource]));")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for taking the time to read my question.

I am trying to create a record set from a query. The
query returns 1 record.

This works:
Set qdf = dbs.QueryDefs("qryTest")

But I don't want to make another query in my database,
but rather use SQL in the code.

Shouldn't this work?

Set qdf = dbs.QueryDefs("SELECT
tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType " & _
"FROM tblGeneticsDefaults " & _
"WHERE
(((tblGeneticsDefaults.Source)=[Forms]![frmMBarn]!
[LivestockSource]));")


I keep getting an error. 3265 Item not found in this
collection.

what am I doing wrong?

Thanks,

Brad

 

[Douglas J. Steele]

One reason is if you're using a pass-through query. I often create
through-away queries to run against SQL Server, for instance. You can even
invoke stored procedures this way.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If all that is required is the recordset - why create the querydef?

Why not just do:

Set rs = Currentdb.OpenRecordset("Select...

--
Paul
Visit my website www.pdtech.co.uk for developer resources.

No, it won't work.

The first one works because you have a query named qryTest. You don't,
however, have a query named SELECT tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType etc.

Try:

Set qdf = dbs.CreateQueryDef("", "SELECT
tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType " & _
"FROM tblGeneticsDefaults " & _
"WHERE
(((tblGeneticsDefaults.Source)=[Forms]![frmMBarn]!
[LivestockSource]));")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for taking the time to read my question.

I am trying to create a record set from a query. The
query returns 1 record.

This works:
Set qdf = dbs.QueryDefs("qryTest")

But I don't want to make another query in my database,
but rather use SQL in the code.

Shouldn't this work?

Set qdf = dbs.QueryDefs("SELECT
tblGeneticsDefaults.Source,
tblGeneticsDefaults.GeneticType " & _
"FROM tblGeneticsDefaults " & _
"WHERE
(((tblGeneticsDefaults.Source)=[Forms]![frmMBarn]!
[LivestockSource]));")


I keep getting an error. 3265 Item not found in this
collection.

what am I doing wrong?

Thanks,

Brad