Help with AfterUpdate combobox

[gojets1721]

I have a combobox with three preset values (New, In Progress, Closed).

I want to have VBA where if the combobox's value is changed from 'Closed' to 'In Progress', then my date field (ClosedDate) is changed to null.

Any suggestions? I don't know how to specifically make an action happened when one specific value is changed to another (i.e. I want nothing to happen if the combobox is changed from 'New' to 'In Progress').

 

[The_Doc_Man]

You need to write event code for the Combo Box change event.

ComboBox.Change event (Access)

Office VBA reference topic

learn.microsoft.com

Inside the the combo's change event, you can compare the combo's .OldValue to its .Value and decide if the specific transition just occurred. If not a change from Closed to InProgress, do nothing.

 

[Pat Hartman]

I would put the code in the Form's BeforeUpdate event. That way you won't have to handle multiple changes to the field and trying to figure out if you need to bring back the date you just removed. The Form's BeforeUpdate event is the last event that runs before the record gets saved so the user won't have an opportunity to waffle

 

[gojets1721]

I would put the code in the Form's BeforeUpdate event. That way you won't have to handle multiple changes to the field and trying to figure out if you need to bring back the date you just removed. The Form's BeforeUpdate event is the last event that runs before the record gets saved so the user won't have an opportunity to waffle

Okay thanks. So I tried the below code in the BeforeUpdate. It's still changing it to 'In progress' even if I select 'no' from the msgbox. It seems like it's not voiding the user's change to the combobox. Do I need to code it to do that?

    Dim strResponse As String
 
    DoCmd.SetWarnings False

    If Me.comboComplaintStatus.OldValue = "Closed" And Me.comboComplaintStatus.Value = "In progress" Then
         
        strResponse = MsgBox("This complaint is closed. Are you sure you want to re-open it?", vbYesNo, ApplicationName)
             
        If strResponse = vbYes Then
                 
            Me.comboComplaintStatus = "In progress"
            Me.txtClosedDate = ""
               
        Else
            'do nothing
        End If
     
    End If

 

[cheekybuddha]

It's still changing it to 'In progress' even if I select 'no' from the msgbox. It seems like it's not voiding the user's change to the combobox. Do I need to code it to do that?

Yes, you must add Cancel = True within that section of the If block.

 

[gojets1721]

Yes, you must add Cancel = True within that section of the If block.

After the "else"?

 

[cheekybuddha]

No, before the 'Else'

Sorry, just re-read your code.

Yes, after the 'Else'

 

[gojets1721]

No, before the 'Else'

Sorry, just re-read your code.

Yes, after the 'Else'

Hmmm. I tried that and it's still not cancelling the change made to the combo

 

[cheekybuddha]

Sorry, also add Me.comboComplaintStatus.Undo

 

[gojets1721]

That worked! Thanks so much!

 

[cheekybuddha]

Glad you got there in the end!

 

[Pat Hartman]

You might find the videos here helpful

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk