Not getting any records by setting a record set from a query

%3CLINGO-SUB%20id%3D%22lingo-sub-3344399%22%20slang%3D%22en-US%22%3ENot%20getting%20any%20records%20by%20setting%20a%20record%20set%20from%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344399%22%20slang%3D%22en-US%22%3E%3CP%3Ehas%20anyone%20had%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etrying%20to%20set%20in%20VBA%20code%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edim%20rs%20as%20recordset%3C%2FP%3E%3CP%3Eset%20rs%20%3D%20db.openrecordset(%22SomeQuery%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20query%20has%20criteria%20of%20a%20boolean%20set%20to%20true%20and%20sorted%20to%20Forms!SomeForm!SomeID%3C%2FP%3E%3CP%3Eand%20some%20date%20criteria.%3C%2FP%3E%3CP%3Ethe%20query%20itself%20is%20working%20when%20running%20it%20with%20out%20vba%20but%20i%20cant%20call%20it%20by%20a%20record%20set%20from%20VBA%2C%20it%20is%20showing%20up%20as%20rs%3Dnull%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3344399%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3352804%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20any%20records%20by%20setting%20a%20record%20set%20from%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3352804%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1384941%22%20target%3D%22_blank%22%3E%40AMB_DATABASE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20best%20ways%20to%20actually%20get%20responsive%20help%20is%20to%20be%20verbose%20in%20providing%20information%20and%20context%20about%20a%20problem.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20is%20the%20SQL%20in%20that%20query%3F%20What%20is%20the%20boolean%20criteria%3B%20where%20and%20how%20is%20it%20set%3F%20What%20is%20that%20%22some%20date%20criteria%22%3F%20Where%20is%20it%20set%20and%20how%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EOne%20of%20the%20problems%20with%20calling%20SQL%20from%20VBA%20is%20that%20you%20have%20to%20be%20adamant%20about%20providing%20the%20parameters%20needed.%20i.e.%20make%20them%20explicit%2C%20don't%20assume%20VBA%20will%20find%20them%20in%20the%20SQL%20in%20a%20saved%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20a%20final%20not.%20It%20is%20Good%20Practice%20to%20fully%20reference%20objects%20when%20dimming%20them.%20In%20the%20old%20days%2C%20we%20got%20away%20with%20less%20discipline.%20These%20days%2C%20it%20is%20not%20so%20safe.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI.e.%26nbsp%3B%3CBR%20%2F%3EDim%20rs%20as%20DAO.Recordset%20(or%20if%20you%20are%20actually%20using%20ADO%2C%20use%20that%20explicitly).%3C%2FP%3E%3CP%3EDim%20db%20as%20DAO.Database%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

has anyone had this?

 

trying to set in VBA code the following:

 

dim rs as recordset

set rs = db.openrecordset("SomeQuery")

 

the query has criteria of a boolean set to true and sorted to Forms!SomeForm!SomeID

and some date criteria.

the query itself is working when running it with out vba but i cant call it by a record set from VBA, it is showing up as rs=null

 

1 Reply

@AMB_DATABASE 

 

One of the best ways to actually get responsive help is to be verbose in providing information and context about a problem.

What is the SQL in that query? What is the boolean criteria; where and how is it set? What is that "some date criteria"? Where is it set and how? 

One of the problems with calling SQL from VBA is that you have to be adamant about providing the parameters needed. i.e. make them explicit, don't assume VBA will find them in the SQL in a saved query.

 

On a final note. It is Good Practice to fully reference objects when dimming them. In the old days, we got away with less discipline. These days, it is not so safe.

 

I.e. 
Dim rs as DAO.Recordset (or if you are actually using ADO, use that explicitly).

Dim db as DAO.Database