Full Version: Select NO in MsgBox to undo option button update
UtterAccess Forums > Microsoft® Access > Access Forms
snsd
Hi:
Is per the below code, when the user clicks on an option button and a combo box is not null, a message box pops up. If the user selects “yes”, the combo box is cleared; if they select “no”, the option button is supposed to revert to its original value. I have obviously used the wrong code to have the option button revert to its original value as it does not work like I want it to.
It would be greatly appreciated if somebody could help me with the correct code for the Case vbNo where I currently have Me.grpRestrictedFlag.Undo.
Thanks in advance for any help you might be able to offer.
Dave
CODE
'Warns user if changing record from Restricted (1) to Not Restricted (2)
Private Sub grpRestrictedFlag_BeforeUpdate(Cancel As Integer)
If Me.grpRestrictedFlag <> 1 And Not IsNull(Me.cboParentCUSIP) Then
Select Case MsgBox("Do you really want to remove the link/cross reference?", vbYesNo)
    Case vbYes
        Me.cboParentCUSIP = Null
    Case vbNo
        Me.grpRestrictedFlag.Undo
    Case Else
End Select
End If
Exit Sub
End Sub
Jack Cowley
Try:

Me.grpRestrictedFlag = 0

I just reread you post and you want the option group to revert to its previous selection and the code I suggested clears the option group. The option group should not change as you are not undoing the form. Can you give more details on what happens prior to the Before Update event?

hth,
Jack
Edited by: Jack Cowley on Thu Jan 12 15:31:26 EST 2006.
snsd
Wow, that was a speedy response!
Thanks for the suggestion. When I change it as you suggested, I get the error message:
The macro or function set to the BeforeUpdate or ValidateRule property for this field is preventing Restricted Securities Database from saving the data in the field.
Any other ideas?
Thanks,
Dave
Jack Cowley
Try this in the No response:
ancel = True
Me.grpRestrictedFlag.Undo
The Cancel will cancel the update...
hth,
Jack
snsd
Bingo!!! Thanks Jack. Awesome stuff.
ave
thanks.gif
Jack Cowley
Dave -
You are welcome and my apologies for being all over the map with my first reponse... It must be the diet Pepsi...
Jack
snsd
No apology required - but how about another related question!
When the user selects a value from a combo box on the mainform, a subform is updated with data based on the value selected. Using the code from the first thread, when the user selects a specific option button, a message box pops-up and asks the user if they want the combobox to be cleared (and the corresponding subform data). I have figured out how to clear the combobox on the mainform, but I don't know how to refresh the subform (subCCApropertyMaster) so that the controls are cleared on it when the combobox is cleared.
Otried the following but it doesn't refresh the subform.
CODE
   Case vbYes
        Me.cboParentCUSIP = Null
        Me.subCCApropertyMaster.Form.Refresh

