By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

enable subform with button

AccessIdiot
100+
P: 493
Hi there,

I would like to put a button on my form that enables or unlocks the subform and at the same time disables (locks) the main form until the user is done with the subform. At that point the user would click a button on the subform that returns to the main form and re-disables the subform.

In other words, enter data on the main form, click the button to go to the sub form. Main form "locks" (becomes disabled), now the user can enter as many records as they want on the subform. When they are done they click a button on the subform. The main form is now enabled (unlocked) and the subform is disabled (locked).

I'm terrible with code so any code help would be much appreciated.
Mar 19 '07 #1
Share this Question
Share on Google+
51 Replies


Rabbit
Expert Mod 10K+
P: 12,359
Before you do this, my question is why you would need it to work this way.
Mar 19 '07 #2

AccessIdiot
100+
P: 493
I have a one to many relationship between Surveys and Replicates: one Survey can have many Replicates.

The form is the Survey. The data entered on the form is for one and only one Survey. The subform is for the Replicate. A user can enter as many Replicates as they want for that Survey.

I do not wanting them changing anything on the Survey form until they are done entering Replicates. I don't want them changing anything on the Replicate subform while they are entering information about a Survey.

Does that make sense? Originally I had these as two separate forms but it was recommended by a couple of people to change it to a form/subform.

So far I have managed to do this (woohoo!):
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewReplicate_Click()                                                         'New Replicate button
  2. Me.sbfrm_Replicate.Enabled = True
  3. End Sub
This is a button I have on the form that enables the subform (which is disabled to start). But I don't know where to put code to disable the form. Does it also go on the button? (It would if this were actionscript, lol). Or does it go on subform load or activate or update or . . . ?

Thanks for any help!

melissa :-)
Mar 19 '07 #3

Rabbit
Expert Mod 10K+
P: 12,359
What could go wrong if they are allowed to enter survey information without first finishing replicate data, and vice versa?
Mar 19 '07 #4

AccessIdiot
100+
P: 493
Replicates are tied to a survey number. If they change the survey number or information about the survey then the replicate data gets messed up. And vice versa.

Ideally, when the user is done entering replicate data for a particular survey and hits the button for "new survey" all the controls are wiped clean and the user is presented with a spanking clean form.

I need to make these forms as idiot proof as possible. I need to create big bold buttons that make everything simple and easy for the user and not allow them to access things they shouldn't and direct the workflow. Hence disabling controls they shouldn't be dabbling in until they've finished a particular task.

I hope that makes sense.
Mar 19 '07 #5

Rabbit
Expert Mod 10K+
P: 12,359
If you set referential integrity in the relationship between the two tables and set it to cascade updates and deletes then whenever they change a primary key, it will update all foreign key values with the same value and if they delete a primary key, it will delete all foreign key values with the same value.
Mar 19 '07 #6

AccessIdiot
100+
P: 493
So is it a really bad idea or very difficult to lock/disable and then unlock/enable forms and subforms? Is that why I haven't been able to find any help on this subject or other posts with similar issues?

I'm used to working in Flash with actionscript. It's really easy just to target something and set it's enabled property to true or false with the click of a button. Why is it so difficult in Access?
Mar 19 '07 #7

Rabbit
Expert Mod 10K+
P: 12,359
It's not so much that as there are better ways to accomplish what you want. If you still want to lock out all the controls instead, that can be done. Let me know if this is what you want to do instead.
Mar 20 '07 #8

AccessIdiot
100+
P: 493
This is from another thread (click here to read) where the discussion started to merge with this one so I moved this pertinent response to this thread:

Good Luck, let us know how you get along [with creating a form/subform instead of multiple forms]. If you get this working correctly, you can hide the field on the subform altogether so they won't be able to change it.

Also, if you set up the relationship to cascade updates and cascade deletes, then whenever they delete or change the primary key value for a record, it updates to the linked table. And if they delete a primary key value, it deletes all records in the linked tables with the same foreign key value.
The only problem I have with hiding a subform until the correct moment is that the subform is large and takes up a lot of space. So when you open the main form you see a few controls and then a very big empty space. It looks silly! Is there a way to have the form autosize itself to fit whatever controls are visible? If that is possible then simply adjusting the visible property would work great and I wouldn't worry about locking controls.

