Error number 91: Object varible / with block not set

[Lee Taylor-Vaughan]

Hello group,

I have the following code that is giving me error number 91. (i am trying to
count records in a list box with this code); I have the read the ms access
help on it, but it doesnt make sense to me. I have tried adding a break
point and hovering the mouse of ther (me.list74.rowsource) and intellesense
gives the sql statement (or part of it i.e. "SELECT firstname, last
name ......."). (i am wondering how long an SQL statement can be for its
declaration?)

can you please take a quick peek at this code and advise why it is not
working? as I have gotten tired of banging my head against the wall with it
and feel it is time to ask for some help.

Private Sub CountRecords()
On Error GoTo Err_Clear_Click

Dim lngCount As Integer
Dim dbCurrent As DAO.Database
Dim rsData As DAO.Recordset
Set dbCurrent = CurrentDb
Set rsData = Nothing
Set rsData = CurrentDb.OpenRecordset(Me.List74.RowSource)
lngCount = 0


rsData.MoveLast
rsData.MoveFirst

Do While rsData.EOF = False
rsData.MoveNext
lngCount = lngCount + 1
Loop





Exit_Clear_Click:
Forms!frmUserSearch.Caption = "User Search: Total Records: " &
Nz(lngCount)
Exit Sub

Err_Clear_Click:
' Debug.Print rsData

Select Case Err.Number
Case Is = 3021 'no records in set
MsgBox "There are no Users for your selection", vbCritical, "No
Records to display"
Me.List74.RowSource = ""
Forms!frmUserSearch.Caption = "User Search: Total Records: " &
Nz(lngCount)
Exit Sub

Case 3078 'no records in database
MsgBox "There are no records entered into the database.",
vbInformation, "No Data to display"
Forms!frmUserSearch.Caption = "User Search: Total Records: " &
Nz(lngCount)

Exit Sub

Case Else
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error
Message, gulp...!"
Forms!frmUserSearch.Caption = "User Search: Total Records: " &
Nz(lngCount)

Exit Sub

End Select

Set dbCurrent = Nothing
Set rsData = Nothing

End Sub

thanks

Lee

 

[Dirk Goldgar]

Hello group,

I have the following code that is giving me error number 91. (i am
trying to count records in a list box with this code); I have the
read the ms access help on it, but it doesnt make sense to me. I have
tried adding a break point and hovering the mouse of ther
(me.list74.rowsource) and intellesense gives the sql statement (or
part of it i.e. "SELECT firstname, last name ......."). (i am
wondering how long an SQL statement can be for its declaration?)

can you please take a quick peek at this code and advise why it is not
working? as I have gotten tired of banging my head against the wall
with it and feel it is time to ask for some help.

Private Sub CountRecords()
On Error GoTo Err_Clear_Click

Dim lngCount As Integer
Dim dbCurrent As DAO.Database
Dim rsData As DAO.Recordset
Set dbCurrent = CurrentDb
Set rsData = Nothing
Set rsData = CurrentDb.OpenRecordset(Me.List74.RowSource)
lngCount = 0


rsData.MoveLast
rsData.MoveFirst

Do While rsData.EOF = False
rsData.MoveNext
lngCount = lngCount + 1
Loop





Exit_Clear_Click:
Forms!frmUserSearch.Caption = "User Search: Total Records: " &
Nz(lngCount)
Exit Sub

Err_Clear_Click:
' Debug.Print rsData

Select Case Err.Number
Case Is = 3021 'no records in set
MsgBox "There are no Users for your selection", vbCritical,
"No Records to display"
Me.List74.RowSource = ""
Forms!frmUserSearch.Caption = "User Search: Total Records:
" & Nz(lngCount)
Exit Sub

Case 3078 'no records in database
MsgBox "There are no records entered into the database.",
vbInformation, "No Data to display"
Forms!frmUserSearch.Caption = "User Search: Total Records:
" & Nz(lngCount)

Exit Sub

Case Else
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error
Message, gulp...!"
Forms!frmUserSearch.Caption = "User Search: Total Records:
" & Nz(lngCount)

Exit Sub

