Re-Use recordset 1

[solo7]

I am using the following code sucessfully to retun the number of records in a SQL statement.

Dim StrSql As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic

'---------- Get count for Module 1 waiting
StrSql = "SELECT TblMod1.Fld_Mod1_Index, TblMod1.Fld_Mod1TestResult FROM TblMod1 WHERE (((TblMod1.Fld_Mod1TestResult)=""""));"
rst.source = StrSql
rst.Open

Txt_Mod1Waiting.Value = rst.RecordCount & " waiting"
rst.Close
Set rst = Nothing

but I would like to use the same procedure on several tables. Can I use my Recordset in a loop for example and re-set the SQL to populate another text box with a new value. So if I have 7 tables (TblMod1 - TblMod7) just change this value in each SQL and point it to Txt_Mod1Waiting.Value - Txt_Mod7Waiting.Value.

 

[traingamer]

Yes you can.

But why not Select Count(*) as TotRecs from tblMod1... which should return a single row rather than waiting for the connection to return a (possibly large) array?

traingamer

 

[traingamer]

or, better yet, have seven unbound controls with the Row Source Type set to Table/Query and the Row Source set to a query like Select Count(*) as TotRecs from tblMod1 where ...

traingamer

 

[solo7]

Sounds great traingamer ,
But it's the first time I've come across the Select Count(*).
So if I was to use the following SQL:

SELECT TblMod2.Fld_Mod2_Index, TblMod2.Fld_Mod1TestResult FROM Tb2Mod21 WHERE (((TblMod2.Fld_Mod2TestResult)=""""));

as a control source in a text box, How would I incorperate the Select Count(*) ??

solo7

 

[traingamer]

If you need the other data (Fld_Mod2_Index, Fld_Mod1TestResult) my idea is probably not a good one for you. If you only need the number of records, just:

SELECT Count(TblMod2.Fld_Mod2_Index) as TotRecs FROM Tb2Mod21 WHERE (((TblMod2.Fld_Mod2TestResult)=""""));

If you know this field (Fld_Mod2_Index) is not null it can be faster to just select that rather than *.

traingamer

 

[PHV]

You may also consider the DCount function:
=DCount("*", "TblMod1", "Fld_Mod1TestResult=''")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[solo7]

Top marks PHV

the simple solutions are so humbling ...

Thanks for that, the DCount was just what I was looking for I was just looking too deep

solo7