Query Def not recognized

A

alex

Query Def not recognized

Hello,
Using Access ’03…

I’ve created a qdf in VBA and I’d like to use it as part of another
query…

My problem, is that the qdf is not being recognized; i.e., when the
query runs, I get a parameter box asking for a value?

Here’s some of my code:
‘’’’’’’’’’’’’’’’
Set dbs = CurrentDb
Dim strQDFname As String
strQDFname = "myQryDef" 'query def name

With dbs
Set qdf = .CreateQueryDef(strQDFname, strQDFsql)
qdf.Close
End With
Set qdf = Nothing

‘sql code using qdf
Dim strSQL As String
strSQL = _
" myQryDef.Field1, " & _
" myQryDef. Field2, " & _
" myQryDef. Field3, " & _
" myQryDef. Field4, " & _
" myTable.Field1, " & _
" myTable. Field2, " & _
" myTable. Field3, " & _
" myTable. Field4 "
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
I can see the query object (myQryDef) and I can use it with other
query objects (I can even run it by just clicking on it); I just can’t
use it w/ VBA for some reason? Do I need to append it to some
collection?

Any thoughts?
alex
 
R

Roger Carlson

Unless you're leaving a bunch out, this:
‘sql code using qdf
Dim strSQL As String
strSQL = _
" myQryDef.Field1, " & _
" myQryDef. Field2, " & _
" myQryDef. Field3, " & _
" myQryDef. Field4, " & _
" myTable.Field1, " & _
" myTable. Field2, " & _
" myTable. Field3, " & _
" myTable. Field4 "

is not a complete SQL statement. Until you put "myQryDef" in a From clause
(which doesn't appear to exist) it won't "recognize" the query.

If this is not the full code, please post it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Query Def not recognized

Hello,
Using Access ’03…

I’ve created a qdf in VBA and I’d like to use it as part of another
query…

My problem, is that the qdf is not being recognized; i.e., when the
query runs, I get a parameter box asking for a value?

Here’s some of my code:
‘’’’’’’’’’’’’’’’
Set dbs = CurrentDb
Dim strQDFname As String
strQDFname = "myQryDef" 'query def name

With dbs
Set qdf = .CreateQueryDef(strQDFname, strQDFsql)
qdf.Close
End With
Set qdf = Nothing

‘sql code using qdf
Dim strSQL As String
strSQL = _
" myQryDef.Field1, " & _
" myQryDef. Field2, " & _
" myQryDef. Field3, " & _
" myQryDef. Field4, " & _
" myTable.Field1, " & _
" myTable. Field2, " & _
" myTable. Field3, " & _
" myTable. Field4 "
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
I can see the query object (myQryDef) and I can use it with other
query objects (I can even run it by just clicking on it); I just can’t
use it w/ VBA for some reason? Do I need to append it to some
collection?

Any thoughts?
alex
 
A

alex

Unless you're leaving a bunch out, this:
‘sql code using qdf
Dim strSQL As String
    strSQL = _
    " myQryDef.Field1, " & _
    " myQryDef. Field2, " & _
    " myQryDef. Field3, " & _
    " myQryDef. Field4, " & _
    " myTable.Field1, " & _
    " myTable. Field2, " & _
    " myTable. Field3, " & _
    " myTable. Field4 "

is not a complete SQL statement.   Until you put "myQryDef" in a From clause
(which doesn't appear to exist) it won't "recognize" the query.

If this is not the full code, please post it.

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Query Def not recognized

Hello,
Using Access ’03…

I’ve created a qdf in VBA and I’d like to use it as part of another
query…

My problem, is that the qdf is not being recognized; i.e., when the
query runs, I get a parameter box asking for a value?

Here’s some of my code:
‘’’’’’’’’’’’’’’’
Set dbs = CurrentDb
    Dim strQDFname As String
    strQDFname = "myQryDef" 'query def name

    With dbs
        Set qdf = .CreateQueryDef(strQDFname, strQDFsql)
        qdf.Close
    End With
    Set qdf = Nothing

‘sql code using qdf
Dim strSQL As String
    strSQL = _
    " myQryDef.Field1, " & _
    " myQryDef. Field2, " & _
    " myQryDef. Field3, " & _
    " myQryDef. Field4, " & _
    " myTable.Field1, " & _
    " myTable. Field2, " & _
    " myTable. Field3, " & _
    " myTable. Field4 "
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
I can see the query object (myQryDef) and I can use it with other
query objects (I can even run it by just clicking on it); I just can’t
use it w/ VBA for some reason?  Do I need to append it to some
collection?

Any thoughts?
alex

That was it Roger!
I forgot to change the name of the query in the FROM clause.
Thanks for pointing me in the right direction.
alex
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top