How do I use VBOKCancel correctly? 3

[testkitt2]

Hello Group

The code below works but if you hit cancel at any time it does not exit the sub, the form just fires up. Any suggestions on how to correct this.. It's probably simple for coders ..but I'm not one.

Private Sub Location_AfterUpdate()
 Dim stDocName As String, cancel As Boolean
 Dim answer1 As Integer
 Dim answer2 As Integer
 Dim stDocNameNew As String
 Dim stLinkCriteria As String
    If Not IsNumeric(Me![Location]) And Not IsNumeric(Me![Location].OldValue) Then
    Exit Sub
    End If
    
    If IsNumeric(Me![Location].OldValue) Then
    MsgBox " Remove Old Position First !", vbOKCancel
     stDocName = "FrmTirePosition"
     stLinkCriteria = "[Fleet Number]=" & "'" & Me![Location].OldValue & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    ElseIf answer1 = vbCancel Then
  cancel = True
  Exit Sub
      ElseIf IsNumeric(Me![Location]) Then
    MsgBox "Add New Tire Position ? ", vbQuestion + vbOKCancel, "DB Admin"
        stDocNameNew = "FrmTirePosition"
        stLinkCriteria = "[Fleet Number]=" & "'" & Me![Location] & "'"
    DoCmd.OpenForm stDocNameNew, , , stLinkCriteria
    ElseIf answer2 = vbCancel Then
  cancel = True
  Exit Sub
    End If
   
End Sub

Thanks for any help on this one.
JZ

Testkitt2

 

[PHV]

Tip: Where are supposed answer1 and answer2 to be assigned a value ?
Another tip: olace the cursor inside the MsgBox word in your code and press the F1 key.

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

 

[testkitt2]

Thank PHV
For you input.
Normally if I use a Msgbox..such as example below

Dim answer As Integer
answer = MsgBox("Click ' YES ' If You Want To Quit This Program," & vbNewLine & " or ' NO '...To Cancell.", vbQuestion + vbYesNo, "Shop Dept")
If answer = vbYes Then
   Application.Quit
    Else
    
Exit_CmdCloseBacklog_Click:
    Exit Sub

The use of multiple "IF" statements is what has me confused.
I want to be able to exit the sub within any of the "If" statements in my original post by clicking "Cancel"
Thank you
JZ

Testkitt2

 

[testkitt2]

leaving work...will log on from home.
Thank you
JZ

Testkitt2

 

[testkitt2]

Hello to all
can anyone help on this post.

"How do I use VBOKCancel correctly?"

Thank you
JZ


Testkitt2

 

[PHV]

can anyone help on this post
Did you make any sense with my answer posted 26 Jul 07 16:47 ?

 

[Melagan]

The users response from a MsgBox returns an integer value to VB - you can then do an action based on the integer MsgBox returns. As PHV suggested, check out VBHelp by putting your cursor somewhere on the word MsgBox and hit F1.

A starting point, though:

Dim intAnswer As Integer
  intAnswer = Eval(MsgBox("Testing, Click OK for Cancel.", vbOKCancel, "Test"))
'User clicks OK = 1
'User clicks Cancel = 2
    If intAnswer <> 1 Then
        Exit Sub
    Else
'       ...
    End If

~Melagan
______
"It's never too late to become what you might have been.

 

[tigersbh]

You can also do it like this:

Public Sub TestVbOKCancel()
intAnswer= "Testing, Click OK for Cancel.", vbOKCancel, "Test")
If intAnswer= vbOK Then
'do this
Else
'do this instead (intAnswer=vbCancel)
End If
End Sub

 

[testkitt2]

Thanks
PHV ,
Melagan ,
tigersbh ,for your assist on this..
I tried PHV suggest "F1" (help) but was unclear upon reading inst and examples.
***********
I tried to change part of the code abit...as shown below.

If IsNumeric(Me![Location].OldValue) Then
    answer1 = MsgBox(" Remove Old Position First !", vbOKCancel)
     If answer1 = vbCancel Then
    Exit Sub
     stDocName = "FrmTirePosition"
     stLinkCriteria = "[Fleet Number]=" & "'" & Me![Location].OldValue & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

and it almost worked...
I will try all sugg given as I can only rely on TekTip members for help.
My VB is needs much help.
Thanks to all
I will post results.
Thanks again
JZ


Testkitt2

 

[PHV]

Be aware that any instruction just below an Exit Sub statement will never be execute ...

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

 

[Melagan]

In VB (and any other language I've worked with), IF statements follow this pattern.

IF..THEN..ELSE..END IF

You are missing the END IF in your IF statement.

'psudo code
IF this = that THEN
  Do some stuff
ELSE
  Do some other stuff
END IF
  Continue with code.

So...

[COLOR=purple]If[/color] IsNumeric(Me![Location].OldValue) Then
  answer1 = MsgBox(" Remove Old Position First !", vbOKCancel)
  [COLOR=blue]If[/color] answer1 = vbCancel Then
    Exit Sub
  [COLOR=blue]End If[/color]
[COLOR=purple]End If[/color]
stDocName = "FrmTirePosition"
stLinkCriteria = "[Fleet Number]=" & "'" & Me![Location].OldValue & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Alternatively, just to add to the confusion, you could write your nested IF line like this:

[blue]If[/blue] answer1 = 1 Then Exit Sub
..
..
etc.

If you write your IF statement in one line like that, you do not need an End If line - furthermore, the instruction after your criteria will execute only if the if statement evaluates. In this case, if answer1 = 1 then it will exit the sub. If answer1 <> 1 (not equal to 1) then whatever follows will be ignored.

Am I right, or is this another version of VB?

~Melagan
______
"It's never too late to become what you might have been.

 

[TheAceMan1]

How are ya testkitt2 . . .

Here's an Idea of what the program flow should look like:

[blue]   If Not IsNumeric(Me![Location]) And Not IsNumeric(Me![Location].OldValue) Then
      Exit Sub
   ElseIf IsNumeric(Me![Location].OldValue) Then
      If MsgBox(" Remove Old Position First !", vbOKCancel) = vbOK Then
         stDocName = "FrmTirePosition"
         stLinkCriteria = "[Fleet Number]=" & "'" & Me![Location].OldValue & "'"
         DoCmd.OpenForm stDocName, , , stLinkCriteria
      End If
   ElseIf IsNumeric(Me![Location]) Then
      If MsgBox("Add New Tire Position ? ", vbQuestion + vbOKCancel, "DB Admin") = vbOK Then
         stDocNameNew = "FrmTirePosition"
         stLinkCriteria = "[Fleet Number]=" & "'" & Me![Location] & "'"
         DoCmd.OpenForm stDocNameNew, , , stLinkCriteria
      End If
   End If[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[testkitt2]

Thanks
PHV ,
Melagan ,
AceMan1

Since I'd like to give the person who will be using this DB at my job besides myself options to cancel are necessary. The many If's, Then's, Else and ElseIf's just got a bit too much for my bit of experience. All of your suggestions did work. I appreciate all of your help..

The AceMan1's suggestion of how the code should flow did exactly what I need it to do and it was what I was trying to explain in my first post.
Again many thanks to the members of TekTips...you guys always come thru..
This post is done at least for me.
Thanks to all
JZ

Testkitt2