Access saved query versus VBA query problem

[adamcbest]

Hello everyone,
I'm new to Tek Tips. I'm having a problem with a VBA query in Access. FIrst off, I wrote a query in design view and saved it to make sure it worked. This query pulled all the info that it shoudl have pulled. However, when i go into the code behind of a button and try to open this query. It comes up with no records. I tried it by using the one i had saved and by putting the SQL code into a string variable and running it against the string. Either way it finds no records. Here is my recordset declaration and open line:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

 

[xlbo]

and where is strSQL ???

 

[adamcbest]

declared above those lines:

Dim strSQL As String

strSQL = "SELECT ApprVendorDetail.VendName as name, ApprVendorDetail.VendEmail as email, ApprSvcs.EntryDateStamp as entry, ApprSvcs.ApprStatus as status, PropDetail.PropAddr as addr, PropDetail.PropCity as city, PropDetail.PropState as state, PropDetail.PropZip as zip, PropDetail.PropCounty as county FROM PropDetail INNER JOIN (ApprVendorDetail INNER JOIN ApprSvcs ON ApprVendorDetail.VendID = ApprSvcs.VendID) ON PropDetail.PropID = ApprSvcs.PropID WHERE (((ApprVendorDetail.VendEmail) Is Not Null) And ((ApprSvcs.ApprStatus) Like '06*' Or (ApprSvcs.ApprStatus) Like '16*' Or (ApprSvcs.ApprStatus) Like '17*' Or (ApprSvcs.ApprStatus) Like '25*' Or (ApprSvcs.ApprStatus) Like '26*' Or (ApprSvcs.ApprStatus) Like '33*' Or (ApprSvcs.ApprStatus) Like '02*')) ORDER BY ApprSvcs.EntryDateStamp DESC;"

this is the exact same sql code that is in my saved query that works. so i don't think the SQL is the problem.. i can only imagine it's some parameter in my rs.open line. But this is stuff i haven't done for a long time, so maybe i'm off somewhere

 

[xlbo]

Have you tried opening the connection before using it in the recordset ??

 

[adamcbest]

i haven't, but i've done it several times before without any complications, but with more simple SQL code.
on the other pages that i have used this i have had this code and have had it work:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim strSQL As String
strSQL = "Whatever my SELECT statement is"

rs.Open strSQL, CurrentProject.Connection, OpenStatic, adLockOptimistic

 

[xlbo]

kinda out of ideas then. Have you tried posting in one of the Access specific forums ?? Think there's one for queries and 1 for VBA so could probably ask in both

 

[adamcbest]

ok thank you