Can you explain about how to cascade updates and deletes? That sounds like good practice regardless of how the forms/subforms are displayed!

Thanks again for the help - always good to rethink how a project is proceeding.

melissa :-)
Mar 20 '07 #9

Rabbit
Expert Mod 10K+
P: 12,359
I've split the thread to Resizing Forms as it's now on a different subject.
Mar 20 '07 #10

NeoPa
Expert Mod 15k+
P: 31,434
You could try using the .Locked property instead. This leaves the data quite readable but simply disables operator interaction.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewReplicate_Click() 'New Replicate button
  2.   Me.sbfrm_Replicate.Locked = False
  3. End Sub
Disabling the main form (Me) will be more difficult as, by its very nature, the subform is part of that. You could do a loop in VBA that locked the individual controls on the main form excepting the SubForm object.
What would you want to do to trigger the switch back? A button on the SubForm? Or a button on the main form which we would leave unlocked?
Mar 20 '07 #11

AccessIdiot
100+
P: 493
Yes a button on the subform to unlock the main form and lock the subform. Either lock or disable, I'm not partial. :-)

I had been wondering about the continuity - if what you did to a form could carry over to the subform (like locking it) or if they could be targeted and treated as two separate entities.
Mar 20 '07 #12

Denburt
Expert 100+
P: 1,356
On the parent button click event add the following (Enabled or locked or both)

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. For Each ctl In me.Controls
  3. If ctl.ControlType = 122 or ctl.name = "ParentButtonName" Then
  4. ctl.Enabled = true
  5. else
  6. ctl.Enabled = false
  7. end if
  8. Next

On the subform button on click event add this

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. For Each ctl In me.parent.Controls
  3. ctl.Enabled = true
  4. end if
  5. Next
That should do what you need.
Mar 20 '07 #13

AccessIdiot
100+
P: 493
This looks simple and clean, just what I need! Could you explain this line though for me?

If ctl.ControlType = 122 or ctl.name = "ParentButtonName" Then

Thanks Denburt! :-)
Mar 20 '07 #14

AccessIdiot
100+
P: 493
bummer, it doesn't work? :-(
Mar 20 '07 #15

Denburt
Expert 100+
P: 1,356
That line should be changed sorry copy paste and didnt check it. Type 122 is actually a subreport obviously not relevant here :)
That line is there so we dont disable the parents button or the subform, the parents button name should be the only thing in the quotes don't bother the ME! etc. I made a slight change to take into consideration the labels dont have the enabled attribute.



Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. For Each ctl In Me.Controls
  3. If ctl.ControlType = acSubform Or ctl.Name = "ParentButtonName" Then
  4. ctl.Enabled = True
  5. Else
  6. If ctl.ControlType <> acLabel Then
  7. ctl.Enabled = False
  8. End If
  9. End If
  10. Next
Mar 20 '07 #16

AccessIdiot
100+
P: 493
Ahh, so this will keep the subform from getting locked and also the button that all this code is going on right?

I'm also assuming that I need to have the subform disabled when the form loads for the first time?

And FYI for others the 2nd bit of code that goes on the button on the subform doesn't need an "end if". :-)
Mar 20 '07 #17

Denburt
Expert 100+
P: 1,356
Right on target good luck :)
Mar 20 '07 #18

AccessIdiot
100+
P: 493
Okay I tried it and it seems to work from form to subform. That is, subform can't be edited (though it isn't "greyed out" as you'd expect). Click on the button on the form and the form controls grey out and the subform is enabled. Great.

Funny, the Access standard navigation buttons on the bottom of the form work though, even when the form is greyed out? This may not matter though as I am considering hiding these buttons from the user and making my own. I need to dumb these forms down as much as possible. :-)

Something doesn't work quite right on the button to enable the form from the subform. I am getting an error of "Run-time error '438': Object doesn't support this property or method" and it barks at this line:
Expand|Select|Wrap|Line Numbers
  1. ctl.Enabled = True