Thanks in advance for any help you might be able to offer.
Dave
Jack Cowley
Dave -
Is the subform based on a parameter query that is getting its criteria from the combo box or is the combo box the MasterLink for the subform? I am not sure what you mean by 'clearing' the data in the subform so I will start with this code:
Me.subCCApropertyMaster.Form.Requery
If you can give me more details on how the combo box and subform interact and what you want to show in the subform when the combo box is cleared I will do what I can to assist you.
Jack
snsd
Jack:
tried what you suggested - but I don't think it's working. When I exit the record and come back in, the subform does not show. But I want it to "disappear" when the combobox is cleared on the mainform.
Not knowing what a "parameter query" is(!!!), I guess I have the MasterLink...
In the properties of the subform, I have:
source object frmParent
link child fields PM_CUSIP
link master fields ParentPropertyCUSIP
The combo box is bound to ParentPropertyCUSIP. When the user selects a value from the combo box, it updates the child field PM_CUSIP.... I guess that might be stating the obvious. When the user clears the combo box (say, because they shouldn't have linked it to the child), I want the subform to be refreshed so the user doesn't have to close and reopen it to see that the link has been removed. I hope that makes sense. BTW, my frmParent is actually the subform. Long story - that I won't explain here. frown.gif
Dave
P.S. I'm just heading home so might not see your response until tomorrow.
Jack Cowley
Dave -
wo possibilites - If you have a Null value in the combo box then you can use code like this in its After Update event:
Me.SubformControlName.Form.Requery
That will show a blank record in the subform.
If the combo box does not have a Null value the user can select then you will need code (command button?) to set it to null:
Me.ComboBoxName = Null
Me.SubformControlName.Form.Requery
I will not be here in the morning so hopefully this is enough information for you to create a solution.
Jack
snsd
Jack:
realized that I was very unclear in my description of what I’m trying to do. Let me try to explain again.
Stating the obvious…
When my form loads, and no value has been selected in the combo box that acts as the master link for the sub form, the sub form is obviously not displayed. When a value is selected from the combo box, the sub form is displayed correctly. What I want to do is basically have the reverse take place. i.e. when the user clears the combo box (i.e. sets it to null), I want the sub form to “disappear” without the user having to click a button or exit the form.
I have successfully accomplished this using the after update event on the combo box. However, I have another piece of code (the one we were working on) which prompts the user to change an option group button when clearing the combo box. I would have thought that the below code between the asterisks would have made the subform “disappear” but it doesn’t. The user has to exit the form and come back in to have the subform disappear. (The AfterUpdate event on the combo box to requery the subform and main form does not seem to fire when the following code is used. When I comment out the below code and just manually clear the combo box, the subform disappears as desired.)
CODE
'Warns user if changing record from Restricted to Not Restricted
Private Sub grpRestrictedFlag_BeforeUpdate(Cancel As Integer)
If Me.grpRestrictedFlag <> 1 And Not IsNull(Me.cboParentCUSIP) Then
Select Case MsgBox("Do you really want to remove the link/cross reference?", vbYesNo)
    Case vbYes
        Me.cboParentCUSIP = Null
‘*******************************************
        Me.subCCApropertyMaster.Form.Requery
‘*******************************************
    Case vbNo
        Cancel = True
        Me.grpRestrictedFlag.Undo
    Case Else
End Select
End If
Exit Sub
End Sub

(For what it's worth, the main form is frmChild (yes, I know that seems backwards) and the name of the form the subForm is based on is frmParent.)
Any ideas on how to refresh the form would be appreciated. I’d be happy to post the database if that is helpful.
Dave
P.S. No rush on getting back to me as I realize you’re out this morning. I’ll just go with a command button to refresh the form for now.
Jack Cowley
Hmmm. So you have code in the combo box After Update event to requery BOTH the form and the subform and that works. Then add code to requery the main form and the subform in the groups Before Update event and see if that fixes the problem. Also, the After Update event in the combo box is not triggered when you set the combo box to Null in your code. Only actual data entry or a 'physical' change to the combo box will fire the code.
On the code in your post try:
Me.Requery
Me.subCCApropertyMaster.Form.Requery
Sooner or later we will get this to work!!!
Jack
snsd
Hi Jack:
When I do what you suggested, I get the following error message:

Run-time error ‘2115’
The macro or function set to the before update or validation rule property for this field is preventing Restricted Securities Database from saving the data in the field.

Would it be helpful if I posted the database?
Dave
Jack Cowley
Dave -
h yes, the dreaded 2115 error. You cannot do an 'update' in the Before Update event... Now why your code is not working is beyond me because I just tried this code in the Before Update event of a control and the subform showed only a single blank record:
Me.MyCombo = Null
Me.MySubform.Form.Requery
You can post your db but there is NO guarantee that I can find out why your code is not working. Are you positive that the name of your SubformControl is "subCCApropertyMaster"? Is that the name at the top of the property sheet when you select the subform while in design view? At the top of the property sheet it will say:
Subform/SubReport subCCApropertyMaster
If that is what you see then you have the right SubformControl name and I am at a loss... Be sure and compact and repair the db and zip it before posting. The max size of the db you can post here is 500k.
Jack
snsd
Jack:
I have attached the database. To replicate what we’re attempting to accomplish, click on the option button labeled “NOT Restricted”. When prompted to do so, click “Yes”. You will see that the value in the control “Enter Parent CUSIP” gets set to null but the sub form does not refresh. I’m hoping you will be able to see something that I’m missing.
HAs always, thanks in advance for your assistance.
Dave
Jack Cowley
Dave -
I have your db but I must leave now and will return about 2pm Pacific Time. Before I go I need to ask you the name of the form I am to look at. Also, I took a quick squiz at your table and it appears that your structure is not normalized as you have a lot of 'repeating groups'. I will look further when I return but now I have to scoot...
Jack
snsd
Jack:
Thanks. No rush. The form is called frmChild and opens when the db opens.
In terms of normalization.... All of the fields beginning with "PM_" are direct imports from a mainframe database and I'm going to be exporting back to same when I'm done with the database. So the only possible changes would be in the last 11 fields. I think the only two fields that are "questionable" are the ParentProperty fields which should likely be in a separate table - but had my reasons for keeping here.
Talk to you soon.
Dave
Jack Cowley
Dave -
just do not understand the db. I haven't a clue as to what is supposed to happen when the option group is selected, but if I select Restricted and type in anything in combo box below the option group the subform is blank. If I make the only selection available from the combo box the subform shows data so the only conclusion that comes to me is that the master/child link does not have a match. If there is no record in the subform related to the selection from the combo box then the subform will remain blank and the requery is not going to change that. What am I missing?
HAs for the normalization - The date/time fields should be records in a related table, not fields in a record. Since I haven't a clue as to what the db is all about I will just mention the normalization and let you decide if you want to change it.
I wish I had a specific answer for you, but it appears to me that Access is doing what it is supposed to do, but I cannot swear to it.
Jack
snsd
Jack:
apologize for the confusion. Perhaps deleting 430,000 of the records and leaving only ten is making it more difficult to figure out – but I’m sure there’s more to it than that.
At a high level, here’s what the database is all about:
We have a database of approximately 430,000 records. The field PM_CUSIP contains approximately 7,000 records that begin with the numbers “99”. We’re doing a research project that attempts to link some of these 7,000 records to one of the 430,000 records that do not begin with the numbers “99”. (I won’t waste your time explaining why we’re doing such.)
frmChild, which loads when the database opens, displays records beginning with “99” in the main form. The research analyst determines whether the record needs to be linked or not. If the record needs to be linked, they check off the “Restricted” option button and then select a value that does not begin with “99” from cboParentCUSIP (whose row source is basically PM_CUSIP) and the value gets bound to a field called ParentPropertyCUSIP. The field ParentPropertyCUSIP is the Link Master Fields on the subform and the field PM_CUSIP is the Link Child Field.
At the end of the day, the goal is to have all 7000 records either marked as “Restricted” or “Not Restricted” and if marked “Restricted”, the fields ParentPropertyCUSIP and ParentPropertyDescription (which gets autofilled when ParentPropertyCUSIP is filled) are populated.
The sub form simply displays those records we have linked to “Restricted 99” numbers.
THere is the challenge I’m facing:

I have created some validation rules that prevents the control labelled “Enter Parent CUSIP” from being filled if either of the option buttons “NOT Restricted” or “To Research” are checked and forces the control labelled “Enter Parent CUSIP” to be filled if the option button “Restricted” is checked.
All of the records in the database that I posted already have the control labelled “Enter Parent CUSIP” filled and the option button “Restricted” checked.
When you click on either “NOT Restricted” or “To Research” and the control labelled “Enter Parent CUSIP” is already filled you are asked whether you want to remove the link to the sub form or not. When you select “yes”, the control labelled “Enter Parent CUSIP” is automatically set to null and I am wanting the sub form to be refreshed at this point. It is this refreshing that is causing the challenge.
(I should also mention 2 other things. The combo box is set-up to select records only after the user has entered the first 3 characters in order to limit the number of records that are populated in the combo box. That is why nothing is happening when you try to select a row in the combo box as I deleted almost the entire database to make it small enough to post. Secondly, unless it is obvious, the after update event on the control labelled “Enter Parent CUSIP” automatically creates the link to the sub form by updating the control labelled “Parent CUSIP” on the subform.)
Anyhow, I’m realizing this explanation is becoming very convoluted and I think it is time to simply create a refresh button where the user can manually refresh the sub form!
Please do not waste any time trying to decipher this post as you have already helped me solve 99% of the challenges and I know others will not be helped if we spend too much time on this issue. Having said that, if you do understand what I described and you do have the solution, I’m all ears.
Dave
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.