RecordCount = -1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a form that I'm using to call a routine which will query a table
(tblWorkOrders) for the number of records matching a certain type (stTemp).
My code is as follows:

Private Function QueryWorkOrders(ByVal stTemp As String)
On Error GoTo Form_AfterUpdate_Err
Dim con As Object
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrders.wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders.wo] Like ""*" & stTemp & "*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

Every time I get a returned value of -1. I've used Message Boxes to verify
my SQL string and compared it to a stored query which works just fine.
Any help would be greatly appreciated.
jason
 
A value of -1 means that the recordset contains at least one record. To get
the actual record count, you'll need to fill the entire recordset by
performing a "MoveLast" action, and then read the record count.
 
You are setting your function to return a Long Integer in

QueryWorkOrders = rs.RecordCount

However, the default return value is a Boolean, which translates any false
non-zero response to a true -1. You need to change your function
declaration to:

Private Function QueryWorkOrders(ByVal stTemp As String) As Long

To return a true record count.
 
Thanks Ken,
I tried the MoveLast statement as follows:

rs.Open...
rs.MoveLast
QueryWorkOrders = rs.RecordCount...

I receive the following error (from Form_AfterUpdate_Err):

"Error is either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."



Ken Snell said:
A value of -1 means that the recordset contains at least one record. To get
the actual record count, you'll need to fill the entire recordset by
performing a "MoveLast" action, and then read the record count.

--

Ken Snell
<MS ACCESS MVP>



jstroup said:
I've got a form that I'm using to call a routine which will query a table
(tblWorkOrders) for the number of records matching a certain type
(stTemp).
My code is as follows:

Private Function QueryWorkOrders(ByVal stTemp As String)
On Error GoTo Form_AfterUpdate_Err
Dim con As Object
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrders.wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders.wo] Like ""*" & stTemp &
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

Every time I get a returned value of -1. I've used Message Boxes to
verify
my SQL string and compared it to a stored query which works just fine.
Any help would be greatly appreciated.
jason
Click to expand...
Click to expand...
 
Ron,

It tried your suggestion, and I am no longer receiving a -1. Instead, I now
get a 0 when I should be getting a 5.

any ideas? also, see my reply to Ken.

thanks
 
just used the old faithful MsgBox trick...BOF is TRUE but there should be at
least 5 returned values under the specified criteria.

jstroup said:
Thanks Ken,
I tried the MoveLast statement as follows:

rs.Open...
rs.MoveLast
QueryWorkOrders = rs.RecordCount...

I receive the following error (from Form_AfterUpdate_Err):

"Error is either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."



Ken Snell said:
A value of -1 means that the recordset contains at least one record. To get
the actual record count, you'll need to fill the entire recordset by
performing a "MoveLast" action, and then read the record count.

--

Ken Snell
<MS ACCESS MVP>



jstroup said:
I've got a form that I'm using to call a routine which will query a table
(tblWorkOrders) for the number of records matching a certain type
(stTemp).
My code is as follows:

Private Function QueryWorkOrders(ByVal stTemp As String)
On Error GoTo Form_AfterUpdate_Err
Dim con As Object
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrders.wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders.wo] Like ""*" & stTemp &
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

Every time I get a returned value of -1. I've used Message Boxes to
verify
my SQL string and compared it to a stored query which works just fine.
Any help would be greatly appreciated.
jason
Click to expand...
Click to expand...
Click to expand...
 
There is a problem with your SQL statement.

Try this:

stSql = "SELECT [tblWorkOrders].[wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders].[wo] Like ""*" & stTemp &
"*"";"

--

Ken Snell
<MS ACCESS MVP>

jstroup said:
just used the old faithful MsgBox trick...BOF is TRUE but there should be
at
least 5 returned values under the specified criteria.

jstroup said:
Thanks Ken,
I tried the MoveLast statement as follows:

rs.Open...
rs.MoveLast
QueryWorkOrders = rs.RecordCount...

I receive the following error (from Form_AfterUpdate_Err):

"Error is either BOF or EOF is True, or the current record has been
deleted.
Requested operation requires a current record."



Ken Snell said:
A value of -1 means that the recordset contains at least one record. To
get
the actual record count, you'll need to fill the entire recordset by
performing a "MoveLast" action, and then read the record count.

--

Ken Snell
<MS ACCESS MVP>



I've got a form that I'm using to call a routine which will query a
table
(tblWorkOrders) for the number of records matching a certain type
(stTemp).
My code is as follows:

Private Function QueryWorkOrders(ByVal stTemp As String)
On Error GoTo Form_AfterUpdate_Err
Dim con As Object
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrders.wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders.wo] Like ""*" & stTemp
&
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

Every time I get a returned value of -1. I've used Message Boxes to
verify
my SQL string and compared it to a stored query which works just
fine.
Any help would be greatly appreciated.
jason
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I wasn't looking at the recordset's SQL string. Now that you are
returning a Long you need to correct your SQL as in Ken's post. The
recordset SQL you were using was not finding any records, hence the
recordcount was 0.
 
sorry to be such a bother about this, but i feel like i'm really close to
getting it nailed down. per your suggestions, my current code reads as
follows:

Private Function QueryWorkOrders(ByVal stTemp As String) As Long
On Error GoTo Form_AfterUpdate_Err
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders].[wo] Like ""*" & stTemp & "*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
rs.MoveLast
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

This code results in the error "No value given for one or more parameters"
and a RecordCount = 0.
 
