asked on

Access 2016 Reference To SubForm - Error 2465

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.

John Tsioumpris

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

Gustav Brock

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.

ste5an

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.

Bob Collison

ASKER

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.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

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:

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 Collison

ASKER

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.

Gustav Brock

You are welcome!

ste5an

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.