CODEWITHSUNDEEP
ms-accessvba
scubydu671
scubydu671

Reputation: 5

Allow or disallow value change in combo box based on initial value to new value

I am trying to write code for a data certification status combobox that will allow or not allow the user to change the status based on specific guidelines:

  1. User may change status from Raw to Clean, no restriction
  2. User may change status from Clean to Certified, no restriction
  3. User may change status from Raw to Certified after answering yes to verify all necessary QA/QC has been done
  4. User may change status from Clean to Raw, if answers yes to proceed and provides explanation in "Comments" field
  5. User may not change status from Certified to Clean, or from Certified to Raw

I assume I would use a Select statement where Case 1 = Raw (ID=1), Case 2 = Clean (ID=2), Case 3 = Certified (ID=3). The IDs 1, 2, and 3 are the ID values in the lookup table for the data certification status'. What I'm struggling with is how I set the "Before" value to compare to the "Current" value, when the user has already selected a different value.

Also, I'm using the "OnChange" event. But wondering if "BeforeUpdate" or "OnDirty" events would be better.

Any help would be greatly appreciated.

Upvotes: 0

Views: 63

Answers (1)

Amr Aly
Amr Aly

Reputation: 26

use "BeforeUpdate" Event on combox

If Me.Combo3.OldValue = 1 And Me.Combo3.Value = 2 Then
    Dim x
    x = MsgBox("all necessary QA/QC has been done? ", vbYesNo)
    If x = vbNo Then
    MsgBox "any thing you want to write"
    Cancel = True
    End If

End If

Upvotes: 1

Related Questions