Access 2016 Reference To SubForm - Error 2465

Bob Collison
Bob Collison used Ask the Experts™
on
I have a Form with a SubForm.

I'm trying to provide a reference to a SubForm that is contained within a Main Form.  The SubForm is not linked to the Main Form.

I have copied the Form Names directly from the Objects so they match exactly.
'Main Form Object Name: F-48-910 - Create New Event Import Data Form
'SubForm Object Name: F-48-911 - Create New Event Import Data SubForm

Code
Dim F48911_001 As Form
Set F48911_001 = [Forms]![F-48-910 - Create New Event Import Data Form]![F-48-911 - Create New Event Import Data SubForm].[Form]

When the 'Set' statement runs I get an Access Error Number 2465  'Can't find the field 'F-48-911 - Create New Event Import Data Subform' referred to in your expression(2465).

I have tried for a couple of hours to fiugure this out without solving it.

Please help!

Thanks,
Bob C.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
If the subform has code behind (is registered) then you can reference it easily like this
Form_NameOfTheSubform.

Open in new window

After the dot Intellisense will kick in and you have full access to all methods/properties/controls
if the form name has spaces in it then the reference is slightly modified
[Form_Name Of TheSubform].

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It should read:

Dim F48911_001 As Form
Set F48911_001 = Me![NameOfYourSubformCONTROL].Form

Open in new window

If that fails, either the control name isn't correct, or try avoiding the underscore in Form's name.
ste5anSenior Developer

Commented:
Or (in addition to Gustav), when want to you run it not in the main form:

Dim F48911_001 As Access.Form
Set F48911_001 = [Forms]![MainFormName]![SubFormControlName].[Form]

Open in new window

The key is, that after the main form name comes the name of the control hosting the sub form. Due to defaults of Access naming controls after content, it maybe named automatically with the same name of the sub form. But you need to check this in the property editor.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Bob CollisonSystem Architect

Author

Commented:
Hi Experts,

Thanks for the updates.  I still can't get it to work having tried all kinds of syntax.  i.e. Same error.

Having viewed the Properties - All Tab the Form Name is correct.  However, please note that the Form Name is exactly as provided above including the spaces and special characters.  This has always been my naming convention (I have it hundreds of Events where it works correctly) and the reason I use the 'Alias' method.  e.g.:
Dim F48911_001 As Form
Set F48911_001 = [Forms]![F-48-910 - Create New Event Import Data Form]![F-48-911 - Create New Event Import Data SubForm].[Form]

I then use the Alias in referencing the Field.  e.g. F48911_001!SORT_SEQ

Since F48911 is a form within F48910 I beleive the Set statement above is the correct one to use.

Additional suggestions?

Thanks,
Bob C.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
From your description, it has to work, so something else is going on.
I still suspect your weird and extensive names as a source for your trouble.
ste5anSenior Developer

Commented:
In your sample

 [Forms]![F-48-910 - Create New Event Import Data Form]![F-48-911 - Create New Event Import Data SubForm].[Form]

Open in new window


The string [F-48-911 - Create New Event Import Data SubForm] must be the name of the control in your form. Not the form name.

Attached is a sample using Main and Sub as form names:

Capture.PNG
Option Compare Database
Option Explicit

Private Sub txtReadValue_Click()

  Dim SubForm As Access.Form
  
  Set SubForm = Me!sfmSubForm.Form
  Me.Caption = SubForm.txtValue.Value
  Set SubForm = Nothing

End Sub

Open in new window

EE29162685.accdb
Bob CollisonSystem Architect

Author

Commented:
Hi Experts,

It's working!

I finally found that the Form F48911 Name on the Property Sheet - Other Tab was still refereincing another form that I had used as a jump start to create this one.  i.e. Related to Gustavs' comments.

Sorry for not identifying this earlier.

Thanks to all,
Bob C.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
ste5anSenior Developer

Commented:
Yup, wrote that you need to check that ;)

BUT:
I finally found that the Form F48911 Name on the Property Sheet - Other Tab was still refereincing another form that I had used as a jump start to create this one.  i.e. Related to Gustavs' comments.
There is no reference on that tab. It's the name of the subform control.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial