Functions

[childlead]

how do you write a function that would read a query, count the number of rows of the query result and then print it on to a list box? i have a list box on my form and i'm trying it get it to read a query and list only the number of rows of the resulting query. thank you for your help. this forum is great!!!

 

[CPUburn]

hi,

a very simple example which should get you a bit further in your quest:


Private Sub Form_Open(Cancel As Integer)
Me!Text0 = lf_query 'I used just a textbox
End Sub


Private Function lf_query() As Long
Dim str_sql$
Dim rcs_a As Recordset

str_sql = "SELECT count(*) as output from tttekst;"
Set rcs_a = CurrentDb.OpenRecordset(str_sql, dbOpenSnapshot)
lf_query = rcs_a!output
rcs_a.Close
Set rcs_a = Nothing
End Function

grtz

CPU-burn

 

[JoeMiller]

Lookup the RecordCount property in a code window, check out the Microsoft Access example which does exactly what you're talking about.

HTH Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

Joe,

Access 2K (ADO) doesn't usually return the correct value with this. I often (always?) get the "-1", indicating that " ... the provider doesn't support ... " MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JoeMiller]

As an ardent Access 97 user, I have yet to fall into any of the issues with ADO vs DAO. So thank you to MichaelRed for pointing that out as it is not knowledge I possess. I will have to remember that though as I have been experimenting with XP and the office should be switching shortly! Joe Miller
joe.miller@flotech.net

 

[genomon]

Try setting the ControlSource property of a text box to
=DCount("[any unique field name]","query name&quot
then use the value returned by the text box to populate the list box.

 

[Databaseguy]

the -1 is returned by the default recordset type. Which is a forward only.

Change the constant to something that supports the recordcount property :

rst.Open "YourTable", CurrentProject.Connection,adOpenKeySet, adLockOptimistic Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Gzep]

Hi All,

DCount was the best answer, but note that the Recordcount
property only shows 0 or <not-zero>, until you do a moveLAST, then it will show the correct number!

Gzep. :