We help IT Professionals succeed at work.

MS Access Combobox to Disable Controls on a Separate Form

TechNinja2
TechNinja2 asked
on
112 Views
Last Modified: 2017-04-20
Hello Experts!

Got another question for you.  I use the following formula to disable/enable controls in Form 1 based on a selection of a combo box in Form 1:
If Me.Combobox.Value = "Item1" Then
     For Each ctl In Me.Controls
          ctl.Enabled = True
     Next ctl
ElseIf Me.Combobox.Value = "Item2" Then
     For Each ctl In Me. Controls
          If ctl.Tag = "Item2" Then
               ctl.Enabled = False
          End If
     Next ctl
End If 

Open in new window

It works great.  What I want to do now is to disable controls in Form 2.  I tried copying the same code in the Current event of Form 2, but a get a Run-time error '438 - object doesn't support this property or method.

I'm hoping I don't need to call out each control separately in Form 2's Current event.  Is there a better (shorter code) way?

Thx!!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
in form2  change
"Me.Textbox.Value "

with

Forms!Form1.Textbox.value

Author

Commented:
Thx for your quick response Rey.  Now I'm getting run time error 2450 - cannot find the reference Form1 (and yes, I substituted Form1 with the name of my 'Form1' where the combo box resides).  Should I put something in the After Update event of Form 1 for Form 2?
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Is there a way to make this private?  It's client confidential.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
@ Rey.  As I got thinking about it - I'm nervous about sending actual code (which would reveal the client's name) without having you sign a confidentiality/non compete agreement with the litigious land we live in :/

@ Pat. The requests of the client are making this database complex.  Forms 1, 2, and 3 have the same controls at the top (sadly a tabbed single form was not an option):

cboSerial: [u]Serial#[/u]   txtDetail: [u]Description[/u]    txtDate: [u]DateofEntry[/u]
(^ = Combobox in my code above)

The user can choose a Serial # from Combobox/cboSerial on Form 1 and it will:
- Auto-populate the Description and DateofEntry boxes at the top of Form 1 and auto-populate Serial#, Description, DateofEntry  boxes at the tops of Forms 2 and 3
- Go to associated subform record on the same form
- Disable certain controls on Form 1 if Serial # ABC is selected (using After Update on the combobox)
- AND the final component on which I'm working, disable certain controls on Forms 2 and 3 since the header auto-populates with the same Serial#/Description/DateofEntry

I figured it out (sometimes I just need the experts to ask questions when I get stuck so I can work through it)

The Current event didn't like this part of the formula (highlighting ctl.Enabled = True w/ the 438 error):
[code]If Me.Combobox.Value = "Item1" Then
     For Each ctl In Me.Controls
         ctl.Enabled = True
     Next ctl[/code]

so I rewrote the formula like this and pasted it in the After Update of Combbox/cboSerial and Current Form of all 3 Forms:
[code]If Me.Combobox.Value = "ABC" (or Item2 above) Then
     For Each ctl In Me.Controls
          If ctl.Tag = 1 Then
               ctl.Enabled = False
          End If
     Next ctl
Else
     For Each ctl In Me. Controls
          If ctl.Tag = 1 Then
               ctl.Enabled = True
          End If
     Next ctl
End If [/code]

I don't know why it won't work w/out ctl.Tag in the Current event and doesn't need it in the After Update event for "Item 1," but now it works like a charm.  The user can select the Serial # from any of the 3 forms and when it hits ABC, certain controls are disabled on the current form and disables certain controls on Forms 2 and 3 when the user switches to them.

I'll give you both points for helping me out.
CERTIFIED EXPERT
Top Expert 2016

Commented:
you should have accepted your last post as the answer.

Author

Commented:
The last time I did so and tried to assign myself a portion of the points, EE grilled me as to why I answered my own question and was giving me grief about assigning myself points.  (I guess it could be a way some people cheat?)  I don't really care about the points.  I just care about getting my projects done (and if someone has the same question, they can scroll through and find it at the end :)
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You can always choose one of your own posts as the answer.  You just can't assign yourself any points for obvious reasons.

At least you have something you think works but "Forms 1, 2, and 3 have the same controls at the top (sadly a tabbed single form was not an option)" isn't an answer to the question "Are you trying to disable the controls on Form2 from code running in Form1?  "  It is a non-sequitur.

Just FYI, if you are going to have multiple forms open at one time, the best technique is to hide all but one or make the active form Model so the user can't type in any other form until the model form is closed.  Then if you need data in a model form that originates in the calling form, "pull" it by having form2 get the values from form1.  Do NOT "push" the data from form1 to form2.  "pushing" causes you to dirty the record in form2 and that is poor practice since it leads to confusion for the user and leads to incomplete records being saved if you are not careful with your validation.

Author

Commented:
Thx Pat - good to know.  Just giving the big picture in my response.  Only one form is available to the user at a time (even though technically all three are open)

Cheers!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.