Full Version: Lock control on main form when check box control in subform clic
UtterAccess Forums > Microsoft® Access > Access Forms
peterparker77
I have a form with a subform - both of these have a check box called "Choose". I want to disable the choose check box on the main form when the user selects one or more "choose" check box(es) in the subform. If I understand correctly this would be done with vba at the "on enable" event of the check box in each form. How best do I go about this? A couple lines of the vba code to get me started would be greatly appreciated. Thanks.
Larry Larsen
Hi
This link will give you details on how to reference other controls on other forms.
Refer to Form and Subform properties and controls.
Pay attention to:
Me.Parent!ControlName.Enabled
TH's
thumbup.gif
peterparker77
One side of my problem now works the way I intended (the check boxes on my subform behave as expected) but I can not make my check box on my main form work. This is what I have:
If Me!ItemDetailsChooseSubform.Form!chooseSubItems.Value = True Then
...and I am following the syntax as specified in your link. Can you spot my problem?
Also, If I have multiple check boxes on my subform(1) and I want to test for the value (if any of them have been checked, are True) to determine if the checkbox on the main form should be locked or not do I have to use a count statement along with a loop to test to see if any subform checkboxes have been clicked?
Thank you!
Larry Larsen
Hi
This code below is a example of control the Enable property of the mains form checkbox.
This code segment is in the afterupdate event of a checkbox on the subform.
CODE
Private Sub Check0_AfterUpdate()
If Me.Check0 = True Then
Me.Parent!mainCheck.Enabled = False
Else
Me.Parent!mainCheck.Enabled = True
End If
End Sub

HTH's
thumbup.gif
peterparker77
That worked somewhat...but here is what I am having a problem with now...
) After I select the check box in subform I cannot unselect that same box without getting a run-time error that states: "object does not support this property or method". I will for sure need to allow my users to deselect a checkbox in the subform when I send it out.
2) The main form Check box is staying locked even when I navigate to the next record on the main form. This will be a problem as well. I guess for that I need another event procedure to reset the main check box.
Thank you for your help. I apologize for being a pain!
Larry Larsen
Hi Shawn
Are you able to post up your db so I have a look..?
(cut down version & not confidential data)
thumbup.gif
peterparker77
Thank you - no confidential data here at all. Any help would be greatly appreciated, my boss just asked about this project this morning... frown.gif
Larry Larsen
Hi
Ok.. I seem to have solved the problem of the main form check box becoming available..
Q: When looping through the records should the "Choose" check box on the main form respond to whether any of the subform check boxes are ticked..?
If so we may need to count the ticks in the subform.. eg >0
thumbup.gif
peterparker77
Thanks for getting back to me. If I understand your question correctly, yes when the main_form record is changed (the user goes to the next record, prior record etc) the main Choose box (check box) should change as well. If I am at record 1 and I choose one or more subform choose boxes the main form choose box should "grey out" or become unusable. If I then move to record 2 and there are no checkboxes in the subform then the mainform choose box should be available to me to then choose if I want to. Hope that makes sense.
Larry Larsen
thumbup.gif
Larry Larsen
Hi
Ok.. check out the attached db and see if it complies..
Sorry it's in A2K but should migrate up.
thumbup.gif
peterparker77
That looks great, it seems to do what I want it to do. I really appreciate your help! You saved the day for me! I may have another question for you on this later but right now this seems to be doing what I need it to do. Thanks again!
hawn in Nashville
Larry Larsen
Hi Shawn
You're welcome..
thumbup.gif
peterparker77
One final question for you concerning forms - you know how in a "one" sided table how it has plus signs on the left that allow you to click on them and see the child table and the "many" related records? How do I go about creating the same basic functionality in my form where the subform would only be visible if a check box or hyperlink was clicked on and then the subform for that particular parent record would appear. If you could point me in the right direction one more time. I will make this my last question on this string, thank you!
Larry Larsen
Hi
You already have the basic setup with your current demo example the only bit that's missing would be the part where you hide & show the subform.
What synchronizes you forms is the master/child refeneces you have currently set.
The main form is based on your One sided table.. and your subform is based on the many sided table.
Some coding like this in the main forms current event:
CODE
'*********** Code Start **********
Private Sub Form_Current()
    With Me![SubformName].Form
        .Visible = (.RecordsetClone.RecordCount > 0)
    End With
End Sub
'*********** Code End **********

