Error Message - Object Required

[dhstein]

I have 2 forms. Form A has a combo box. When the user selects the value, I
capture the value, open form B and attempt to set a textbox on formB to that
value. Here is the code:

' Code Start *********************

Private Sub cbxNewProduct_AfterUpdate()


Dim Category As String
Dim NextSKU As String

Category = cbxNewProduct.Value

NextSKU = DLookup("LastEntry", "tblCategories", "ID = " & Category & "")
NextSKU = CStr(Val(NextSKU) + 1)

On Error GoTo cbxNewProduct_AfterUpdate_Err
cbxNewProduct.Value = ""

DoCmd.OpenForm "frmNewProduct"
frmNewProduct.tbxProductSKU.Value = NextSKU


cbxNewProduct_AfterUpdate_Exit:
Exit Sub

cbxNewProduct_AfterUpdate_Err:
MsgBox Error$
Resume cbxNewProduct_AfterUpdate_Exit


End Sub

'Code End **********************


I am getting this error in a message box format:

Microsoft Office Access

Object Required

I tried to set breakpoints, but it isn't clear to me where I'm getting this
error. I'm pretty sure it's on the OpenForm command.

After I wrote the above - I did some more investigation. Form B was copied
from another form that was bound to a table. I wanted to get many of the
same boxes and similar layouts which is why I did that. Form B has all
unbound controls, but the form itself shows the table as the record source.
I can't remove that - it seems to be requiring a record source for the form.
So if there is an easy way to change that thanks, otherwise I will have to
delete the form and start from scratch.

 

[Tom van Stiphout]

On Tue, 17 Feb 2009 19:51:01 -0800, dhstein

Debug this by hitting Ctrl+Break when the error appears, and you
should be able to figure out which line causes the problem.
My guess? The line:
frmNewProduct.tbxProductSKU.Value = NextSKU
Why? Because frmNewProduct is not set. Actually that reminds me, did
you not have Option Explicit at the top of your file? That's a MUST
HAVE. And you should set this to be the default in the Options dialog.

If you want this to work, you have some options:
#1: rewrite as:
Form_frmNewProduct.tbxProductSKU.Value = NextSKU
(this only works if there is at least one line of VBA in the form)

#2: dim frmNewProduct as Form
DoCmd.OpenForm "frmNewProduct"
set frmNewProduct = Forms!frmNewProduct
frmNewProduct.tbxProductSKU.Value = NextSKU

#3: rewrite as:
Forms!frmNewProduct.tbxProductSKU.Value = NextSKU

-Tom.
Microsoft Access MVP

 

[dhstein]

Tom,

Thanks for your reply. Choice 2 worked perfectly. For the record choice
3 gave me the error "Application-defined or object-defined error".

So now that the code works my question is why ? What does it mean to "Set
the Form" What piece of understanding am I missing here? Thanks for your
help.

David

 

[Tom van Stiphout]

On Wed, 18 Feb 2009 03:28:01 -0800, dhstein

Perhaps #3 should be:
Forms!frmNewProduct!tbxProductSKU.Value = NextSKU

You had a variable frmNewProduct of type Form that was not
initialized.
Just writing DoCmd.OpenForm "frmNewProduct" does NOT create such a
variable, nor initialize it. It *does* however create an initialize a
variable Form_frmNewProduct (at least if some VBA code exists in the
code-behind).
If you want to initialize an object-type variable like frmNewProduct,
it is not enough to write:
frmNewProduct = Forms!frmNewProduct
but you need to write:
set frmNewProduct = Forms!frmNewProduct
(just like you do with other object-type variables like recordsets).

HTH,

-Tom.
Microsoft Access MVP

 

[dhstein]

From what little I know about OOP, is this an example of instantiating an
instance of the object frmNewProduct whose class is Forms!frmNewProduct ?
Thanks again for the help and the information.

David