=> Recordset value in ADO

[Rhonda Fischer]

Hello,

I have created a recordset in ADO and would like to perform
two different operations dependant on if the recordset
contains one or greater than one record. However I
seem only to receive the .Recordset value of -1,
disregardless of how many records are contained in the
cursor. How can I get around this?

The code I am using is as follows.

Thank you for any suggestions you might have.

Kind Regards
Rhonda


Sub displayStaffSurname(inSurname As String)
On Error GoTo Err_displayStaffSurname

'Proceedure called from
' => Form: FrmPersonnel
' => CmdBtn: cmdSearch_Click()

'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

'Open the connection
Set cnn = CurrentProject.Connection

'Set up the Command objects's Connection, SQL and
parameter types
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT * FROM tblStaff WHERE
Surname = " & "'" & inSurname & "'"
End With

Set rst = cmd.Execute

If rst.EOF Then
MsgBox "No Matching Surname, Try Agan :-)",
vbOKOnly, "Incorrect Entry"
ElseIf (rst.RecordCount = 1) Then '**** PROBLEM HERE
'display singular record on Main Personnel Form
Forms![frmPersonnel]!txtSurname = Trim(rst!Surname)
Forms![frmPersonnel]!txtFirstName = Trim(rst!
Firstname)
Forms![frmPersonnel]!txtDepot = Trim(rst!Depot)
Forms![frmPersonnel]!txtDepartment = Trim(rst!
Department)
ElseIf (rst.RecordCount > 1) Then '**** PROBLEM HERE
'loop through and populate fields on a different
'form
End If


cnn.Close
Set cmd = Nothing

Exit_displayStaffSurname:
Exit Sub

Err_displayStaffSurname:
MsgBox Err.Description
Resume Exit_displayStaffSurname

End Sub

[Daniel Johnson]

Rhonda,

The RecordCount property returns -1 if the number of records is unknown at
runtime.
Open the RecordSet as a ForwardOnly or Static cursor instead.

rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly
or
rst.Open sSQL, cnn, adOpenForwardOnly, adLockReadOnly

Also, I wouldn't bother using the Command object.

I hope this helps,
Danny.

"Rhonda Fischer" <Rhonda.Fischer@Turners-Distribution.com> wrote in message
news:073401c3479e$5b38cd80$a601280a@phx.gbl...

> Hello,
>
> I have created a recordset in ADO and would like to perform
> two different operations dependant on if the recordset
> contains one or greater than one record. However I
> seem only to receive the .Recordset value of -1,
> disregardless of how many records are contained in the
> cursor. How can I get around this?
>
> The code I am using is as follows.
>
> Thank you for any suggestions you might have.
>
> Kind Regards
> Rhonda
>
>
> Sub displayStaffSurname(inSurname As String)
> On Error GoTo Err_displayStaffSurname
>
> 'Proceedure called from
> ' => Form: FrmPersonnel
> ' => CmdBtn: cmdSearch_Click()
>
> 'Declaration
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim rst As ADODB.Recordset
>
> Set cnn = New ADODB.Connection
> Set cmd = New ADODB.Command
> Set rst = New ADODB.Recordset
>
> 'Open the connection
> Set cnn = CurrentProject.Connection
>
> 'Set up the Command objects's Connection, SQL and
> parameter types
> With cmd
> .ActiveConnection = cnn
> .CommandText = "SELECT * FROM tblStaff WHERE
> Surname = " & "'" & inSurname & "'"
> End With
>
> Set rst = cmd.Execute
>
> If rst.EOF Then
> MsgBox "No Matching Surname, Try Agan :-)",
> vbOKOnly, "Incorrect Entry"
> ElseIf (rst.RecordCount = 1) Then '**** PROBLEM HERE
> 'display singular record on Main Personnel Form
> Forms![frmPersonnel]!txtSurname = Trim(rst!Surname)
> Forms![frmPersonnel]!txtFirstName = Trim(rst!
> Firstname)
> Forms![frmPersonnel]!txtDepot = Trim(rst!Depot)
> Forms![frmPersonnel]!txtDepartment = Trim(rst!
> Department)
> ElseIf (rst.RecordCount > 1) Then '**** PROBLEM HERE
> 'loop through and populate fields on a different
> 'form
> End If
>
>
> cnn.Close
> Set cmd = Nothing
>
> Exit_displayStaffSurname:
> Exit Sub
>
> Err_displayStaffSurname:
> MsgBox Err.Description
> Resume Exit_displayStaffSurname
>
> End Sub

[Bob Barrows]

Daniel Johnson wrote:

> Rhonda,
>
> The RecordCount property returns -1 if the number of records is
> unknown at runtime.
> Open the RecordSet as a ForwardOnly or Static cursor instead.

ForwardOnly returns -1. Since only a single record is retrieved at a time,
the provider will not know the number of records until the last record in
the resultset has been retrieved.

Bob Barrows

[Rhonda Fischer]

Thank you very much it worked, I'm very happy :)

Bestest Regards
Rhonda

>-----Original Message-----
>Daniel Johnson wrote:

>> Rhonda,
>>
>> The RecordCount property returns -1 if the number of

records is

>> unknown at runtime.
>> Open the RecordSet as a ForwardOnly or Static cursor

instead.

>ForwardOnly returns -1. Since only a single record is

retrieved at a time,

>the provider will not know the number of records until

the last record in

>the resultset has been retrieved.
>
>Bob Barrows
>
>
>.
>