Full Version: Verify Update to a Combo Box
UtterAccess Forums > Microsoft Access > Access Forms
zoid
Verify Update to a Combo Box
I have the following code in the AfterUpdate event of my combo box;
CODE
  
Private Sub ResolutionBox_AfterUpdate()
Dim Resp
Dim MyUpdate As String
Dim MyDate As Date
MyDate = Format(Date, "Short Date") & " " & Format(Time(), "Long Time")
Resp = MsgBox("Do you want to update the Resolution?", vbYesNo)
If Resp = vbNo Then
    Exit Sub
ElseIf Resp = vbYes Then
    MyUpdate = "INSERT INTO RES_HIST ( RES_ID, RES, RES_DATE )SELECT " _
    & Me.ID & ", " & "'" & Me.RESOLUTION & "'" & ", " & "#" & MyDate & "#" & ";"
    CurrentDb.Execute MyUpdate
End If
End Sub

Everything works just fine but Id like to expand on it.
Right now the combo box is updated no matter what the user clicks in the message box.
Is there a way to NOT update the combo box if the user clicks NO in the message box?
R. Hicks
Your validation needs to be in the Before Update evnet of the control instaed of the After Update event ...
If the user chooses No .. then invoke the Cancel = True to cancel the event ...
DH
zoid
Wow -it's that simple?
Thanks Ricky!
R. Hicks
It should be .. if I understand your intentions ...
You are welcome .. wink.gif
DH
zoid
I updated the code to:
CODE
Private Sub ResolutionBox_BeforeUpdate(Cancel As Integer)
Dim Resp
Dim MyUpdate As String
Dim MyDate As Date
MyDate = Format(Date, "Short Date") & " " & Format(Time(), "Long Time")
Resp = MsgBox("Do you want to update the Resolution?", vbYesNo)
If Resp = vbNo Then
    CancelEvent = True
    Exit Sub
ElseIf Resp = vbYes Then
    MyUpdate = "INSERT INTO RES_HIST ( RES_ID, RES, RES_DATE )SELECT " _
    & Me.ID & ", " & "'" & Me.RESOLUTION & "'" & ", " & "#" & MyDate & "#" & ";"
    CurrentDb.Execute MyUpdate
End If
End Sub

But the combo box still changes - any thoughts?
dannyseager
replace
ancelEvent = True
with
Cancel = True
zoid
Thanks Danny.
tried your sugestion but the combo box is still changing.
Could it be something else?
zoid
OK - this might help diagnose the problem.
If I change the value in the combo box and select No in the message box, not only does the combo box remain changed, but if I try to click on another control or move to another record, the messagebox reappears.
Is this supposed to happen?
R. Hicks
Your the complete code you are using ...
DH
zoid
Ricky, this is what I currently have.......
!--c1-->
CODE

Private Sub ResolutionBox_BeforeUpdate(Cancel As Integer)
Dim Resp
Dim MyUpdate As String
Dim MyDate As Date
MyDate = Format(Date, "Short Date") & " " & Format(Time(), "Long Time")
Resp = MsgBox("Do you want to update the Resolution?", vbYesNo)
If Resp = vbNo Then
    Cancel = True
    Exit Sub
ElseIf Resp = vbYes Then
    MyUpdate = "INSERT INTO RES_HIST ( RES_ID, RES, RES_DATE )SELECT " _
    & Me.ID & ", " & "'" & Me.RESOLUTION & "'" & ", " & "#" & MyDate & "#" & ";"
    CurrentDb.Execute MyUpdate
End If
End Sub

Thanks again!
zoid
Just as an FYI for anyone with a similar problem.......
just needed to add "Me.Undo" right before the line "Cancel = True"
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.