Full Version: Having trouble canceling update with Option Group
UtterAccess Forums > Microsoft® Access > Access Forms
BrianS
I have an option group with 2 option buttons. I am checking a condition before letting them update in the BeforeUpdate event then setting Cancel = 1 if the condition is not met. When I do this the option is still moving.
x.
CODE
Private Sub FraOpt_BeforeUpdate(Cancel as Integer)
If FraOpt = 2 then
     If Condition = False Then
         Cancel = 1
     End If
End If
End Sub

Is there some trick with option groups that I am missing?
Jack Cowley
Try:
ancel = True
True is actually -1 but it is easier to read if you use true...
hth,
Jack
BrianS
Nope Jack, I tried that too, actually any non zero integer will work here, It seems like just an odd querk with option groups?? I have to be missing something, here is my code
CODE
Private Sub FraOpt_BeforeUpdate(Cancel As Integer)
On Error GoTo FraOpt_BeforeUpdate_Err
Dim strSQL As String
Dim EmpRec As DAO.Recordset
Set EmpRec = CurrentDb.OpenRecordset("Select * From tblEmployee Where EmpNum = " & Me.txtEmpNum & ";", 2)
'Removes an employee from the Imputed Value program
'Deletes their tblImput Record for this period
strSQL = "DELETE * FROM tblImput "
strSQL = strSQL & "WHERE EmpNum = " & Me.txtEmpNum & " "
strSQL = strSQL & "AND PayDate = #" & Me.txtDate & "#;"
If FraOpt = 2 Then
    If (MsgBox("You are about to remove Employee:  " & Me.txtEmpNum & _
    " from the imputed value program", vbOKCancel + vbExclamation)) = vbOK Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        Me.txtImpVal = 0
        DoCmd.SetWarnings True
        chkImpValFlag = False
        If Not EmpRec.EOF Then
            With EmpRec
                .Edit
                !LicPlate = Null
                !Plack = Null
                .Update
            End With
        Else
            Cancel = -1
            'FraOpt = FraOpt.OldValue
        End If
        'Me.Parent!cboxCarNum = Null
    End If
ElseIf FraOpt = 1 Then
    If Not IsNull(EmpRec!LicPlate) Then
        chkImpValFlag = True
    Else
        MsgBox "You must first assign a city vehicle to this employee.", vbExclamation, "Imputed Value"
        Cancel = -1
        'FraOpt = FraOpt.OldValue
    End If
End If
    
FraOpt_BeforeUpdate_Exit:
EmpRec.Close
Set EmpRec = Nothing
Exit Sub
FraOpt_BeforeUpdate_Err:
MsgBox Err.Number & " " & Err.Description
Resume FraOpt_BeforeUpdate_Exit
End Sub
Jack Cowley
Well, you have completely stumped me. Your code looks OK and it does not make sense that the Cancel would not work. If the Option = 1 you would think that the Cancel would work. Have you tried using Case Select just for fun instead of If Then? It would seem that it would not make any difference but who knows...
I'm sorry that I do not have a definitive answer for you. Maybe one of the mightier Access brains has the solution...
Jack
BrianS
I will try the select case and a good 'ol compact and repair to see if it has any effect. Thanks for your help
BrianS
I got nothing, I may have to do this the dirty way - AfterUpdate/OldValue property. I have no idea why this is not working albeit that I have never tried Cancel on an Option Group but still, you would think it would work. This is frustrating!!!
Jack Cowley
WHAT! You hadn't done a compact and repair? OH MY... If that fixes it then we will have both learned something... Good luck with this....
ack
Jack Cowley
I understand your frustration. Logic says that if the Cancel is executed it should work regardless of how it is 'called'. Let's hope a compact and repair sets things right...
ack
BrianS
Nope, the almight compact/repair let me down this time. I am out of ideas
Jack Cowley
How about:
ancel = True
Me.Undo
That just came to mind...
Jack
BrianS
I don't want to undo all the changes on the form, just this option group, btw I tried FraOpt.Undo and that did not work either
Jack Cowley
I am grasping at straws... FraOpt = 0
ack
BrianS
This is all I have been able to get to work (simple example)
CODE
Private Sub Frame0_AfterUpdate()
Select Case Frame0
    Case 1
        MsgBox "nope"
        Frame0 = -1
    Case 2
        MsgBox "yes"
End Select
End Sub

-1 will set the frame to no options
mvos
Brian,
I've been playing around with this and also getting nowhere
One interesting thing, when I set the following code
If Me.OptGrp =1 Then
Cancel = True
End If
and tried to change to design view while option 1 was selected, the change to design was cancelled?????
BrianS
I got that as well. It is cancelling the close or unload event of the form but won't cancel the update. I went with the solution that I described above, wait until afterupdate to test and assign the value of the option group to -1 if it fails the test. I guess we know for the future how to avoid this. I wonder if this is a problem with Access 2000 and above?
BrianS
Since we have pretty much decided that this is a bug, maybe we should run it by Ricky/Gord and report this a little more publicly here as something to watch out for? Any thoughts? John?, Jack?
Jack Cowley
In a VERY quick trial this seems to work:
CODE
Private Sub Frame0_BeforeUpdate(Cancel As Integer)
If Frame0 = 1 Then
Cancel = True
Me.Frame0.DefaultValue = 0
ElseIf Frame0 = 2 Then
MsgBox "This is 2"
End If
End Sub

Jack
BrianS
Yeah it seems to be working here too, I wonder what the default value has to do with anything? Thankyou sooooo much Jack, I would have never thought to try that. I will get back to you after some further tests, thanks again
mvos
Neat Jack,
Seems an odd way to have to cure it though.
BTW - the same problem exists in 2000
Jack Cowley
I do not know why this worked, but it did. Nothing else I tried, just as you tried, worked. Not sure that this is the utlimate answer but if it does what you want then you are home free...
ack
BrianS
Well it seems to be behaving itself, thanks Jack and John for you perservence (hope that is spelled right). Thanks again.
Jack Cowley
LOL! I just realized that this is the Access97 forum! I did my trial in Access2000... I am losing it... Well, actually, I lost it a long time ago...
Thanks John... Again!
Jack
mvos
No problem
Back had the solution, I just confirmed that you weren't 'losing it'
mvos
Better use those glasses Jack
Jack Cowley
I really think I need to connect my optic nerves to my brain!
ack
davidmound
I know this is a really old thread, but I found it in a search and it helped me, so I figured I would post some additional info for anyone who would still be looking...
The original problem above involved an Option Group. I had the same problem in Access2003 with a combobox on a continuos form. Coding 'Cancel = True' wasn't preventing the combobox from changing values.
The solution above (following Cancel = True with Me.combobox.DefaultValue = 0) worked in this situation as well. If anyone is still reading this and knows why this problem occurs, please reply.
Thanks,
David
Jack Cowley
I have not read back through this thread so I am not sure where your Cancel = True code is being used. Not all events support the Cancel code so I am thinking that setting the default to 0 is what corrected the problem.
th,
Jack
davidmound
In the BeforeUpdate event procedure of a combobox, I was checking certain conditions and if an unacceptable condition was found, attempting to cancel the update of the combobox with 'Cancel=True'.
However, that didn't stop it from updating to the new (unacceptable) value. Neither did combobox.undo, nor docmd.cancelevent.
The only thing that seemed to work (and it really is just a workaround), is:
Cancel = True
Me.combobox.DefaultValue = 0
-dm
Jack Cowley
Hmmm. If your two lines of code do what you want then I say go for it, as I cannot think of a better or any other way to do it...
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.