any ideas?
Mar 20 '07 #19

Denburt
Expert 100+
P: 1,356
uh yeah that the labels change that one line to read:

Expand|Select|Wrap|Line Numbers
  1. If ctl.ControlType <> acLabel Then
  2. ctl.Enabled = true
  3. End If
Mar 20 '07 #20

AccessIdiot
100+
P: 493
Yes! Hey that works great! Now I just need to add some code to once again disable the subform once the controls on the main form are enabled - so it's a constant back and forth: when one is abled the other is disabled.

I think I can piece it together from the other code. I'd like to try anyway. I'm sure I'll be back if I can't get it to work.

Have to head home now but am excited to try it tomorrow!

Thanks again Denburt for all your help!!

:-) melissa
Mar 20 '07 #21

Denburt
Expert 100+
P: 1,356
My pleasure glad I could help. You may want to review the code I posted for the subform since you will need to reference the parent. Good luck and have fun.
Mar 20 '07 #22

AccessIdiot
100+
P: 493
Okay I'm stuck. :-)

On the button on the subform that is supposed to re-enable the form and disable the subform I have this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewSurvey_Click()                                                    'New Survey button - enable form, disable subform
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Parent.Controls
  5.     If ctl.ControlType <> acLabel Then
  6.         ctl.Enabled = True
  7.     End If
  8. Next
  9. For Each ctl In Me.Controls
  10.     If ctl.ControlType <> acLabel Then
  11.         ctl.Enabled = False
  12.     End If
  13. Next
  14. End Sub
But I get this error message:
"Run-time error '2164':
You can't disable a control while it has the focus."

So obviously I need to remove focus. But how do I do that?!

thanks for any help!
melissa
Mar 21 '07 #23

Rabbit
Expert Mod 10K+
P: 12,359
Okay I'm stuck. :-)

On the button on the subform that is supposed to re-enable the form and disable the subform I have this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewSurvey_Click()                                                    'New Survey button - enable form, disable subform
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Parent.Controls
  5.     If ctl.ControlType <> acLabel Then
  6.         ctl.Enabled = True
  7.     End If
  8. Next
  9. For Each ctl In Me.Controls
  10.     If ctl.ControlType <> acLabel Then
  11.         ctl.Enabled = False
  12.     End If
  13. Next
  14. End Sub
But I get this error message:
"Run-time error '2164':
You can't disable a control while it has the focus."

So obviously I need to remove focus. But how do I do that?!

thanks for any help!
melissa
After you enable all the controls on the main form, move the focus to one of the controls with Me.Parent.ControlName.SetFocus
Mar 21 '07 #24

Denburt
Expert 100+
P: 1,356
This should work.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewSurvey_Click()                                                    'New Survey button - enable form, disable subform
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Parent.Controls
  5.     If ctl.ControlType <> acLabel Then
  6.         ctl.Enabled = True
  7.         ctl.setfocus
  8.    elseid CTL.name= "SubFormName"
  9.         ctl.Enabled = false
  10.     End If
  11. Next
  12. End sub
  13.  
Mar 21 '07 #25

AccessIdiot
100+
P: 493
Hi Rabbit, I tried that but it didn't work. Maybe I had the code in the wrong place but it still barked the same error on the same line. I tried your code and got the same thing.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewSurvey_Click()                                                    'New Survey button - enable form, disable subform
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Parent.Controls
  5.     If ctl.ControlType <> acLabel Then
  6.         ctl.Enabled = True
  7.     End If
  8. Next
  9. Me.Parent.txt_SurveyNum.SetFocus
  10. For Each ctl In Me.Controls
  11.     If ctl.ControlType <> acLabel Then
  12.         ctl.Enabled = False
  13.     End If
  14. Next
  15. End Sub
Denburt I tried your code and it didn't work. I think by elseid you meant Else If . . . Then? It doesn't throw an error but it doesn't disable the subform.
Mar 21 '07 #26

Denburt
Expert 100+
P: 1,356
Yes that should have read elseif. My question to you is did you change "SubFormName" to your subforms name (leave out ME! or anything but the name of the form control on the main form) if you did then make sure it is spelled correctly and it should work.
Mar 21 '07 #27

