Edward S Zuris
edzedz at comcast.net
Wed May 7 09:40:47 CDT 2008
This works with empty tables too.
Debug.Print zzzTest04(1), zzzTest04(2)
0 138
Function zzzTest04(zflag As Long) As Long
' ***************************************************
' simple record count
' ***************************************************
Dim lRcdCount As Long
Dim sSQL As String
Dim dbsV2H As DAO.Database
Dim rsPull As DAO.Recordset
DBEngine.SetOption dbMaxLocksPerFile, 5752048
Set dbsV2H = CurrentDb()
zzzTest04 = 0
sSQL = ""
sSQL = sSQL & " SELECT "
sSQL = sSQL & " * "
sSQL = sSQL & " FROM "
If zflag = 2 Then
sSQL = sSQL & " JP02Boiler "
Else
sSQL = sSQL & " JP03Req "
End If
sSQL = sSQL & " ; "
Set rsPull = dbsV2H.OpenRecordset(sSQL)
On Error Resume Next
lRcdCount = 0
rsPull.MoveLast
lRcdCount = rsPull.RecordCount
rsPull.MoveFirst
On Error GoTo 0
zzzTest04 = lRcdCount
On Error Resume Next
rsPull.Close
dbsV2H.Close
On Error GoTo 0
End Function
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Steve Zayko
Sent: Wednesday, May 07, 2008 6:07 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Simple Recordset Question
Gustav:
Will this work if there are no records in the table?
99% of the time there will be no records in the Matrix table. It is
being used as a dump for non-valid items. Thus if there are no records
life is good and the code goes on. If there are records then the code
fixes the issues and then moves on.
Thanks for everyone's help.
-Z
Stephen Zayko, PE
Associate Engineer
Stantec
2321 Club Meridian Drive Suite E
Okemos MI 48864
Ph: (517) 349-9499 Ext. 224
Fx: (517) 349-6863
Cell: (517) 204-5136
steve.zayko at stantec.com
www.stantec.com
The content of this email is the confidential property of Stantec and
should not be copied, modified, retransmitted, or used for any purpose
except with Stantec's written authorization. If you are not the intended
recipient, please delete all copies and notify us immediately.
Please consider the environment before printing this email.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, May 07, 2008 4:54 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Simple Recordset Question
Hi Doug and Steve
You don't even need "somefield":
lngCount = DCount("*", "Matrix")
/gustav
>>> dw-murphy at cox.net 07-05-2008 04:51 >>>
Why not just use dcount("somefield","Matrix")? One line.
Doug
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Zayko
Sent: Tuesday, May 06, 2008 3:10 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Simple Recordset Question
OK, I have a first grader level question for you all.
I have an access 2003 database and a form. When I click a button on
said
form I would like the code to open a table as a recordset and count the
records. I know I have done this before and I know I have seen this
done
quite easily. However, I am getting an error that I cannot get past.
This is my code:
Dim rst_Matrix As Recordset
Dim int_X as integer
Set rst_Matrix = CurrentDb.OpenRecordset("SELECT Matrix.* from Matrix;",
_
DbOpenDynaset, dbReadOnly)
rst_Matrix.MoveLast
int_X = rst_Matrix.RecordCount
On line 3 I am getting a type mis-match.
What am I doing wrong and how do I get my code to get past this?
Thanks
-Z
Stephen Zayko, PE
Associate Engineer
Stantec
2321 Club Meridian Drive Suite E
Okemos MI 48864
Ph: (517) 349-9499 Ext. 224
Fx: (517) 349-6863
Cell: (517) 204-5136
steve.zayko at stantec.com
www.stantec.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com