End Select

Set dbCurrent = Nothing
Set rsData = Nothing

End Sub

thanks

Lee

What line is giving you the error? It's not obvious to me why you'd get
that error, but knowing which line raises the error would help narrow it
down -- not the line in the error-handler that displays the error, but
the line in the body of the routine that causes the error and jumps to
the error-handler.

All that aside, I fail to see why you would need all this elaborate
code. The list box has a ListCount property; why not just interrogate
it to find out how many records are in the list?

Where is this function located? Is it on the same form with the list
box? Is that the same as "frmUserSearch"?

 

[Lee Taylor-Vaughan]

It gives the error on

Set rsData = Currentdb.openRecordset(me.list74.rowsource)

I was told that this type of code is necessary in counting records as the
..count property is not always reliable? is this true?

Thanks

Lee

 

[Lee Taylor-Vaughan]

Which is better, ADODB or DOA?

What line is giving you the error? It's not obvious to me why you'd get
that error, but knowing which line raises the error would help narrow it
down -- not the line in the error-handler that displays the error, but
the line in the body of the routine that causes the error and jumps to
the error-handler.

All that aside, I fail to see why you would need all this elaborate
code. The list box has a ListCount property; why not just interrogate
it to find out how many records are in the list?

Where is this function located? Is it on the same form with the list
box? Is that the same as "frmUserSearch"?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

It gives the error on

Set rsData = Currentdb.openRecordset(me.list74.rowsource)

In what event are you executing the code? I seem to recall reading that
combo and list boxes aren't always fully instantiated in the form's Open
event. If the code is being called from the Open event, you might try
moving it to the Load event, or even (if that fails and it's practical
to do so) to the Load event.

I was told that this type of code is necessary in counting records as
the .count property is not always reliable? is this true?

I assume you mean the ListCount property, which is what I suggested.
The only circumstance I'm *aware* of in which the ListCount property
would be incorrect is the case in which there are more records returned
by the rowsource query than the maximum supported in a list box:
65,536, IIRC. I'm not sure what the ListCount property would say then.

I suppose it's also possible that you might have delayed loading of the
combo box if the rowsource is a linked ODBC table, or if you do this in
an ADP. I have no information about that, though. What exactly were
you told about the ListCount property not being reliable?

 

[Dirk Goldgar]

Which is better, ADODB or DOA?

For what purpose? DAO is more efficient and more flexible for working
with Jet databases. ADO may be better for ODBC databases, but if you're
working in a Jet database with linked ODBC tables, I think (but can't
prove) that DAO will often come out ahead for those cases where it can
easily be applied.

 

[Lee Taylor-Vaughan]

In what event are you executing the code? I seem to recall reading that
combo and list boxes aren't always fully instantiated in the form's Open
event. If the code is being called from the Open event, you might try
moving it to the Load event, or even (if that fails and it's practical
to do so) to the Load event.

on the 'on-click' event of a command button. --the form and list box are
unbound upon loading the form, or opening the form--only populated when the
search button is clicked. This code is exucuted after the search button is
clicked -i.e. after all other code for this event as Call CountRecords
(this is the last line of code in this procedure).

I assume you mean the ListCount property, which is what I suggested.
The only circumstance I'm *aware* of in which the ListCount property
would be incorrect is the case in which there are more records returned
by the rowsource query than the maximum supported in a list box:
65,536, IIRC. I'm not sure what the ListCount property would say then.

I think this is what i heard about this... i dont fully recall. Although I
know that the table (SQL Server via ODBC) will have many more records than
65,536, and problably close to a million records.

I suppose it's also possible that you might have delayed loading of the
combo box if the rowsource is a linked ODBC table, or if you do this in
an ADP. I have no information about that, though. What exactly were
you told about the ListCount property not being reliable?

see above.

 

[Lee Taylor-Vaughan]

My db is ODBC (linked to SQL server). Using access 2003 ADP. Where can i
find more info on using DOA / ADOBD?

 

[Dirk Goldgar]

My db is ODBC (linked to SQL server). Using access 2003 ADP.

