unexpected error: objectvariable or With block....

[gerard1979]

error number 91 is coming up but I've no idea why,
i have the simple code:
dim cnn as adodb.connection
dim rst as new adodb.recordset
set rst = nothing
set cnn as currentproject.connection
strSQL = "SELECT * FROM tblLocatie wHERE [ID] = " & tempID
rst.open strSQL, cnn, adopenstatic, adlockreadonly

now the error comes up in the last line: I have no idea why,
anybody handle this once?
thanks in advance,
gerard

 

[gerard1979]

I didn';t change anything and the following apeared

object required: number 494,

it drives me crazy
Gerard

 

[Laois]

I think it should be

set cnn = currentproject.connection

= not as

Dermot

 

[cmmrfrds]

'- make the connection NEW - which gives an instance
dim cnn as new adodb.connection
dim rst as new adodb.recordset
''- remove this set rst = nothing
'- It just wiped out your rst.
set cnn as currentproject.connection
strSQL = "SELECT * FROM tblLocatie wHERE [ID] = " & tempID
rst.open strSQL, cnn, adopenstatic, adlockreadonly

if rst.EOF = True then
exit
else
' do some processing.
end if

rst.close
Set rst = nothing

 

[Laois]

Your declaring your rst as a recordset and then you are setting it to nothing. Your then trying to open it but it does not exist.

dim rst as new adodb.recordset
set rst = nothing

This is how your code should look.

***********************************************************
Dim cnn As adodb.connection
Dim rst As new adodb.recordset
Dim strSQL As String

Set cnn = New adodb.connection
Set rst = New adodb.recordset

cnn = currentproject.connection
strSQL = "SELECT * FROM tblLocatie wHERE [ID] = " & tempID
rst.open strSQL, cnn, adopenstatic, adlockreadonly

***********************************************************
Notice it is "=" not "As" in the Set statements.

Personaly I like to use the format below when working with ADO. It is easier to read and to find a problem and you don't need the cnn or the strSQL guys.

***********************************************************
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adopenstatic
rst.LockType = adlockreadonly

rst.Open "SELECT * FROM tblLocatie WHERE [ID] = " & tempID

***********************************************************

Dermot

 

[gerard1979]

Thanx for all the quick reactions. When I handle the way of Dermot:
rst.activeconnection =
etc.
I get the following error: to few required parameters.
AND i get not the autofill option (doesn't no the name, but when u do rst. then the possibilities normally come up)

I can't understand this. I'll do this 100 times before. But now I split the rst in several subs maybe that's the problem?
Gerard

 

[cmmrfrds]

You can define the object(s) and create the instance in 1 step, it is your preference.
dim cnn as New adodb.connection
dim rst as New adodb.recordset

If you are using the rst across subs in a single Form, then define as Public in the declarations section.
If you are using the rst across Forms, then define as Public in the declarations section of the standard module.