Make field on a sub-form not enabled if checkox on main form is false

SteveL13
SteveL13 used Ask the Experts™
on
I have a form with a sub-form.  If a checkbox on the main form is false then I want a text box on the sub-form to NOT be enabled.

I am trying this but get an error...  "Application-defined or object-defined error"

The main form is frmQuoteOrderDetail
The sub-form is subfrmOrderDetailsSizes
The checkbox on the main form is chkbxYSqty
The textbox on the sub-form is txtYSqty

My code so far:

    If Forms!frmQuoteOrderDetail.Form!subfrmOrderDetailsSizes.Form.chkbxYSqty = False Then
        Me.txtYSqty.Enabled = False
    End If

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
I assume that you have your code in the OnClick event of the checkbox...

Your code is failing because your reference to the field is incorrectly formatted.  As is, it refers to a (non-existent) field on your main form.  This is the syntax you should be using:

If Me.CheckBox = False Then
   Me.MainFormName.SubFormName.FieldName.Enabled = False
Else
   Me.MainFormName.SubFormName.FieldName.Enabled = True
End If

Open in new window


A few more thoughts:  your original code has the syntax flipped;  it looks at a checkbox on the subform, and disables a field on the main form.  If you want to use the full syntax (rather than the Me keyword), say this:

if  Forms!frmQuoteOrderDetail.chkbxYSqty = False Then
     Forms!frmQuoteOrderDetail.Form.subfrmOrderDetailsSizes.txtYSqty.Enabled = false
end if
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Paul's code might work, but generally the syntax to refer to a control on a forms subform looks something like

Form!MainFormName!subformControlName!Form.controlName
or
me!subFormControlname!Form.controllName

Open in new window

so you might try:
If Me.chkbxYSqty = False Then
   Me.subfrmOrderDetailsSizes.Form. txtYSqty.Enabled = False
Else
   Me.subfrmOrderDetailsSizes.Form. txtYSqty.Enabled = True
End If

Open in new window


You would need to put this in the Form_Current event as well as in the checkboxes Checked event.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
I'll just add that the Me keyword is optional, a form know the controls it hold without needing to refer to itself:
If chkbxYSqty = False Then
   subfrmOrderDetailsSizes.Form.txtYSqty.Enabled = False
Else
   subfrmOrderDetailsSizes.Form.txtYSqty.Enabled = True
End If

Open in new window

Should you be charging more for IT Services?

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!

Distinguished Expert 2017

Commented:
I would do this in the Current event of the subform.


Me.Somecontrol.Enabled = Me.Parent!SomeCheckbox


If you put the code in the checkbox of the main form, it will only apply to the subform when the checkbox is modified.  I presume that if the user opens to an existing main form record, you would still want the subform to be controlled by the value in the checkbox.  Therefore, the correct event to use is the Current event of the subform.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You had the form/subform reversed, but still, one line is enough:

Private Sub chkbxYSqty_AfterUpdate()

    Me!subfrmOrderDetailsSizes.Form!txtYSqty.Enabled = Me!chkbxYSqty.Value

End Sub

Open in new window

Distinguished Expert 2017

Commented:
As I already explained, Gus' solution will work ONLY if chkbxYSqty is updated.  If you scroll to an existing record, this code won't run.  That may be fine with you but I'm just pointing out the potential issue.

Also, updating the subform from the mainform may cause an unintended consequence.  I haven't tested it but it seems like moving focus to the subform to post a value in a control will cause Access to save the mainform record.  Depending on your validation code, this may raise strange errors if  there are required fields that follow the check box in the tab order which are not yet filled when the suggested code runs.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
As I stated in my original response:

"You would need to put this in the Form_Current event as well as in the checkboxes Checked event. "

Dale

Author

Commented:
I got it with: (in the on current event of the form, "frmQuoteOrderDetail"

    If Forms!frmQuoteOrderDetail.subfrmOrderDetailsSizes.Form.chkbxYS = False Then
        Me.txtYSqty.Enabled = False
        Else
        Me.txtYSqty.Enabled = True
    End If

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If a checkbox on the main form is false then I want a text box on the sub-form to NOT be enabled.

That's what you asked. It doesn't correlate with what you have "got it with" ...

Author

Commented:
I think I mixed up the question originally.  My mistake.
Distinguished Expert 2017

Commented:
Dale,
Let me get this right.  You are proposing code in two events and are willing to take the potentially adverse consequences of forcing Access to save a record before it is completed by moving focus to the subform rather than moving the code to a single event in the subform?

Steve13,
OK, putting the code in the main form's current event solved the problem I told you that you would have for existing records.  What happens if you change the checkbox?  If you don't have code ALSO in the click event, the subform won't change.  Put  the code in a dozen events if you want or put it in one.

Author

Commented:
The checkboxes are locked and the user can't check/uncheck them.
Distinguished Expert 2017

Commented:
OK, then the Current event of the main form or the Current event of the subform will both work.  I would still use the subform's Current event because that way if the main form is later changed to allow the user to update the flag, then no one will have to remember to change the code.  But that's my style - defensive.  When it is 6 of 1, half dozen of the other, I try to choose the safest option or the option that will provide the most flexibility in the future..  You also never said whether the user can change other items on the main form.

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