Referencing a subform control with VBA (1 Viewer)

jmwoolst

Registered User.
Local time
Today, 05:29
Joined
Aug 1, 2019
Messages
18
I have a form (frmViewAllAdjustments) that shows all billing adjustments. Some are pending review and some that are already reviewed. The subform (ARO_Adj_Details Subform) shows the related records. My goal is to make the records on the subform editable if the record of the main form has not been reviewed yet.

It seems to be unable to recognize the subform control when I attempt to reference it with VBA for the OnCurrent() function of the main form. Below is the code I used to attempt to enable one of the subform controls based on review status. It has been failing on the second line of the If statement with runtime error 2465 "Access can't find the field '|1' referred to in your expression".

If Me.ReviewApproveDenyBox.Value = "Not Reviewed" Then
Forms.frmViewAllAdjustments.[ARO_Adj_Details Subform].Form.FlatType.Enabled = True
End If

Does anyone know the correct syntax for referencing this control? I have found a few variations online but none have worked for me so far.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:29
Joined
Oct 29, 2018
Messages
21,357
Hi. In the syntax, you need to use the nane of the subform control and not the name of the form it contains. They are usually the same, but sometimes they're not.
 

jmwoolst

Registered User.
Local time
Today, 05:29
Joined
Aug 1, 2019
Messages
18
Hi. In the syntax, you need to use the nane of the subform control and not the name of the form it contains. They are usually the same, but sometimes they're not.

The current objects I have are:
Main form - frmViewAllAdjustments
Sub form - ARO_Adj_Details Subform
Subform Control - FlatType

Would the correct syntax just be
"Forms.frmViewAllAdjustments.FlatType.Enable = True" or would I need additional pieces to specify that the control is on the sub form?

Thank you
 

isladogs

MVP / VIP
Local time
Today, 12:29
Joined
Jan 14, 2017
Messages
18,186
Assuming that is the name of the subform container on the main form, the correct syntax is
Code:
Forms!frmViewAllAdjustments.[ARO_Adj_Details Subform].Form.FlatType.Enabled = True

Or as you are on the main form, you can use the Me. Operator

Code:
Me.[ARO_Adj_Details Subform].Form.FlatType.Enabled = True

You may find this reference useful http://access.mvps.org/Access/forms/frm0031.htm
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:29
Joined
Oct 29, 2018
Messages
21,357
The current objects I have are:
Main form - frmViewAllAdjustments
Sub form - ARO_Adj_Details Subform
Subform Control - FlatType

Would the correct syntax just be
"Forms.frmViewAllAdjustments.FlatType.Enable = True" or would I need additional pieces to specify that the control is on the sub form?

Thank you

Hi. From the main form, I would try this.

Me.FlatType.Enabled=True
 

jmwoolst

Registered User.
Local time
Today, 05:29
Joined
Aug 1, 2019
Messages
18
Assuming that is the name of the subform container on the main form, the correct syntax is
Code:
Forms!frmViewAllAdjustments.[ARO_Adj_Details Subform].Form.FlatType.Enabled = True
Or as you are on the main form, you can use the Me. Operator

Code:
Me.[ARO_Adj_Details Subform].Form.FlatType.Enabled = True
You may find this reference useful

I attempted both versions but unfortunately still received the same error from the initial post.

The link you included offered some alternatives but the syntax I found for referring to a control property on a subform if you are on main form was not successful.

Code from link:
Me!Subform1.Form!ControlName.Enabled

My version:
Me![ARO_Adj_Details Subform].Form!FlatType.Enabled = True

This gave the error that Access can't find the field 'ARO_Adj_Details Subform' referred to in the expression.

Had to take the link out of my reply because I do not have enough posts(?)
 

jmwoolst

Registered User.
Local time
Today, 05:29
Joined
Aug 1, 2019
Messages
18
Hi. From the main form, I would try this.

Me.FlatType.Enabled=True

I attempted this code but received an error stating the "method or data member not found", I assume this is due to the control not technically existing on the main form.
 

Micron

AWF VIP
Local time
Today, 08:29
Joined
Oct 20, 2018
Messages
3,476
Based on what you stated in post 3, post 5 should have worked. I don't see a comment on that version so maybe you missed it? If you did exactly that and it didn't work, there is something else wrong such as an object name is misspelled somewhere. To be clear, this error is raised when you try to navigate from record to record on the main form and it does not occur when the form is opened? I ask because if on opening, then you have this or similar code on the subform current event trying to reference the main form in which case it will fail during form open.
 

