Determine a recordcount from a query

[Ian Shaw]

Hi Guys,

Please help me!! I am trying to determine the record count of a query using
the following code: I know that it needs some help!

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim str As String
Dim chk As Single

chk = [Forms]![ReportSelectorForm]![Combo7]

str = "SELECT EmployeeTraining.LicenceID, EmployeeTraining.EmployeeID,
EmployeeTraining.[Date Completed], EmployeeTraining.[Expiry Date] " & _
"FROM EmployeeTraining " & _
"WHERE (((EmployeeTraining.LicenceID)= " & chk & ")); "

Set db = DBEngine(0)(0)
Set qdf = db.CreateQueryDef("", str)
rs = qdf.OpenRecordset

If Not qdf.EOF Then
rs.MoveLast
Debug.Print rs.RecordCount
Else
Debug.Print "No records retured"
End If

Unfortunately for me I do not know how to fix it!! I have tried every
permuation that I can think of and to no avail!

Any assistance would be inavaluable!!

Cheers

 

[John W. Vinson]

Hi Guys,

Please help me!! I am trying to determine the record count of a query using
the following code: I know that it needs some help!

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim str As String
Dim chk As Single

chk = [Forms]![ReportSelectorForm]![Combo7]

str = "SELECT EmployeeTraining.LicenceID, EmployeeTraining.EmployeeID,
EmployeeTraining.[Date Completed], EmployeeTraining.[Expiry Date] " & _
"FROM EmployeeTraining " & _
"WHERE (((EmployeeTraining.LicenceID)= " & chk & ")); "

Set db = DBEngine(0)(0)
Set qdf = db.CreateQueryDef("", str)
rs = qdf.OpenRecordset

If Not qdf.EOF Then
rs.MoveLast
Debug.Print rs.RecordCount
Else
Debug.Print "No records retured"
End If

Unfortunately for me I do not know how to fix it!! I have tried every
permuation that I can think of and to no avail!

Any assistance would be inavaluable!!

Cheers

So... what's going wrong? What needs to be fixed?

And why not simply use

DCount("*", "[EmployeeTraining]", "[LicenseID]=" & chk)

(unless you proceed to do something else with the recordset of course)?

 

[Ian Shaw]

Thanx John, I have just found the error of my ways in fact!! Its amazing
how a break can clear your mind when you hva e been working on something for
a while!!

Heres the changes I made to fix the bloody code!!

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim str As String
Dim chk As Single

chk = [Forms]![ReportSelectorForm]![Combo7]

str = "SELECT EmployeeTraining.LicenceID, EmployeeTraining.EmployeeID,
EmployeeTraining.[Date Completed], EmployeeTraining.[Expiry Date] " & _
"FROM EmployeeTraining " & _
"WHERE (((EmployeeTraining.LicenceID)= " & chk & ")); "

Set db = DBEngine(0)(0)
Set qdf = db.CreateQueryDef("", str)
SET rs = qdf.OpenRecordset 'error
is SET

If Not rs.EOF Then
'error is rs.eof vice qdf.eof
rs.MoveLast
MsgBox rs.RecordCount
Else
MsgBox "No records retured"
End If
rs.Close
qdf.Close
Set rs = Nothing
Set qdf = Nothing

Hi Guys,

Please help me!! I am trying to determine the record count of a query using
the following code: I know that it needs some help!

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim str As String
Dim chk As Single

chk = [Forms]![ReportSelectorForm]![Combo7]

str = "SELECT EmployeeTraining.LicenceID, EmployeeTraining.EmployeeID,
EmployeeTraining.[Date Completed], EmployeeTraining.[Expiry Date] " & _
"FROM EmployeeTraining " & _
"WHERE (((EmployeeTraining.LicenceID)= " & chk & ")); "

Set db = DBEngine(0)(0)
Set qdf = db.CreateQueryDef("", str)
rs = qdf.OpenRecordset

If Not qdf.EOF Then
rs.MoveLast
Debug.Print rs.RecordCount
Else
Debug.Print "No records retured"
End If

Unfortunately for me I do not know how to fix it!! I have tried every
permuation that I can think of and to no avail!

Any assistance would be inavaluable!!

Cheers

So... what's going wrong? What needs to be fixed?

And why not simply use

DCount("*", "[EmployeeTraining]", "[LicenseID]=" & chk)

(unless you proceed to do something else with the recordset of course)?