Run-time error 91 Object Variable or With block variable not set

[Richard Hollenbeck]

I've marked the line in this subroutine where I've been getting this error.
It may be something stupid but I've been staring at this error trying to fix
it for over an hour. I'm pretty sure the table and field names and controls
are all named correctly, and the control referred to in the errant code is
open, and it has data in it.

Private Sub cmdAddIngredientToRecipe_Click()

' Get RecipeID for future action query
Dim recipeID As Long
Dim dbGetRecipeID As DAO.Database
Dim rsGetRecipeID As DAO.Recordset
Dim StrSQL As String
Set dbGetRecipeID = CurrentDb()

StrSQL = "SELECT tblRecipes.lngRecipeID FROM tblRecipes WHERE
(((tblRecipes.lngRecipeID)= " & [Forms]![frmRecipes]![lngRecipeID] & "));"

Set rsGetRecipeID = dbGetRecipeID.OpenRecordset(StrSQL, dbOpenDynaset)
recipeID = rsGetRecipeID.Fields(0)

Set rsGetRecipeID = Nothing
Set dbGetRecipeID = Nothing

' This works fine to this point

' Get IngredientID for future action query

Dim IngredientID As Long
Dim dbGetIngredientID As DAO.Database
Dim rsGetIngredientID As DAO.Recordset

'I'm filling variables to use in an append query down later in this
subroutine.

StrSQL = "SELECT tblIngredients.lngIngredientID FROM tblIngredients
WHERE tblIngredients.IngredientName = '" & cboIngredientName & "'"

'IngredientName is a text field.

' The next line is flagged as a problem. I get a run-time error 91" Object
Variable
' or with block variable not set. I'm predicting something is wrong with my
SQL statament on the previous line.

Set rsGetIngredientID = dbGetIngredientID.OpenRecordset(StrSQL,
dbOpenDynaset)
IngredientID = rsGetIngredientID.Fields(0)

Set rsGetRecipeID = Nothing
Set dbGetRecipeID = Nothing

MsgBox IngredientID 'just to test my code this far.

End Sub

[John Mishefske]

Richard Hollenbeck wrote:

I've marked the line in this subroutine where I've been getting this error.
It may be something stupid but I've been staring at this error trying to fix
it for over an hour. I'm pretty sure the table and field names and controls
are all named correctly, and the control referred to in the errant code is
open, and it has data in it.

Private Sub cmdAddIngredientToRecipe_Click()

' Get RecipeID for future action query
Dim recipeID As Long
Dim dbGetRecipeID As DAO.Database
Dim rsGetRecipeID As DAO.Recordset
Dim StrSQL As String
Set dbGetRecipeID = CurrentDb()

StrSQL = "SELECT tblRecipes.lngRecipeID FROM tblRecipes WHERE
(((tblRecipes.lngRecipeID)= " & [Forms]![frmRecipes]![lngRecipeID] & "));"

Set rsGetRecipeID = dbGetRecipeID.OpenRecordset(StrSQL, dbOpenDynaset)
recipeID = rsGetRecipeID.Fields(0)

Set rsGetRecipeID = Nothing
Set dbGetRecipeID = Nothing

' This works fine to this point

' Get IngredientID for future action query

Dim IngredientID As Long
Dim dbGetIngredientID As DAO.Database
Dim rsGetIngredientID As DAO.Recordset

'I'm filling variables to use in an append query down later in this
subroutine.

StrSQL = "SELECT tblIngredients.lngIngredientID FROM tblIngredients
WHERE tblIngredients.IngredientName = '" & cboIngredientName & "'"

'IngredientName is a text field.

' The next line is flagged as a problem. I get a run-time error 91" Object
Variable
' or with block variable not set. I'm predicting something is wrong with my
SQL statament on the previous line.

Set rsGetIngredientID = dbGetIngredientID.OpenRecordset(StrSQL,
dbOpenDynaset)

The 91 error is indicating that an object variable is not set to a value, i.e. it is
"Nothing".

Looking at the line where the error occurs shows that you are setting the
rsGetIngredientID object to contain a reference to a recordset. This line depends on
another object - "dbGetIngredientID".

