Getting record count to an unbound text box on a form. - Access 97

[KMITCH]

Option Compare Database<br>Option Explicit<br>Dim Db As Database<br>Dim Rst As Recordset<br>Private Sub Form_Current()<br>&nbsp;&nbsp;&nbsp;Set Db = CurrentDb<br>&nbsp;&nbsp;&nbsp;Set Rst = Db.OpenRecordset(&quot;SELECT * FROM [tblLogFile]&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Rst.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me!Text0 = Rst.RecordCount<br>&nbsp;&nbsp;&nbsp;Rst.Close<br>&nbsp;&nbsp;&nbsp;Db.Close<br>&nbsp;End Sub<br>----------------------------------------------------------<br>The above code is the only way I can come up with to<br>get a record count to an unbound text box on a form.<br>It works fine but I would like to know if there is a<br>more elegant way to accomplish the same thing without<br>invoking a Database and Recordset object.&nbsp;&nbsp;My desire <br>is to have a summary form which displays record counts<br>from multiple tables/queries.<br><br>Any help is appreciated.<br><br>Thanks,<br>Keith

 

[DreewToo]

Hi Keith,<br><br>I've used the DCount() function successfully to perform this.&nbsp;&nbsp;Setting the bound field to DCount(&quot;[ID]&quot;,&quot;tblLogFile&quot;,&lt;criteria if any&gt accomplishes the task fairly easily.<br><br>HTH,<br>Drew

 

[KMITCH]

Thanks Drew,<br><br>I did run across the DCount function and agree that<br>it is a better solution for the specific example I<br>showed above.&nbsp;&nbsp;My real world problem, however, has<br>a multi table join and I didn't know if DCount would<br>work for more than 1 table at a time.<br><br>More comments welcome.<br><br>Keith

 

[DreewToo]

Sure Keith, <br><br>I left out that piece of your question.&nbsp;&nbsp;Could you create a query/ies that links the tables?&nbsp;&nbsp;Using the DCount function against this works the same way.<br><br>Drew

 

[KMITCH]

Thanks Drew!&nbsp;&nbsp;That is a very workable solution.<br>If there is a downside, it is only that there would<br>be a few extra queries laying around in the .mdb; but<br>I have been willing to make that sacrifice in the name<br>of simplicity on more than one occasion.<br><br>Keith