thumbup.gif
peterparker77
I put the code that you supplied into the On Current event for the main form but the behavior of my form did not seem to change at all. Would you have time to be more specific in terms of the exact lines of code? Sorry to be such a pain, I have been working on learning vba for a few months now and it is slowly becoming more clear to me as I go along. Thanks.
Larry Larsen
Hi
Hope I understand..
Attached is your db now the subform will only appear if the parent record has any child records.
thumbup.gif
peterparker77
Once again, outstanding. I really will have to buy you a beer if I'm ever in Leicestershire. One question for you - and this is more theoretical at this point. If I wanted to change the design of my form around (the main form) so that instead of seeing 3 different text boxes for Item#, Item Name and Cart Qty I wanted instead to show a list box with those fields in it so that navigating from record to record would not be necessary, how might I go about starting that? Thank you
Larry Larsen
Hi Shawn
The offer of a beer is much appreciated..
To create a listbox of values from a table is best done using/creating a query and use this query as the row source for the list box.
Attached is an example..
thumbup.gif
peterparker77
Thanks for that example - that was just a visual representation of what my form would look like with a list box right? Because it did not seem to have any functionality built into it in the attached file.
ouldn't you know it, I had this almost finished and my boss informs me that it will *have* to have a listbox as the main form display - no moving from record to record and displaying one item at a time in the main form. So I feel like I'm back to square one here really! lol Not fun.
peterparker77
I wanted to ask your advise on this new db form...I have the same issue with this new form (list box instead of separate text boxes this time) - I need to make the check box in the main form (frmChoicesAll) only "clickable" if there have been no choices made in the check boxes at the subform level, and vice versa. Could you please tell me what part (what sub procedures) of the ItemName / ItemDetailsChooseSubform I need to incorporate into my new form/subform? It should just be a matter of changing the form and control names in the vba code correct? If you have time for this great, if not I think I can make it work. Thank you sir.
Larry Larsen
Hi
ok.. see attached.
thumbup.gif
peterparker77
Well, I managed to break it! (see attached) It worked when you sent it but I was messing around with the OnLoad event for the form frmChoicesAll, added a couple of things and somehow it screwed up the code block that you had inserted, it is probably something very simple, could you take a look at it? The error occurs when I open that form, select an item in the Listbox in the mainform and then try to make a selection in the subform checkboxes.
lso, at this point the main form choose box does not reset when I move from one Item to another (by selecting a different one in the list box), what was the name of the subprocedure that handled that problem? If you remember it I can modify it to suit my needs. Thank you!
Larry Larsen
Hi
No error's ..?
peterparker77
Sorry about that - I noticed that too after I closed the form and reopened it. Weird.
Can you tell me how I can get the main form check box to reset each time I move to another item in the List box?
peterparker77
I think I spoke to soon, it is doing it again. It is giving me this run-time error "Object Variable or With Block variable not set". Then when I click Debug it is pointing to the SQLString line. I didn't change anything about that bit of code so I don't understand why it is acting buggy. It happens now whenver I open up the frmChoicesAll form, click on an item in the list box and try to click on a check box in the subform that pops up (this is when the main form checkbox is not selected).
Larry Larsen
Hi
Again I can run the db without any error's, it may be a problem with a reference library link.
Check out your reference library for "Missing" links..
Compact & compile the db and see you get any error messages..
thumbup.gif
peterparker77
Well, I compacted and repaired but it did not do any good. I then went to another person's desk to test for the same problem and it occured on his computer.
Could it be a problem in the way the search string is looking for an identifier:
SQLString = "SELECT * FROM ItemDetailsChoose WHERE ItemNum = " & Me.ItemNum
...meaning could there be a problem with the "&Me.ItemNum" part of the code?
Does the fact that I am using a list box on this form matter? I copied and pasted the same code from both of these events (the AfterUpdate event for the subform checkboxes) and they are exactly alike except for the names of the controls (which makes sense). The most noticable difference I can see between the two forms is that in one form I have a list box and in the other I have text boxes.
I'm not sure what you mean by missing links (?) in the reference libary. I checked to see if I have an active reference to the Microsoft DAO object library and I do.
peterparker77
I think i found the source of the problem....and it was something simple...
...at least I hope this is it...I had the name of the subform check box the same as the name of the related (linked) field in my table. Someone else advised me to see if that was the case and it was. When I changed the name of the subform check box (along with the references to that check box in the module) everything seems to be working again correctly. My bad! I know better! I have had classes in Access before and naming conventions is something they taught from day one so I shoud have taken the time to follow them.
Just one more thing, the check box in my main form does not change when i move from record to record by selecting a new item in my list box - what could be causing this?
Larry Larsen
Hi
Check it out now..
Ogot rid of some of that heavy recordset coding and used the DCount()..
thumbup.gif
peterparker77
Thank you! You have gone above and beyond to be sure in your efforts to help me! I think I've got everything that I need on the Form side of this. I am now working on the Report / output. Thank you again for everything Larry! I have had a few questions on the Report that I'm building so I've started a few posts there.
Larry Larsen
thumbup.gif -o!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.