Detect Change in Combo Box Selection 1

[Dave177]

Hello,

I have a combobox [cmbComplainee] which has as its Rowsource: ID, Name, Type.
Only Name and Type are visible and once a selection is made Name is visible in the combobox as selected, while ID is stored in the underlying table.
There are many different names but only two different types of Complainee and what I would like is if the user changes the selection so that the type changes, a message box displays (before the update occurs) warning the user that the type is about to be changed.
I have tried using a variety of things such as "on change" and oldvalue but can't seem to get it to work. Someone suggested using a form level variable that grabs the value of cmbComplainee.column(2) when cmbComplainee gets focus and then again beforeupdate and then comparing them.
Do you think this would work and how would you create such a form level variable?
Thanks for any advice.
David

 

[BradCollins]

in the 'onchange' event of the combo put the following code

    intresponse = MsgBox("You are about to update the record, are you sure ?", vbYesNo + vbQuestion, "Your DbName")
                    
    If intresponse = vbNo Then
        'Exit routine
        Else
            'make your change
    End If

 

[Dave177]

Hi Brad,
Thanks for your help.
Would this message appear whenever the combobox was changed or only if the third column (ie. Type) was different from what it was previously? Sorry, my original question wasn't too clear. The message should only appear if there is a change in Type. (There are many different complainees but only two types).

Thanks

David

 

[BradCollins]

This would appear every time there is a change in the combobox as with each new selection you are changing the entire row ie: ID, Name, Type

 

[TheAceMan1]

How are ya Dave177 . . .

You'll need two variables to track selections for this to work. Special Note: Type is considered to be [blue]text data type[/blue] in the code. If type is numeric ... changes have to be made.
[ol][li]In the declarations section of the form, copy/paste the following:

[blue]Private typOld As String, typSel As String[/blue]

[/li]
[li]Then in the [blue]AfterUpdate[/blue] event of the combo, copy/paste the following:

[blue]   Dim CBx As ComboBox, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   Set CBx = Me![[purple][b][i]YourComboboxName[/i][/b][/purple]]
   DL = vbNewLine & vbNewLine
   
   typOld = typSel
   typSel = CBx.Column(2)
   
   If typOld <> "" And typOld <> typSel Then
      Msg = "You are changing type from " & _
            "'" & typOld & "' to '" & typSel & "'." & DL & _
            "Are you sure you want to do this?"
      Style = vbQuestion + vbYesNo
      Title = "Change In Type Detected! . . ."
      
      If MsgBox(Msg, Style, Title) = vbNo Then
         typSel = typOld
         typOld = ""
         CBx = Null
      End If
   End If[/blue]

[/li]
[li]Be sure to rem or delete code in other events you've tried for this (prevents interaction!).[/li]
[li]Perform ypur testing . . .[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dave177]

Hi AceMan,

Thanks for your help but it is not working yet. My knowledge on Access is very patchy so you will have to bear with me.
Every time I make a change to the combobox the message box appears saying

You are changing type from '' to 'THE NEW TYPE'
Are you sure you want to do this?

So presumably typOld does not have an initial value. Should its value have been declared in the general declarations section under

Private typOld As String, typSel As String

eg. typSel = me.cmbmycombobox.column(2)

I tried this but got lots of errors about "on current event"
Thanks for your help on this.

David

 

[Dave177]

Hi Aceman,

Just to confirm, it works when it is not a new record but if it is a new record it has the message box as mentioned a above.

David

 

[PHV]

So, test the NewRecord property:

...
If Not Me.NewRecord Then
   If typOld <> "" And typOld <> typSel Then
...
   End If
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dave177]

Sorry, my mistake. If you open the form at a particular record then it seems that nothing is loaded into typOld and it does the message box as above. If however, you click yes you want to change it, then if you try to change it again it gives the warning as is required (i.e only when the type cbx.column(2) is about to change.
Also, if you click "No" to stop change it changes the cbx to "". Presumably you would have to store the cbx.column(0) somewhere too. I tried
me.undo
cancel = True
but it didn't work.

DAvid

 

[Dave177]

sorry, I'm being a complete idiot - must be from being away from work for a while.
Thank you both for your help.

 

[TheAceMan1]

[blue]If you open the form at a particular record then it seems that nothing is loaded into typOld and it does the message box as above.[/blue]

It sounds like your opening the form while it design view. You should close then open the form which initializes both variables. Note that on open of the form the initial selection sets the pace as there's no prior selection to compare with.

Post back the combobox code as you have it now.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dave177]

Hi Aceman,
Thanks for your help but the form is now behaving strangely.
I open the form by selecting a case from a listbox and pressing a button on a previous form.
When the form initially opens the combo box allows you to change from one type to another without displaying the message box at all.
However, any more changes between type does cause the message box to show.
Also...
How would I, instead of having:

      If MsgBox(Msg, Style, Title) = vbNo Then
         typSel = typOld
         typOld = ""
         CBx = Null
      End If

have CBx set to the previous value before change was made... If I try:

      If MsgBox(Msg, Style, Title) = vbNo Then
         typSel = typOld
         typOld = ""
         Cancel = True
         Me.undo
      End If

then the value of CBx goes back to that when the form was initially loaded. So if someone loaded the form with Complainee A (which is type 1) selected and then changed it to Complainee B (which is also type 1) but then changed it to Complainee Z (which is type 2) and clicked "No" it would go back to Complainee A and not Complainee B (which is what I would prefer).

Sorry for drawing this out a bit...

David