Determine a recordcount from a query

  • Thread starter Thread starter Ian Shaw
  • Start date Start date
I

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
 
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
Click to expand...

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)?
 
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

John W. Vinson said:
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
Click to expand...

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)?
Click to expand...
 
Back
Top