Looking at the code I see that this is defined with:

Dim dbGetRecipeID As DAO.Database

so it is defined to be a reference to a DAO Database object. However, I don't see where
you actually instantiated this object; i.e. you don't have a line like:

Set dbGetRecipeID = CurrentDb()

or

Set dbGetRecipeID = dbEngine.OpenDatabase(...)

so that would explain the "Object variable is not set" message.
--
'---------------
'John Mishefske
'---------------

[Richard Hollenbeck]

"John Mishefske" <jm**********@SPAMyahoo.com> wrote in message
news:hY******************@tornado.rdc-kc.rr.com...

Richard Hollenbeck wrote:

I've marked the line in this subroutine where I've been getting this
error. It may be something stupid but I've been staring at this error
trying to fix it for over an hour. I'm pretty sure the table and field
names and controls are all named correctly, and the control referred to
in the errant code is open, and it has data in it.

Private Sub cmdAddIngredientToRecipe_Click()

' Get RecipeID for future action query
Dim recipeID As Long
Dim dbGetRecipeID As DAO.Database
Dim rsGetRecipeID As DAO.Recordset
Dim StrSQL As String
Set dbGetRecipeID = CurrentDb()

StrSQL = "SELECT tblRecipes.lngRecipeID FROM tblRecipes WHERE
(((tblRecipes.lngRecipeID)= " & [Forms]![frmRecipes]![lngRecipeID] &
"));"

Set rsGetRecipeID = dbGetRecipeID.OpenRecordset(StrSQL,
dbOpenDynaset)
recipeID = rsGetRecipeID.Fields(0)

Set rsGetRecipeID = Nothing
Set dbGetRecipeID = Nothing

' This works fine to this point

' Get IngredientID for future action query

Dim IngredientID As Long
Dim dbGetIngredientID As DAO.Database
Dim rsGetIngredientID As DAO.Recordset

'I'm filling variables to use in an append query down later in this
subroutine.

StrSQL = "SELECT tblIngredients.lngIngredientID FROM tblIngredients
WHERE tblIngredients.IngredientName = '" & cboIngredientName & "'"

'IngredientName is a text field.

' The next line is flagged as a problem. I get a run-time error 91"
Object Variable
' or with block variable not set. I'm predicting something is wrong with
my SQL statament on the previous line.

Set rsGetIngredientID = dbGetIngredientID.OpenRecordset(StrSQL,
dbOpenDynaset)

The 91 error is indicating that an object variable is not set to a value,
i.e. it is "Nothing".

Looking at the line where the error occurs shows that you are setting the
rsGetIngredientID object to contain a reference to a recordset. This line
depends on another object - "dbGetIngredientID".

Looking at the code I see that this is defined with:

Dim dbGetRecipeID As DAO.Database

so it is defined to be a reference to a DAO Database object. However, I
don't see where you actually instantiated this object; i.e. you don't have
a line like:

Set dbGetRecipeID = CurrentDb()

or

Set dbGetRecipeID = dbEngine.OpenDatabase(...)

so that would explain the "Object variable is not set" message.
--
'---------------
'John Mishefske
'---------------

Thank you. That at least changed something. I added the code:
"Set dbGetIngredientID = CurrentDb()" without the quotes just before the
second SQL statement. Now I'm not getting that error 91 anymore. Now I'm
getting error 3021, "no current record". But the related forms are open and
pointed at valid records. Hey at this point any change is progress. :-)
I'll double check that my practice query (that works by the way) is closed
then try again.

[Richard Hollenbeck]

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:YiJog.6211$il.3902@trnddc03...

"John Mishefske" <jm**********@SPAMyahoo.com> wrote in message
news:hY******************@tornado.rdc-kc.rr.com...

Richard Hollenbeck wrote:

Problem solved. In addition to forgetting to set the db as the currentdb(),
I was looking for a single value but had multiple columns in the combobox.
Fixed that and everything went well after that. Now on to the next problem.
I'll try to figure it out first.

Thanks for all the help.

Rich