isladogs

MVP / VIP
Local time
Today, 12:29
Joined
Jan 14, 2017
Messages
18,186
The code in post #7 is wrong for the reason you stated

Code from link:
Me!Subform1.Form!ControlName.Enabled

My version:
Me![ARO_Adj_Details Subform].Form!FlatType.Enabled = True

This gave the error that Access can't find the field 'ARO_Adj_Details Subform' referred to in the expression.

Your version above is correct PROVIDING the subform container on the main form is called [ARO_Adj_Details Subform]. The fact that it errored indicates a different name has been used

Open the main form in design view. Click on the border of the subform so that it has an orange line around it
IMPORTANT: Do NOT click inside the subform.

Now look in the form property sheet and note the name shown for the subform container (that's the main form control containing the subform). Name is the first item in the Other tab
Now replace the code in RED above with the name shown in the property sheet
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:29
Joined
Oct 29, 2018
Messages
21,357
I attempted this code but received an error stating the "method or data member not found", I assume this is due to the control not technically existing on the main form.
Hi. Sorry to hear that, but it means FlatType is not the name of your subform container. Please follow the other suggestions on how to find out the name of the subform container and then make sure to use it in your syntax. Cheers!
 

jmwoolst

Registered User.
Local time
Today, 05:29
Joined
Aug 1, 2019
Messages
18
Hi All-

Thank you all for assisting, as some of you just pointed out my mistake was with the name of the subform container. I had been referencing it with the name of the subform (ARO_Adj_Details Subform) instead of the name of the container (ARO_Adj_Details Subform(view)). I aliased the container as 'DetailsSubform' and am now successfully referencing it with:

Me!DetailsSubform.Form!FlatType.Enabled = True

Thank you again for all the help!
 

Micron

AWF VIP
Local time
Today, 08:29
Joined
Oct 20, 2018
Messages
3,476
I get it now (I think)! :eek:
This
Subform Control - FlatType

should have said control on subform? For future reference, subform control is the control that contains the subform
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:29
Joined
Oct 29, 2018
Messages
21,357
Hi All-

Thank you all for assisting, as some of you just pointed out my mistake was with the name of the subform container. I had been referencing it with the name of the subform (ARO_Adj_Details Subform) instead of the name of the container (ARO_Adj_Details Subform(view)). I aliased the container as 'DetailsSubform' and am now successfully referencing it with:

Me!DetailsSubform.Form!FlatType.Enabled = True

Thank you again for all the help!
Hi. Congratulations! Glad to hear you got it sorted out. Is FlatType a subform inside a subform? Just curious...
 

jmwoolst

Registered User.
Local time
Today, 05:29
Joined
Aug 1, 2019
Messages
18
I get it now (I think)! :eek:
This
Subform Control - FlatType

should have said control on subform? For future reference, subform control is the control that contains the subform

Yes, I meant to say that the field from the subform that I wanted to reference was FlatType but I worded it as if FlatType was the name of the subform container. Now I understand the correct nomenclature.

Thank you
 

Micron

AWF VIP
Local time
Today, 08:29
Joined
Oct 20, 2018
Messages
3,476
You're welcome. Sorry for any confusion caused. Good luck with your db
 

jmwoolst

Registered User.
Local time
Today, 05:29
Joined
Aug 1, 2019
Messages
18
Hi. Congratulations! Glad to hear you got it sorted out. Is FlatType a subform inside a subform? Just curious...

FlatType is a field on the subform that I wanted to enable, I mistakenly thought control/field were interchangeable which added confusion. I realize I worded it as if FlatType was the name of the subform container when that was not my intention.

Also I didn't even consider the control/container name being different than the name of the subform so that might have been my bigger issue.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:29
Joined
Oct 29, 2018
Messages
21,357
FlatType is a field on the subform that I wanted to enable, I mistakenly thought control/field were interchangeable which added confusion. I realize I worded it as if FlatType was the name of the subform container when that was not my intention.

Also I didn't even consider the control/container name being different than the name of the subform so that might have been my bigger issue.
No worries. We were all happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom