RecordCount = -1

[jstroup]

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

 

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

 

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

 

[jstroup]

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

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

 

[jstroup]

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

 

[jstroup]

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

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

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

 

[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>

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

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

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

 

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

 

[jstroup]

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.

 

[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>

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

 

[jstroup]

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

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>

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

 

[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>

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

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>

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

 

[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!

 

[jstroup]

eureka!!! thanks for everything!!!
jason