473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

enable subform with button

AccessIdiot
493 256MB
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
51 15170
Rabbit
12,516 Expert Mod 8TB
Before you do this, my question is why you would need it to work this way.
Mar 19 '07 #2
AccessIdiot
493 256MB
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
12,516 Expert Mod 8TB
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
493 256MB
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
12,516 Expert Mod 8TB
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
493 256MB
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
12,516 Expert Mod 8TB
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
493 256MB
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
12,516 Expert Mod 8TB
I've split the thread to Resizing Forms as it's now on a different subject.
Mar 20 '07 #10
NeoPa
32,556 Expert Mod 16PB
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
493 256MB
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
1,356 Expert 1GB
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
493 256MB
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
493 256MB
bummer, it doesn't work? :-(
Mar 20 '07 #15
Denburt
1,356 Expert 1GB
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
493 256MB
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
1,356 Expert 1GB
Right on target good luck :)
Mar 20 '07 #18
AccessIdiot
493 256MB
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
1,356 Expert 1GB
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
493 256MB
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
1,356 Expert 1GB
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
493 256MB
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
12,516 Expert Mod 8TB
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
1,356 Expert 1GB
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
493 256MB
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
1,356 Expert 1GB
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
493 256MB
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
1,356 Expert 1GB
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
493 256MB
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
493 256MB
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
1,356 Expert 1GB
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
493 256MB
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
1,356 Expert 1GB
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
493 256MB
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
1,356 Expert 1GB
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
32,556 Expert Mod 16PB
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
1,356 Expert 1GB
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
493 256MB
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
32,556 Expert Mod 16PB
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
1,356 Expert 1GB
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
12,516 Expert Mod 8TB
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
1,356 Expert 1GB
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
32,556 Expert Mod 16PB
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
493 256MB
Spices? Hah, I'm having enough trouble just making toast.

;-)
Mar 26 '07 #45
Denburt
1,356 Expert 1GB
No resistors? That’s my main ingredient....
Mar 26 '07 #46
NeoPa
32,556 Expert Mod 16PB
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
1,356 Expert 1GB
Sanity? I didn't know such a word existed.... What does it mean?
Mar 26 '07 #48
NeoPa
32,556 Expert Mod 16PB
I'm not sure I can remember any more :(
I'm sure I knew it once.
Mar 26 '07 #49
Denburt
1,356 Expert 1GB
O.K. According to Wiki I must be un-sane :) not quite insane but not quite sane either...

Sanity
Mar 26 '07 #50

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

Similar topics

1
by: Suni | last post by:
Hi everybody, But what I am trying to do is like, there are 2 forms, and if I click on the button in form1, it'll open up form2 and disable the button in form1. Then when I click on the button in...
6
by: M O J O | last post by:
Hi, I have a webform with a textbox and a button. When the textbox is empty (=""), I want the button to be disabled. When the user enters text in the textbox, the button must be enabled. Is...
26
AccessIdiot
by: AccessIdiot | last post by:
I have been looking through posts on events happening based on a combo box choice but none really get at the heart of what I am trying to do. I think this is simple but I'm a newbie so any hand...
0
by: ArmyGeek | last post by:
Hey Guys, Im trying to figure out how to Disable a Button after a user clicks it, then Enable the Button if the user emptys a Text Box where the user can input information. I also want to...
2
by: ArmyGeek | last post by:
Hey Guys, Im trying to figure out how to Disable a Button after a user clicks it, then Enable the Button if the user emptys a Text Box where the user can input information. I also want to...
2
by: maheshprof | last post by:
hi , how to enable a button when i enter a text in a textbox.and also it should be disabled initially after user types a text in the textbox it should enable
11
by: ajaymohank | last post by:
Hello Friends....... in my project i am having a dropdown and based on the values selected from the dropdown, i have to enable 2 radio button. in the on change function of drop down i have written...
1
by: dougancil | last post by:
I have 4 forms. I have a button on form2 that when pressed, I want it to enable a button on form1. What I've tried is this: Private Sub Button2_Click(ByVal sender As System.Object, ByVal e...
5
by: hrprabhu | last post by:
In the form of the attached database both the “Update” button and the “Insert” button will be enabled only when all the fields are input. I want to enable only one button at a time. When the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.