Oh my, you should have said so! That explains why your code didn't
work. The CurrentDb method returns a reference to a copy of the Access
application's current Jet database. In an ADP, there *is* no Jet
database, so CurrentDb always returns Nothing. Hence your "Object
variable not set" error -- that's what happens when you try to use an
object variable or reference that is currently Nothing. The fact that
you were using DAO code led me to believe you were working in an MDB
file, so this possibility didn't even occur to me.

In your current situation you should do something like this:

Dim rsData As ADODB.Recordset
Dim lngCount As Long

Set rsData = New ADODB.Recordset
With rsData
Set .ActiveConnection = CurrentProject.Connection
.Open "SELECT COUNT(*) FROM (" & Me.List74.RowSource & ")"
lngCount = .Fields(0)
.Close
End With
Set rsData = Nothing

 

[Dirk Goldgar]

on the 'on-click' event of a command button. --the form and list box
are unbound upon loading the form, or opening the form--only
populated when the search button is clicked. This code is exucuted
after the search button is clicked -i.e. after all other code for
this event as Call CountRecords (this is the last line of code in
this procedure).

That doesn't look like the problem, then. And in fact I believe I've
found the problem. See my answer in the other subthread.

I think this is what i heard about this... i dont fully recall.
Although I know that the table (SQL Server via ODBC) will have many
more records than 65,536, and problably close to a million records.

Maybe. But if you were planning to show even 65,536 records to your
users in a list box, you were really building an unworkable interface.
Especially in a client-server application, you want to whittle down the
list of records a lot farther than that before you go hauling them
across the network.

 

[Lee Taylor-Vaughan]

That doesn't look like the problem, then. And in fact I believe I've
found the problem. See my answer in the other subthread.


Maybe. But if you were planning to show even 65,536 records to your
users in a list box, you were really building an unworkable interface.
Especially in a client-server application, you want to whittle down the
list of records a lot farther than that before you go hauling them
across the network.

Thank you for you input here. I suspected that this would be very
unworkable. This list box is on a form that the user enters information to
return search results. Is there code that i can use to require the user to
enter more information before the list box is populated with too many
records? i.e. if the user enters the last name of smith and there are
500,000 records, how would access (adp) know this before hauling the data
over the network?

 

[Lee Taylor-Vaughan]

Oh my, you should have said so! That explains why your code didn't
work. The CurrentDb method returns a reference to a copy of the Access
application's current Jet database. In an ADP, there *is* no Jet
database, so CurrentDb always returns Nothing. Hence your "Object
variable not set" error -- that's what happens when you try to use an
object variable or reference that is currently Nothing. The fact that
you were using DAO code led me to believe you were working in an MDB
file, so this possibility didn't even occur to me.

In your current situation you should do something like this:

Dim rsData As ADODB.Recordset
Dim lngCount As Long

Set rsData = New ADODB.Recordset
With rsData
Set .ActiveConnection = CurrentProject.Connection
.Open "SELECT COUNT(*) FROM (" & Me.List74.RowSource & ")"

I am getting an error on this line of code.. it appears that it is trying to
combine 2 SQL statements together. the first error states. (upon form being
opened) Line 1: "incorrect syntax near ')'.
when the list box is populated (it is not populated when the form is loaded,
only after user fills out info in unbound controls i.e. fname lname etc...)
i get this error: "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified"
What does this mean? is it because the code is tryin to put 2 sql statements
together, how do i get around it? How do i use a list boxes .count feature?
(this seems easier at this point, although the code way is more
educational!)

 

[Brendan Reynolds]

If I'm right in thinking that the RowSource of the list box control is a SQL
statement that uses the value entered in a text box to filter records, then
you can use the text box directly and avoid the reference to the RowSource
(which is the source of the problem here). Something like ...

..Open "SELECT COUNT(*) FROM YourTableName WHERE YourFieldName = '" &
Me!YourTextBox & "'"

I'm assuming here that the field in question is a text field, hence the
single quotes around the value of the text box. Adjust as necessary if the
field is a number or date/time field.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dirk Goldgar]