AccessIdiot
100+
P: 493
Okay now when I launch the form the subform starts out greyed out (it didn't before, even though it was disabled in the properties panel) and when I press the button on the form (which is supposed to disable the form controls and enable the subform controls) everything is greyed out, including the subform?

Oh except for the buttons - they all seem clickable and the button you coded above DOES work.

It's so close! :-)
Mar 21 '07 #28

Denburt
Expert 100+
P: 1,356
I press the button on the form (which is supposed to disable the form controls and enable the subform controls)
O.K. show me that piece of code that you are using there.
Mar 21 '07 #29

AccessIdiot
100+
P: 493
Okay, here goes. And just FYI the subform is set to disabled in its properties when the project loads.

Code on button on form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewReplicate_Click()                                         'New Replicate button disable form, enable subform
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Controls
  5. If ctl.ControlType = acSubform Or ctl.Name = "btnNewReplicate" Then
  6.     ctl.Enabled = True
  7. Else
  8.     If ctl.ControlType <> acLabel Then
  9.         ctl.Enabled = False
  10.     End If
  11. End If
  12. Next
  13. End Sub
Code on button on subform:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewSurvey_Click()                                                    'New Survey button - enable form, disable subform
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Parent.Controls
  5.     If ctl.ControlType <> acLabel Then
  6.         ctl.Enabled = True
  7.         ctl.SetFocus
  8.    ElseIf ctl.Name = "sbfrm_Replicate" Then
  9.         ctl.Enabled = False
  10.     End If
  11. Next
  12.  
  13. End Sub
Mar 21 '07 #30

AccessIdiot
100+
P: 493
Okay my bad! I somehow managed to set all the controls enabled property to false when I was trying to set the subform enabled property to false. So it is no longer greyed out when I launch the form.

However the button on the subform does not disable the subform controls, although it does enable the form controls.
Mar 21 '07 #31

Denburt
Expert 100+
P: 1,356
On the code for the sub form...

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnNewSurvey_Click()                                                    'New Survey button - enable form, disable subform
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Parent.Controls
  5.     If ctl.ControlType <> acLabel and not ctl.ControlType = acSubform Then
  6.         ctl.Enabled = True
  7.         ctl.SetFocus
  8.    ElseIf ctl.ControlType = acSubform Then
  9.         ctl.Enabled = False
  10.     End If
  11. Next
  12.  
  13. End Sub
  14.  
Mar 21 '07 #32

AccessIdiot
100+
P: 493
Brilliant! Thank you!

Instead of using an else if I tried just having a nested if, like so:
Expand|Select|Wrap|Line Numbers
  1. For Each ctl In Me.Parent.Controls
  2.     If ctl.ControlType <> acLabel Then
  3.         ctl.Enabled = True
  4.         ctl.SetFocus
  5.                If ctl.ControlType = acSubform Then
  6.                     ctl.Enabled = False
  7.                     End If
  8.     End If
  9. Next
But it didn't work. Any ideas why? Why is that with your code we had to tell Access not to enable the subform in the first if statement and then again in the else if? Isn't that sort of doing the same thing twice? Sorry if the question is stupid but I'm just trying to understand how Access works.

Okay, the final touch would be to have both the form and subform advance to a nice new clean record. I have this right now beneath the "Next" of your code above and it works:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord acDataForm, "frm_Survey", acNewRec
but I can't do the same for the subform or it throws an error that the sbfrm isn't open.
Mar 21 '07 #33

Denburt
Expert 100+
P: 1,356
The if statement merely tells access that if the type of control is a label or subform then move on to the next section (since we don't want those enabled labels dont have that feature and the subform we want to disable) which in this case is an elseif, there I ask access if this control type is a subform (since that is the only control we want to disable) then we want to disable it.


Why is that with your code we had to tell Access not to enable the subform in the first if statement and then again in the else if?
Putting it another way if the control is a subform we dont want to run the first part (enabled) so access will look at the second part and realize what we want (a subform disabled).


Expand|Select|Wrap|Line Numbers
  1. If ctl.ControlType <> acLabel and not ctl.ControlType = acSubform Then
  2.         ctl.Enabled = True
  3.         ctl.SetFocus
  4.    ElseIf ctl.ControlType = acSubform Then
  5.         ctl.Enabled = False
  6.     End If
but I can't do the same for the subform or it throws an error that the sbfrm isn't open.
If you have just created a new record in the main form then you shouldn't need to go to a new record in the subform since the first record is a new one.
Mar 21 '07 #34

AccessIdiot
100+
P: 493
Thank you for the explanation, that helps a lot.

My bad on the form going to a new record - it works fine. I have a couple of unbound controls - a combo box and a couple of textboxes and I'm trying to figure out how to clear them and get them back to blank when I re-enable the form and with a new record.

Thanks so much for your help, I'm so happy its working! :-)
Mar 21 '07 #35

Denburt
Expert 100+
P: 1,356
I'm so happy its working! :-)
I am glad it is working also good luck, and give us a holler if you need anything else..
Mar 21 '07 #36

NeoPa
Expert Mod 15k+
P: 31,434
D4mn you guys have been busy!
I look away for a coupla days and you fit in a month's worth of posts.
Anyway Denburt, Nice work.
And Melissa, keep asking for those explanations. It's really good to understand the answers, rather than simply pasting them into your code.
Mar 22 '07 #37

Denburt
Expert 100+
P: 1,356
D4mn you guys have been busy!
I look away for a coupla days and you fit in a month's worth of posts.
Anyway Denburt, Nice work.
And Melissa, keep asking for those explanations. It's really good to understand the answers, rather than simply pasting them into your code.
LOL, thanks and I agree that understanding what you are doing goes much further than simply copy paste. Which is why I will be more than happy to try and explain anything I can.
Mar 22 '07 #38

AccessIdiot
100+
P: 493
Yeah this has been great, I'm learning a lot and learning how to apply similar code to other forms. Thanks again to both of you!
Mar 23 '07 #39

NeoPa
Expert Mod 15k+
P: 31,434
That's great. It's always fun working with someone who wants to learn rather than just copy in solutions :)
Mar 23 '07 #40

Denburt
Expert 100+
P: 1,356
I'm learning a lot and learning how to apply similar code to other forms
I like hearing statements like that it makes me feel good. Makes me think of the old expresion "I am not just feeding someone but teaching them to fish". ;)
Mar 23 '07 #41

