Subform - Limit combo Box selection based on previous entry

[Guest]

I have a main form which contains a subform. The subform’s table is a many
to one relationship to the main form’s table. The subform tracks the status
actions for each record (loan request) in the main form. I need a way to
force the user to select the status action In Process if the previous action
for that request was On Hold. The status actions are selected from a combo
box. Currently the user will sometimes select the status Recommend or
Approve after putting the request On Hold but it needs to go back to In
Process before it can be recommended, approved, or whatever.

Your assistance is much appreciated.

Thank you.

 

[Guest]

There's probably more than one way to accomplish what you want, and maybe
someone else will reply with a better solution, but here is something you can
try.

Add an unbound checkbox to your subform. Set it's visible property to no (so
your users won't see it) and it's default propery to false. Now, in the After
Update event of your status combo box set the checkbox to true if "On Hold"
is selected.

If Me.StatusCombobox = "On Hold" Then

Me.Checkbox = True

End If

Next, in the Before Update event of the combo box, verify if the checkbox is
true (which would mean that the current value of the combo box is "On Hold").
If so, limit the choice to "In Process".

If Me.Checkbox = True Then

If Me.StatusCombobox <> "In Process" Then

MsgBox "You must select 'In Process' at this time", vbOKOnly, "Invalid
Selection"
Cancel = True 'cancel the update
Me.StatusCombobox.SetFocus 'set focus back to the combo box

Else

Me.Checkbox = False '(the user selected "In Process" so reset the
checkbox to False so other selections can be made after this)

End If
End If


You might need to play with it a little to make it work in your DB, but it's
one approach you could try

HTH

 

[Guest]

Private Sub cboStatus_BeforeUPdate(Cancel As Integer)
If Me.cboStatus.OldValue = "On Hold" Then
MsgBox "Only In Process allowed when changing from On Hold",
vbExclamation
Cancel = True
Me.cboStatus.Undo
End If
End Sub

 

[Guest]

Well, that's a lot easier than my solution, but isn't it going the fire the
message box and cancel the event even if the user does select "In Process"?
Maybe it should be

Private Sub cboStatus_BeforeUPdate(Cancel As Integer)
If Me.cboStatus.OldValue = "On Hold" Then
If Me.cboStatus <> "In Process" Then
MsgBox "Only In Process allowed when changing from On Hold",
vbExclamation
Cancel = True
Me.cboStatus.Undo
End If
End If
End Sub

or something like that.

 

[Guest]

You are correct.

 

[Guest]

Thank you. I tried the other suggestion but could not get it to work.
Nothing happened. So tried this approach and it works just fine. Only
problem I had was I was not able to set the checkbox visable property to no.
Well, I could set it to no but it was still visable. Not sure if that is due
to the subform being set to datasheet view. At any rate I just put the
checkbox on the main form made the appropriate changes to the code and it
works fine.

Thank you again for the help and for such a speedy reply.