QueryDef

  • Thread starter Thread starter Brad
  • Start date Start date
B

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
 
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!
 
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]));")
 
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.

Douglas J. Steele said:
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!)



Brad said:
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
Click to expand...
Click to expand...
 
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!)



Paul said:
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.

Douglas J. Steele said:
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!)



Brad said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top