Rabbit
Expert Mod 10K+
P: 12,359
I like hearing statements like that it makes me feel good. Makes me think of the old expresion "I am not just feeding someone but teaching them to fish". ;)
Too bad you can't eat a database.
Mar 23 '07 #42

Denburt
Expert 100+
P: 1,356
LOL tried that once all the little black (I think it was crunchy pepper or maybe resistors) things on the green waffle thingy kept getting stuck in my teeth. :)
Mar 23 '07 #43

NeoPa
Expert Mod 15k+
P: 31,434
If you can teach them about spices, they can feed themselves and make loads of money into the bargain. Don't get them putting any resistors in though, that won't help sales ;(
Mar 24 '07 #44

AccessIdiot
100+
P: 493
Spices? Hah, I'm having enough trouble just making toast.

;-)
Mar 26 '07 #45

Denburt
Expert 100+
P: 1,356
No resistors? Thatís my main ingredient....
Mar 26 '07 #46

NeoPa
Expert Mod 15k+
P: 31,434
Picture me holding my head in my hands reflecting the despair. There is little help left for the world it seems :(
What price sanity?
Mar 26 '07 #47

Denburt
Expert 100+
P: 1,356
Sanity? I didn't know such a word existed.... What does it mean?
Mar 26 '07 #48

NeoPa
Expert Mod 15k+
P: 31,434
I'm not sure I can remember any more :(
I'm sure I knew it once.
Mar 26 '07 #49

Denburt
Expert 100+
P: 1,356
O.K. According to Wiki I must be un-sane :) not quite insane but not quite sane either...

Sanity
Mar 26 '07 #50

51 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.