I am getting an error on this line of code.. it appears that it is
trying to combine 2 SQL statements together. the first error states.
(upon form being opened) Line 1: "incorrect syntax near ')'.

Were you getting that error before? I don't see offhand how the code I
suggested caused that particular error. Are you sure this error was on
that specific line? I didn't think that section of code was even being
executed when the form is first opened.

when the list box is populated (it is not populated when the form is
loaded, only after user fills out info in unbound controls i.e. fname
lname etc...) i get this error: "The ORDER BY clause is invalid in
views, inline functions, derived tables, and subqueries, unless TOP
is also specified" What does this mean? is it because the code is
tryin to put 2 sql statements together, how do i get around it? How
do i use a list boxes .count feature? (this seems easier at this
point, although the code way is more educational!)

<g> Let's go on with the code approach for a bit. I really think it
will be more profitable in the long run.

I see that Brendan is assuming you have a control reference in the list
box's rowsource. If so, his approach is the best way to go about it and
my way won't work without more complex code. I'm assuming, to the
contrary, that you are actually rewriting the listbox's rowsource in the
AfterUpdate event of a text box on the form, so that the control
reference won't present a problem. More information on this point would
settle the matter.

We can fix the code I suggested, at least as far as the second error you
mentioned is concerned, by modifying the rowsource's SQL statement a bit
more before opening it. Try this:

Dim rsData As ADODB.Recordset
Dim lngCount As Long
Dim strSQL As String

strSQL = Trim(Me.List74.RowSource)

' Don't proceed if rowsource is blank.
If Len(strSQL) = 0 Then Exit Sub

If Left(strSQL, 7) = "SELECT " Then
strSQL = "SELECT TOP 100 PERCENT " & Mid(strSQL, 8)
End If

strSQL = "SELECT COUNT(*) FROM (" & strSQL & ")"

Set rsData = New ADODB.Recordset
With rsData
Set .ActiveConnection = CurrentProject.Connection
.Open strSQL
lngCount = .Fields(0)
.Close
End With
Set rsData = Nothing

 

[Dirk Goldgar]

Thank you for you input here. I suspected that this would be very
unworkable. This list box is on a form that the user enters
information to return search results. Is there code that i can use to
require the user to enter more information before the list box is
populated with too many records? i.e. if the user enters the last
name of smith and there are 500,000 records, how would access (adp)
know this before hauling the data over the network?

What you can do is build and run the "SELECT COUNT" query first after
the filtering criteria are entered, to find out how many records will be
returned, and only if the count is below a reasonable number of records
set the list box's rowsource to the actual query. If the count is too
high, display an appropriate message to the user and ask them to enter
more information to limit the list of records returned.

 

[Lee Taylor-Vaughan]

Were you getting that error before? I don't see offhand how the code I
suggested caused that particular error. Are you sure this error was on
that specific line? I didn't think that section of code was even being
executed when the form is first opened.


<g> Let's go on with the code approach for a bit. I really think it
will be more profitable in the long run.

I see that Brendan is assuming you have a control reference in the list
box's rowsource. If so, his approach is the best way to go about it and
my way won't work without more complex code. I'm assuming, to the
contrary, that you are actually rewriting the listbox's rowsource in the
AfterUpdate event of a text box on the form, so that the control
reference won't present a problem. More information on this point would
settle the matter.

We can fix the code I suggested, at least as far as the second error you
mentioned is concerned, by modifying the rowsource's SQL statement a bit
more before opening it. Try this:

Dim rsData As ADODB.Recordset
Dim lngCount As Long
Dim strSQL As String

strSQL = Trim(Me.List74.RowSource)

' Don't proceed if rowsource is blank.
If Len(strSQL) = 0 Then Exit Sub

If Left(strSQL, 7) = "SELECT " Then
strSQL = "SELECT TOP 100 PERCENT " & Mid(strSQL, 8)
End If

strSQL = "SELECT COUNT(*) FROM (" & strSQL & ")"

Set rsData = New ADODB.Recordset
With rsData
Set .ActiveConnection = CurrentProject.Connection
.Open strSQL

---- (.open strSQL) gives this (with no values to search in txtbox) error:
Incorrect syntax near keyword "and"

