Error 91 on db.openrecordset(strSQL) (1 Viewer)

Argie

New member
Local time
Today, 12:50
Joined
Jul 3, 2016
Messages
3
I am developing a database where I have to create a report with VBA.
This report is a main/sub report with different subreports attached and selected by the user. Also there are sometimes several blank fields in the subreport depending on de the main record and I don't want those blank records occupy spaces on the paper.
Now I am stuck with this error trying to read data from a table to check if a field have a value or not.
This is the code so far:

Sub Test()
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim Aantal As Integer
Dim Apparaat As String

Apparaat = Forms!Toestelgegevens!cboToestel.Value
If Len(Apparaat) = 0 Then
MsgBox "Geen toestel geselecteerd!", vbCritical, "Fout!"
Exit Sub
End If

DoCmd.OpenReport "Toestelgegevens", acViewDesign, , "[Toestel] = '" & Apparaat & "'"

If selKalibratie.Value = True Then
strSQL = "SELECT * FROM Kalibratie WHERE Kalibratie.[Toestel] = '" & Apparaat & "' ;"
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
If Len(rst.Fields("Middel")) > 0 Then
Debug.Print rst.Fields("Middel")
End If
rst.MoveNext
Loop
End If
rst.Close
db.Close

End Sub

Sorry, for some dutch words in it.

When I run this code I get error 91 Objectvariable or Block variable With... in the line in Red. The variable Apparaat have the correct value in it.
I cannot find what is wrong here. I am using the latest version of MS Access.
 

sneuberg

AWF VIP
Local time
Today, 04:50
Joined
Oct 17, 2014
Messages
3,506
You need at least a

Code:
Set db = CurrentDB

before the

Code:
Set rst = db.OpenRecordset(strSQL)
 

Argie

New member
Local time
Today, 12:50
Joined
Jul 3, 2016
Messages
3
Thanks Sneuberg for the quick answer.
I think I am going to bed now (it's late in Belgium) because I didn't see that this line was missing in my code. A few hours ago I delete some lines in my code and by mistake took this line with it but didn't saw it when I get back to it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,175
you can do away without initialising the db variable using:

set rst = dbengine(0)(0).Openrecordset(strSQL)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:50
Joined
Jan 20, 2009
Messages
12,849
set rst = dbengine(0)(0).Openrecordset(strSQL)

I have noticed you often prefer to use the database-workspace reference rather than Currentdb presumably so you can refer to it repeatedly without instantiating an object variable.

However, are you aware of the differences between these two alternatives?

While it usually is, the first database is not guaranteed to be the current database. Also the Currentdb function refreshes the collections while the Workspace reference comes as is.

Best use Currentdb.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,175
yes i know, this is the default workspace (last (0) in the expression).
whether you add new workspace, but refer to 0, it will always point to the current database opened.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:50
Joined
Jan 20, 2009
Messages
12,849
https://msdn.microsoft.com/en-us/library/office/ff835631.aspx

In previous versions of Microsoft Access, you may have used the syntax DBEngine.Workspaces(0).Databases(0) or DBEngine(0)(0) to return a pointer to the current database. In Microsoft Access 2000, you should use the CurrentDb method instead. The CurrentDb method creates another instance of the current database, while the DBEngine(0)(0) syntax refers to the open copy of the current database. The CurrentDb method enables you to create more than one variable of type Database that refers to the current database. Microsoft Access still supports the DBEngine(0)(0) syntax, but you should consider making this modification to your code in order to avoid possible conflicts in a multiuser database.
 

Users who are viewing this thread

Top Bottom