Resetting a field through VBA if criteria not met

[jossimon]

I have the following code:
Private Sub cmb_status_AfterUpdate()
DoCmd.SetWarnings False
Dim CancelUpdate As String

If Me.cmb_status = 5 Then
If IsNull(Me.CompleteDate) Then
MsgBox "Cannot update Status to Complete until Complete Date and Time to Complete are populated."
CancelUpdate = "Update T_Tickets set StatusId = null where Ticketid = " & Forms!F_UpdateTicket.Txt_TicketNo
DoCmd.RunSQL (CancelUpdate)
Me.Requery
Else
If IsNull(Me.cmb_CompleteTime) Then
MsgBox "Cannot update Status to Complete until Complete Date and Time to Complete are populated."
Else
End If
End If
Else
End If
DoCmd.SetWarnings False
End Sub

The CANCELUPDATE script is not taking place.

Basically, I have a combo box to select from but if they select "Complete", the system needs to check to see if Complete Date and Time to complete have been populated. If not, then it needs to cancel the update to the status combo box and leave it blank until those two fields have been populated. Please help.

 

[dRahme]

Hi, You might double check your sql string. If this is from a query go to design view then sql view. Cut and paste
the sql into your code.


Private Sub cmb_status_AfterUpdate()
DoCmd.SetWarnings False

If Me.cmb_status = 5 Then

If IsNull(Me.CompleteDate) Or IsNull(Me.cmb_CompleteTime) Then

MsgBox "Cannot update Status to Complete until Complete Date and Time to Complete are populated."

DoCmd.RunSQL "Update T_Tickets set StatusId = null where Ticketid = " & Forms!F_UpdateTicket.Txt_TicketNo & ""

Me.Requery

End If
End If

DoCmd.SetWarnings True
End Sub

Hope that helps.

 

[jossimon]

This still does not reset the StatusID to null. Any more suggestions?

 

[gol4]

Why not do the test in the on enter event that way there would be not need to cancel an update. Correct the problem before it happens

 

[gol4]

To better clarify

Private Sub cmb_status_on Enter()
If IsNull(Me.CompleteDate) Then
MsgBox "Cannot update Status to Complete until Complete Date is populated."
docmd.gotocontrol "cmb_CompleteDate"
Else
If IsNull(Me.cmb_CompleteTime) Then
MsgBox "Cannot update Status to Complete until Complete Time to Complete is populated."
docmd.gotocontrol "cmb_CompleteTime"
End If
end if

End Sub

 

[jossimon]

This did not work either. The user enters the drop-down, and then selects Complete. On enter the field would not be = to Complete or StatusID=5. Any more ideas?

 

[PHV]

And what about doing the check in the BeforeUpdate event of the Combo playing with the Cancel parameter ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886