select case not working as i thought

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

when my user wants to change a valid value in a combobox to another i want
him to have a chance to abort it. i wrote this code so that when the value of
IRB_Number is not zero (0) -- at which point we know that other than the
default of zero is in the control --- then if/when the user picks 'No' from
the msgBox pair of choices, i want the original value to remain in the
control. what seems to be happening is that even when 'No' gets selected, the
new value gets entered into the control.

Private Sub IRB_Number_AfterUpdate()
Dim intAnswr As Integer
If Me.IRB_Number = 0 Then
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = ""
Me.Title.Requery
Else
intAnswr = MsgBox("You are about to change this protocol number from #" &
Me.IRB_Number.OldValue _
& " to #" & Me.IRB_Number & "." & Chr(13) & "This will change it EVERYWHERE
THROUGHOUT the database!" & Chr(13) & "Do you wish to continue?", vbCritical
+ vbYesNo + vbDefaultButton2, "Consider this!!")
Select Case intAnswr
Case vbYes
MsgBox "Remember to change the Title!", vbExclamation, "Important"
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = "None"
Me.Title.Requery
Case vbNo
GoTo Line1
End Select
End If
Line1:
End Sub
 
I think the problem is you are not backing out the change. First, this
should probably be in the Before Update event and you need to put Cancel =
True just before the GoTo Line1 statment in the No condition. Also, in this
case, an If statement would be better than the Select Case. The Select Case
is best used if more than 2 options based on the same condition are required.
for example:
Lets say intInfo should be a value from 1 to 5, but may not always be.

Select Case intIno
Case is = 1
Do whatever for 1
Case is = 2
Do whatever for 2
Case is 3 to 5
Do whatever for 3, 4, or 5
Case Else
Msgbox "IntInfo Must Be 1 thru 5"
End Select

I have taken the liberty of modifying your code:

Private Sub IRB_Number_AfterUpdate(Cancel As Integer)
Dim intAnswr As Integer

If Me.IRB_Number = 0 Then
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = ""
Me.Title.Requery
Else
intAnswr = MsgBox("You are about to change this protocol number from
#" &
Me.IRB_Number.OldValue _
& " to #" & Me.IRB_Number & "." & Chr(13) & "This will change it
EVERYWHERE
THROUGHOUT the database!" & Chr(13) & "Do you wish to continue?",
vbCritical
+ vbYesNo + vbDefaultButton2, "Consider this!!")
If intAnswr = vbYes Then
MsgBox "Remember to change the Title!", vbExclamation, "Important"
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = "None"
Me.Title.Requery
Else
Cancel = True
Exit Sub
End If
End If

End Sub

Try to avoid the Goto. Gotos are the evil invention of twisted demons. It
there is code that needs to executed after the last End If, then instead of
the Exit Sub or a Goto, set a Boolean field to control whether the rest of
the code should be executed.
....
Else
Cancel = True
blnGotANo = True
End If
End If
If Not blnGotANo then ' The answer was Yes
'Do Yes Only Code
Else
'Do No Only Code
End If
'Do code for Yes or No
End Sub

Good Luck
 
klatuu, thanks and i'm pretty sure you're on base.....what i put together in
the interim since posting mine is as follows (and it almost seems to work):

Private Sub IRB_Number_AfterUpdate()
Dim intAnswr As Integer
If Me.IRB_Number = 0 Then
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = ""
Me.Title.Requery
ElseIf Me.IRB_Number.OldValue <> 0 Then intAnswr = MsgBox("You are about to
change this protocol number from #" & Me.IRB_Number _
..OldValue & " to #" & Me.IRB_Number & "." & Chr(13) & "This will change it
EVERYWHERE THROUGHOUT the database!" & Chr(13) & "Do you wish to continue?",
vbCritical + vbYesNo + vbDefaultButton2, "Consider this!!")
Select Case intAnswr
Case vbYes
MsgBox "Remember to change the Title!", vbExclamation, "Important"
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = "None"
Me.Title.Requery
Case vbNo
Me.IRB_Number = Me.IRB_Number.OldValue
End Select
End If
End Sub

as you can see i've brought in the Oldvalue which i'm using to remember what
i'm changing the IRB number 'from' 'to' and to restore it in the case of
vbNo.

thanks lots for the bandwidth.

-ted
 
The OldValue is a good idea.

Ted said:
klatuu, thanks and i'm pretty sure you're on base.....what i put together in
the interim since posting mine is as follows (and it almost seems to work):

Private Sub IRB_Number_AfterUpdate()
Dim intAnswr As Integer
If Me.IRB_Number = 0 Then
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = ""
Me.Title.Requery
ElseIf Me.IRB_Number.OldValue <> 0 Then intAnswr = MsgBox("You are about to
change this protocol number from #" & Me.IRB_Number _
.OldValue & " to #" & Me.IRB_Number & "." & Chr(13) & "This will change it
EVERYWHERE THROUGHOUT the database!" & Chr(13) & "Do you wish to continue?",
vbCritical + vbYesNo + vbDefaultButton2, "Consider this!!")
Select Case intAnswr
Case vbYes
MsgBox "Remember to change the Title!", vbExclamation, "Important"
Me.IRB_Number.Requery
Me.Title.SetFocus
Me.Title = "None"
Me.Title.Requery
Case vbNo
Me.IRB_Number = Me.IRB_Number.OldValue
End Select
End If
End Sub

as you can see i've brought in the Oldvalue which i'm using to remember what
i'm changing the IRB number 'from' 'to' and to restore it in the case of
vbNo.

thanks lots for the bandwidth.

-ted
Click to expand...
 
Back
Top