RecordCount = -1

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
 
K

Ken Snell [MVP]

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.
 
R

Ron Kunce

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.
 
G

Guest

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...
 
G

Guest

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
 
G

Guest

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...
 
K

Ken Snell [MVP]

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...
 
R

Ron Kunce

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.
 
G

Guest

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.
 
K

Ken Snell [MVP]

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...
 
G

Guest

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...
 
K

Ken Snell [MVP]

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...
 
R

Ron Kunce

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Switchboard Wizard Number of Items on Page 7
reference in recordset.findfirst 3
Populate form from list box selection 4
problem with reading recordset 3
Update records in tabled based upon listbox selection/s 4
ADO Connection 2
438 - Object doesn't support this property or method 4
Where would "DoCmd.Close..." go in switchboard's code? 3

Top