Form in Access “Object not Defined” Error

Question

I have programmed user forms in Word and Project without problems but I am having problems loading Form1 in MS Access. It gives me this error:

Object variable or With block not set

The form does launch when I use DoCmd.OpenForm "Form1", acNormal, , , , acDialog.

I now understand that Load Form1 does not work in MS Access. So I must use DoCmd.OpenForm...

But I am still having problems accessing the data entered on the form.

My form has two entry fields: frm_Company and frm_Date, Below is the code.

Dim str_Company as String 'variable to capture result for form input
Dim date_Forecast as String 'variable to capture result of form input


DoCmd.OpenForm "Form1", acNormal, , , , acDialog

str_Company = Forms!Form1!frm_Company
date_Forecast = Forms!Form1!frm_Date

When the code is executed Form1 launches, the user enters the data for the two field and presses a "Close Form" button. When VBA tries to execute the last two lines I get the error:

Microsoft cannot find the referenced form Form1

Thanks for your assistance.

If you declare Form1 As Form, and then use it without assigning that variable any reference, you are referring to an invalid object reference and that will always raise error 91. Access forms are document modules, like Worksheet modules in Excel: they're owned by Access, not VBA: they have very little in common with userforms, other than "form" as part of the name. — Mathieu Guindon
Me refers to the current object, you can't use it outside the form to refer to that form. — Mathieu Guindon
Why do you need to open a second form? Why do you need 2 values from second form? Why is data not just entered on first form? Is second form UNBOUND? — June7
My first form opens an Excel file I will use to load data into my database. Before I load the data I need the user to enter the Company name and the File Date on Form1 that will loaded into every record of the database along with data obtained from the Excel spreadsheet. — Kaiser Otto
Still doesn't explain why the two values can't be input on first form. — June7

June7 June7 · Accepted Answer · 2019-09-25T05:51:41

Not necessary to use form object variable just to open a form. Simply reference form name - as in successful third attempt. Then if calling form needs to reference form it opened, use Forms collection.

Forms!Form1.controlname

or

Forms!Form1!fieldname

Me qualifier references object code is behind.

However, acDialog suspends code execution until called form closes. Therefore, when first form code resumes, second form has been closed and cannot be found. Code behind second form should reference first to push values back to first form. Alternative would be to set global variables or TempVars that can then be referred to by any procedure.

Form in Access “Object not Defined” Error

1 Answers