strSQL = SELECT COUNT(*) FROM (SELECT TOP 100 PERCENT AccountID AS [A/C
#], LastName AS LName, FirstName AS FName, MiddleName AS Initial,
AddressLine1 AS Address, City, State, PersonUserName AS UserID,
email_address AS Email FROM tblMain And (State = N'') ORDER BY LastName,
FirstName)
the SQL appears to be ok?, no? -- there has to be the word and, no?

I also get the following error on the same line of code if i enter a value
in the one/or more of the txtboxes: "incorrect syntax near ')'"
strSQL = SELECT COUNT(*) FROM (SELECT TOP 100 PERCENT AccountID AS [A/C
#], LastName AS LName, FirstName AS FName, MiddleName AS Initial,
AddressLine1 AS Address, City, State, PersonUserName AS UserID,
email_address AS Email FROM tblMain And (State = N'') ORDER BY LastName,
FirstName)
there appears to be the correct number of ('s to )'s? - do parentheses have
to be taken out in this type of list count?

(this is becoming more and more educational as time goes on! )

 

[Dirk Goldgar]

Were you getting that error before? I don't see offhand how the
code I suggested caused that particular error. Are you sure this
error was on that specific line? I didn't think that section of
code was even being executed when the form is first opened.


<g> Let's go on with the code approach for a bit. I really think it
will be more profitable in the long run.

I see that Brendan is assuming you have a control reference in the
list box's rowsource. If so, his approach is the best way to go
about it and my way won't work without more complex code. I'm
assuming, to the contrary, that you are actually rewriting the
listbox's rowsource in the AfterUpdate event of a text box on the
form, so that the control reference won't present a problem. More
information on this point would settle the matter.

We can fix the code I suggested, at least as far as the second error
you mentioned is concerned, by modifying the rowsource's SQL
statement a bit more before opening it. Try this:

Dim rsData As ADODB.Recordset
Dim lngCount As Long
Dim strSQL As String

strSQL = Trim(Me.List74.RowSource)

' Don't proceed if rowsource is blank.
If Len(strSQL) = 0 Then Exit Sub

If Left(strSQL, 7) = "SELECT " Then
strSQL = "SELECT TOP 100 PERCENT " & Mid(strSQL, 8)
End If

strSQL = "SELECT COUNT(*) FROM (" & strSQL & ")"

Set rsData = New ADODB.Recordset
With rsData
Set .ActiveConnection = CurrentProject.Connection
.Open strSQL

---- (.open strSQL) gives this (with no values to search in txtbox)
error: Incorrect syntax near keyword "and"

strSQL = SELECT COUNT(*) FROM (SELECT TOP 100 PERCENT AccountID
AS [A/C #], LastName AS LName, FirstName AS FName, MiddleName AS
Initial, AddressLine1 AS Address, City, State, PersonUserName AS
UserID, email_address AS Email FROM tblMain And (State = N'')
ORDER BY LastName, FirstName)
the SQL appears to be ok?, no? -- there has to be the word and, no?

I also get the following error on the same line of code if i enter a
value in the one/or more of the txtboxes: "incorrect syntax near ')'"
strSQL = SELECT COUNT(*) FROM (SELECT TOP 100 PERCENT AccountID
AS [A/C #], LastName AS LName, FirstName AS FName, MiddleName AS
Initial, AddressLine1 AS Address, City, State, PersonUserName AS
UserID, email_address AS Email FROM tblMain And (State = N'')
ORDER BY LastName, FirstName)
there appears to be the correct number of ('s to )'s? - do
parentheses have to be taken out in this type of list count?

(this is becoming more and more educational as time goes on! )

The two SQL statements you posted appear to be identical. Was that a
copy/paste mistake on your part? In either case, neither is correct
syntax, I don't think it's due to the code that I wrote. You're
building the rowsource in code, right? It looks like you aren't
building it properly, because this part of the statement:

FROM tblMain And (State = N'')

is clearly invalid. It appears that most of the WHERE clause, including
the WHERE keyword itself, has been left out. You'd better debug the
code that builds the rowsource.