Create a variable from a query created in VBA

[kaelcarp]

I would like to create a dynamic query using VBA and use the query's result as a variable. I have done this before, without using dlookup (I think it's too complex for dlookup), but I cannot seem to remember how.

What I'd essentially like to be able to do is create a query:



docmd.runsql("SELECT x from y where z;") [note: actual query will be a lot more complex than that and include subqueries]

...and use the result of that as a variable. I don't want to create permanent queries because I'd like to avoid the clutter that would come from that.

[pbaldy]

You would open a recordset on the SQL and get the value from that. You should know that a saved query gets compiled and will run faster than one created in code. I generally only create SQL in code when the actual structure of it will change based on user input. IMHO, you're trading "clutter" for maintainability. It would be easier to create and maintain a saved query.

[kaelcarp]

I probably will need to be able to have the structure of the query change based on user input. We're still in the planning phase on this and I'm trying to think ahead by asking here, which is why I didn't post in more detail. There will definitely be dynamic elements to the query.

What do you mean by opening a recordset on the SQL? Sorry, I'm not particularly advanced with this. I'm not sure exactly how to do that.

[Rawb]

A Recordset is basically an Array with the Table (or Query) data pre-entered in it.

You'd use a Recordset like this:

  ' Create an empty Recordset that you can use
  Dim rstTableName as Recordset

  ' Grab the results of our Query into the Recordset variable
  Set rstTableName = CurrentDb().OpenRecordset("SELECT x FROM y WHERE [x]='z', dbOpenDynaset)

  ' Set the Form Element Me!Text0 to the contents of Field x in Table y
  Me!Text0 = rstTableName("x") ' Me!Text0 now equals "z"!

If your Table/Query results (the Recordset) have more than just a single Record in them, you can move back and forth through it by using the following builtin Methods:
rstTableName.MoveFirst = Go to the first Record in the Recordset
.MoveLast = Go to the last Record
.MoveNext = Go to the next Record
.MovePrevious = Go to the previous Record
.RecordCount = The number of Records in the Recordset (to be sure this number is accurate, you will want to do a MoveLast before checking this. That will force Access to actually load the whole Recordset)
.EOF = Are you at the end of the Recordset (no more Records!)
.BOF = Are you at the beginning of the Recordset (BEFORE the first record)

[kaelcarp]

Thank you. That was immensely helpful. Much appreciated.