using recordsets with Dynamic queries

[MaliciousMike]

Hello,

What would i put "val" equal to if the query will always output 1 result?

for example, if rst!DataColumn was "SerialNumber", and rst!Query was "qrySerialNo"

sql = "SELECT " & rst!DataColumn & " FROM " & rst!Query

Would be

sql = "SELECT SerialNumber FROM qrySerialNo"

and i would put val as = rst!SerialNumber

but how would i put val as that if it varies?

sql = "SELECT " & rst!DataColumn & " FROM " & rst!Query
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)
val = ????

Thanks for any help.

~Mike

 

[LPurvis]

I wasn't exactly clear on what you were wanting at first. (Still might not be ;-)
I assume though - that you're referring to obtaining a value from the second opened recordset?
You could go as simple as
val = rst(0)

Because you've closed the first recordset (by using the same object variable to open the second one) you can't refer to the field by name - unless you used a variable to store it in the mean time.
However, as long as you're only selecting a single field then the above is sufficient (and more efficient).

However - would recomment explicitly closing the first recordset before opening another. Even if bloating isn't a result - it's just good practice.

 

[MaliciousMike]

Ah yes. That would make perfect sense!
Gotta hate mental problems that occur at the end of the shift!

Thanks for that!

Btw, when i right a routine, i always use the same variable to get the recordset and then close it all up at the end.

Cheers.

 

[LPurvis]

Hi. Glad the pieces fit together in the cold light of morning ;-)

However - regarding closing. My point was that you should really Close the object prior to re-using it even during the procedure - not only at the end.

IMO having close like

Set rst = dbs.OpenRecordset(sql1, dbOpenDynaset, dbSeeChanges)
Debug.Print rst(0)
Set rst = dbs.OpenRecordset(sql2, dbOpenDynaset, dbSeeChanges)
Debug.Print rst(0)
is as potentially problematic as not closing up at the end of the procedure.

It's very little effort to have
Set rst = dbs.OpenRecordset(sql1, dbOpenDynaset, dbSeeChanges)
Debug.Print rst(0)
rst.Close
Set rst = dbs.OpenRecordset(sql2, dbOpenDynaset, dbSeeChanges)
Debug.Print rst(0)
instead.