SQL statement in VBA that refers to other queries

[RCurtin]

Hi,
I have the following code in a module - Im creating a recordset in ADO.

The sql code is fine - I've tested it in a query but I'm getting the "Jet cannot find your query or table" error. This is because the sql refers to two other queries:NewestTransmittal and LatestRevTransmitted. How can I get this to work in VBA? I tried using querydefs but couldn't get it to work.

Any ideas?

sqlTransmittals = "SELECT tblOutTransmittals.DocID, tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum " & _
                "FROM LatestRevTransmitted INNER JOIN ((NewestTransmittal INNER JOIN tblOutTransmittals ON (NewestTransmittal.CCNum = tblOutTransmittals.CCNum) AND (NewestTransmittal.MaxOfTransmittalDate = tblOutTransmittals.TransmittalDate)) " & _
                "INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID) ON LatestRevTransmitted.MaxOfRevisionNum = tblTransmittedDocs.RevisionNum " & _
                "WHERE (((tblOutTransmittals.CCNum)=" & rsRecipients!CCNum & ") AND ((tblTransmittedDocs.DrawingNum)='" & DrawingNum & "') AND ((tblTransmittedDocs.RevisionNum)=" & Rev & "));"
    

            transmittalRecords.Open sqlTransmittals, conn, adOpenForwardOnly, adLockOptimistic, adCmdText

 

[RCurtin]

Any ideas?

 

[namliam]

Why use ADO at all? And not DAO? Much easier...

What is your connection (conn)?

the queries run by them selves, right?

 

[RCurtin]

Thanks so much for the reply - have been tring to get this work for ages.

To be honest I didn't even know about DAO when I started this and its quite a big project so it would take alot of work to change everything to DAO now.

The reason I'm using a recordset is that I need to get the outTransmittalID of all records returned. This code worked perfectly when I had a simpler SQL statement but I had to change the SQL because I want to return the newest transmittal issued. As a result the query refers to two other queries - NewestTransmittal and LatestRevTransmitted

When I run the code I get an error saying that that it cannot find the query LatestRevTransmitted. These were created as regular queries and are saved with the names specified in this query. How do I get it to 'see' these queries?

            transmittalRecords.Open sqlTransmittals, conn, adOpenForwardOnly, adLockOptimistic, adCmdText
            
            With transmittalRecords
                newTransID = createTransmittal(!outTransmittalID, DrawingNum, Rev)
                numTransmittals = numTransmittals + 1
                newTransmittalList = newTransmittalList & " OR [DocID]=" & newTransID & ""
                .MoveNext
            End With
            transmittalRecords.Close
            
            rsRecipients.MoveNext

 

[namliam]

I allways use DAO, so I dont think I can really help you. There should be no problem in having a recordset query like this use 'normal' queries.

The only thing I am guessing is it has something to do with:
1) ADO
2) The connection needed for ADO

 

[RCurtin]

Didn't realise that the type of connection (ADO V DAO) could be causing the problem. I changed it to DAO and its working now:

Set qdfTransmittal = dbs.QueryDefs("qselRecentTransmittal")     'most recent transmittals sent with
            qdfTransmittal.Parameters("DrawNum") = DrawingNum               'where current drawing appeared
            qdfTransmittal.Parameters("CCNum") = rsRecipients!CCNum         'to each distinct recipient

Thanks for your help,
RCurtin

 

[namliam]

transmittalRecords.Open sqlTransmittals, conn, adOpenForwardOnly, adLockOptimistic, adCmdText

Not the ADO vs DAO thing, but the CONN part in your statement is probably the problem... But like I said, I aint no ADO man...