You're missing an "s" in this line:
stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"

Change it to this:
stSql = "SELECT [tblWorkOrders].[wo] FROM [tblWorkOrders]"

--

Ken Snell
<MS ACCESS MVP>

jstroup said:
sorry to be such a bother about this, but i feel like i'm really close to
getting it nailed down. per your suggestions, my current code reads as
follows:

Private Function QueryWorkOrders(ByVal stTemp As String) As Long
On Error GoTo Form_AfterUpdate_Err
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders].[wo] Like ""*" & stTemp &
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
rs.MoveFirst
rs.MoveLast
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

This code results in the error "No value given for one or more parameters"
and a RecordCount = 0.

jstroup said:
I've got a form that I'm using to call a routine which will query a table
(tblWorkOrders) for the number of records matching a certain type
(stTemp).
My code is as follows:

Private Function QueryWorkOrders(ByVal stTemp As String)
On Error GoTo Form_AfterUpdate_Err
Dim con As Object
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrders.wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders.wo] Like ""*" & stTemp &
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

Every time I get a returned value of -1. I've used Message Boxes to
verify
my SQL string and compared it to a stored query which works just fine.
Any help would be greatly appreciated.
jason
Click to expand...
Click to expand...
 
that's embarassing...also, I had misplaced the ";" such that it was being
included as part of the "Like" criteria.

but alas, i'm back to the EOF/BOF error and RecordCount = 0.

thank you for your patience.

jason

Ken Snell said:
You're missing an "s" in this line:
stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"

Change it to this:
stSql = "SELECT [tblWorkOrders].[wo] FROM [tblWorkOrders]"

--

Ken Snell
<MS ACCESS MVP>

jstroup said:
sorry to be such a bother about this, but i feel like i'm really close to
getting it nailed down. per your suggestions, my current code reads as
follows:

Private Function QueryWorkOrders(ByVal stTemp As String) As Long
On Error GoTo Form_AfterUpdate_Err
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders].[wo] Like ""*" & stTemp &
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
rs.MoveFirst
rs.MoveLast
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

This code results in the error "No value given for one or more parameters"
and a RecordCount = 0.

jstroup said:
I've got a form that I'm using to call a routine which will query a table
(tblWorkOrders) for the number of records matching a certain type
(stTemp).
My code is as follows:

Private Function QueryWorkOrders(ByVal stTemp As String)
On Error GoTo Form_AfterUpdate_Err
Dim con As Object
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrders.wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders.wo] Like ""*" & stTemp &
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

Every time I get a returned value of -1. I've used Message Boxes to
verify
my SQL string and compared it to a stored query which works just fine.
Any help would be greatly appreciated.
jason
Click to expand...
Click to expand...
Click to expand...
 
Sorry... I so rarely use ADO that I overlooked the fact that it uses a
different wildcard than DAO.

Change * to % in your SQL string.
--

Ken Snell
<MS ACCESS MVP>


jstroup said:
that's embarassing...also, I had misplaced the ";" such that it was being
included as part of the "Like" criteria.

but alas, i'm back to the EOF/BOF error and RecordCount = 0.

thank you for your patience.

jason

Ken Snell said:
You're missing an "s" in this line:
stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"

Change it to this:
stSql = "SELECT [tblWorkOrders].[wo] FROM [tblWorkOrders]"

--

Ken Snell
<MS ACCESS MVP>

jstroup said:
sorry to be such a bother about this, but i feel like i'm really close
to
getting it nailed down. per your suggestions, my current code reads as
follows:

Private Function QueryWorkOrders(ByVal stTemp As String) As Long
On Error GoTo Form_AfterUpdate_Err
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders].[wo] Like ""*" & stTemp &
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
rs.MoveFirst
rs.MoveLast
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

This code results in the error "No value given for one or more
parameters"
and a RecordCount = 0.

:

I've got a form that I'm using to call a routine which will query a
table
(tblWorkOrders) for the number of records matching a certain type
(stTemp).
My code is as follows:

Private Function QueryWorkOrders(ByVal stTemp As String)
On Error GoTo Form_AfterUpdate_Err
Dim con As Object
Dim rs As New ADODB.Recordset
Dim stSql As String
stSql = "SELECT [tblWorkOrders.wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders.wo] Like ""*" & stTemp
&
"*;"""
rs.Open stSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
QueryWorkOrders = rs.RecordCount
Exit Function
Form_AfterUpdate_Err:
MsgBox "Error is " & Err.Description
End Function

Every time I get a returned value of -1. I've used Message Boxes to
verify
my SQL string and compared it to a stored query which works just fine.
Any help would be greatly appreciated.
jason
Click to expand...
Click to expand...
Click to expand...
 
Try changing your sql string from:

stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders].[wo] Like ""*" & stTemp &
"*"";"

To:

stSql = "SELECT [tblWorkOrder].[wo] FROM [tblWorkOrders]"
stSql = stSql & " WHERE [tblWorkOrders].[wo] Like ""%" & stTemp &
"%;"""

reason: you are using an ADO recordset which expects SQL Server type
wildcard characters.

Then change your recordset open to:

rs.Open stSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Reason: I don't really know why, but a dynamic recordset seems to have
trouble getting a record count (forward only won't work either). You can
however substitute "adOpenStatic" with a "adLockReadOnly" for a faster
result since you are not doing anything but returning a record count.

The "rs.MoveFirst" does nothing since the record set opens on the first
record. Delete it.

This should work!
 
Back
Top