Code for counting number of records in query

  • Thread starter Thread starter merry_fay
  • Start date Start date
M

merry_fay

Hi,

I need to count the number of records in queries quite a lot to check for
duplicates or to ensure the data I'm about to append hasn't already been
added & then inform the user. I've been using this code to get the count of
records:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLcnt As String

strSQLcnt = "SELECT Count(…………. etc


Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQLcnt)
xx = rs.Fields(0)
rs.Close: Set rs = Nothing
Set db = Nothing

If xx = 0 Then

etc


Is there a simpler or shorter way of doing this as I'm getting some very
long macros when I have to repeat this for 10 different tables in one macro?

Thanks
merry_fay
 
Hi,

I need to count the number of records in queries quite a lot to check for
duplicates or to ensure the data I'm about to append hasn't already been
added & then inform the user. I've been using this code to get the count of
records:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLcnt As String

strSQLcnt = "SELECT Count(…………. etc

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQLcnt)
    xx = rs.Fields(0)
    rs.Close: Set rs = Nothing
    Set db = Nothing

If xx = 0 Then

etc

Is there a simpler or shorter way of doing this as I'm getting some very
long macros when I have to repeat this for 10 different tables in one macro?

Thanks
merry_fay
Click to expand...

I would create a function and call it ten times:
(Cauton: aircode)


Public Function GetRecCount(strTableName As string) As Long
Dim rst as DAO.Recordset
Set rst = CurrentDB.OpenRecordset(strTableName)
If rst.RecordCount>0 Then
rst.MoveLast
rst.MoveFirst
EndIf
GetRecCount = rst.RecordCount
rst.Close
Set rst = Nothing
End Function



Thus, your procedure code would look something like:

.....other code
xx1 = GetRecCount("tbl1")
xx2 = GetRecCount("tbl2")
xx3 = GetRecCount("tbl3")
xx4 = GetRecCount("tbl4")
xx5 = GetRecCount("tbl5")
xx6 = GetRecCount("tbl6")
xx7 = GetRecCount("tbl7")
xx8 = GetRecCount("tbl8")
xx9 = GetRecCount("tbl9")
....other code

Good Luck,
Fred
 
Assuming this code is in it's own sub then pass in the
tablename as a string when calling it and then concatenate
into the select string strSQLcnt:

Sub sCountRows (strTableName as String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLcnt As String

'Change this:
'> strSQLcnt = "SELECT Count(..... etc
' To this:
strSQLcnt = "SELECT COUNT blah blah blah FROM" & " " &
strTableName & ";"


etc etc

End Sub

Then call the procedure with the appropriate tablename each
time you need to query a table.

sCountRows "tblCustDetail"

In the long run you'll find it beneficial to try and use
code to run/call a procedures and what not rather than
macro's.

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In merry_fay typed:
 
was wollten sie eigentlich von mir bitte nicht jetzt als Quelltext an mich
zuschicken
 
bitte bearbeiten und fertig machen
Nick Coe (UK) said:
Assuming this code is in it's own sub then pass in the tablename as a
string when calling it and then concatenate into the select string
strSQLcnt:

Sub sCountRows (strTableName as String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLcnt As String

'Change this:
'> strSQLcnt = "SELECT Count(..... etc
' To this:
strSQLcnt = "SELECT COUNT blah blah blah FROM" & " " & strTableName & ";"


etc etc

End Sub

Then call the procedure with the appropriate tablename each time you need
to query a table.

sCountRows "tblCustDetail"

In the long run you'll find it beneficial to try and use code to run/call
a procedures and what not rather than macro's.

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In merry_fay typed:
Click to expand...
 
You can shorten that using

Dim db As DAO.Database
Set db= Currentdb()
xx = DB.OpenRecordSet(strSQLCount).Fields(0)

Or even shorter (not necessarily more efficient if you are doing this multiple
times in one procedure)

xx = CurrentDb().OpenRecordSet(strSQLCount).Fields(0)

Depending on what you are doing, you might be able to use the DCount function.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
bitte bearbeiten und fertig machen
Click to expand...

Dies ist eine "Newsgruppe" uber die programme Microsoft Access. Wenn Sie
wollen, machen Sie ein Post mit sein Frage (auf Englisch), oder fragen auf
Deutsch an microsoft.public.de.access.
 
You can shorten that using

Dim db As DAO.Database
Set db= Currentdb()
xx = DB.OpenRecordSet(strSQLCount).Fields(0)

Or even shorter (not necessarily more efficient if you are doing
this multiple times in one procedure)

xx = CurrentDb().OpenRecordSet(strSQLCount).Fields(0)
Click to expand...

Or shorter still, since the .Fields is the default collection of a
recordset:

xx = CurrentDb().OpenRecordSet(strSQLCount)(0)

The first time I ran across this (just in the last year!), it
completely mystified me. Now I use it in code *all* the time in
place of DCount() or Trevor Best's tCount() replacement for it.
 
Back
Top