Cancelling InputBox procedure - Part 2

[navi95]

Hi all,

so I have the following code which works fine when inputting the data etc:

Private Sub Umschaltfläche13_Click()
TempVars.Add "bestnr", InputBox("Bestellnummer Eingeben")
DoCmd.OpenReport "druck_liefer", acViewReport
DoCmd.PrintOut
DoCmd.Close
TempVars.RemoveAll
End Sub

The issue is that when I hit cancel or close the input box which appears, the code still runs which I do not want as the report then stays blank etc.

Thanks in advance!

 

[navi95]

I have found stuff like this:

Dim strInput As String
strInput = InputBox("Enter something:")
If StrPtr(strInput) = 0 Then
MsgBox "You pressed cancel!"
End If

That seems like what I need, but how do I apply it to my own code??

 

[Gasman]

Private Sub Umschaltfläche13_Click()
Dim strInput As String
strInput = InputBox("Bestellnummer Eingeben")
If StrPtr(strInput) > 0 Then
    TempVars.Add "bestnr", strInput
    DoCmd.OpenReport "druck_liefer", acViewReport
    DoCmd.PrintOut
    DoCmd.Close
    TempVars.RemoveAll
End If
End Sub

 

[The_Doc_Man]

First, be aware that StrPtr is an "undocumented" feature normally used to deal with passing strings from VBA code to compiled code in other languages (and passing them back, too). There are other xxxPtr functions for datatypes like application objects and maybe a few data-structure pointers. You get back an address from them. If you are using Access in any of its 32-bit flavors, the returned value is a LONG. If you use the 64-bit flavor of Access, the answer needs to have a QUAD to hold the return value, or else you could get a trap about an overflow.

Second, from the documentation I found, it is possible that you would get a pointer anyway (even if the call didn't work). I think the technically correct way to handle this is to build a subroutine call for which the inputs are passed in by reference. One variable is the string you would have passed in to the InputBox function to tell it the question to ask. Another variable would be the string to which you want to return the answer. You WANT this as a string so tht you can generalize the use of the subroutine. The third variable is the error code indicator.

You do it this way to protect the InputBox function by surrounding it with a local error trap that is inside the subroutine. That way you can do something constructive with failed results.

Public Sub AskMeSafely( ByRef sQues as String, ByRef sAnsw as String, ByRef lCode as Long)

Dim lErrCode as Long           'internal holder of error code if detected
Dim sInpAns as String          'either the answer or the error description

    On Error GotTo AskFail     'set the trap

    lErrCode = 0                   'assume the input will work
    sInpAnsw = InputBox( sQues )    'ask the question

GotAnAnswer:

    lCode = lErrCode              'return whatever code we have
    sAnsw = sInpAnsw           'and whatever answer we have
    Exit Sub

AskFail:

    lErrCode = Err.Number          'something went wrong, load the error code
    sInpAnsw = Err.Description   'and for giggles, return the error string
    Resume GotAnAnswer

End Sub

Don't hold my feet to the fire on this because I shot from the hip, but if this works as I think it would, you would call the subroutine before your line that starts with "TempVars.Add" and ask the question separately from the other operations. If the code comes back 0 then the input worked OK and your sAnsw string holds the answer. But if the code comes back non-zero then you had a CANCEL or some other error and the sAnsw is the error message. So you call the subroutine and follow it with an

    IF lCode <> 0 Then Goto SomeplaceElse

To use the subroutine, remember to create variables in your normal code to hold the values for these arguments since you are passing by reference. Set the values for the variable you will supply for sQues. Set the other string to "" (empty string) and set the variable for the lCode to be 0.

 

[pbaldy]

I simply test for an empty string, which is what hitting cancel returns if I remember correctly.

 

[gemma-the-husky]

OP. Why this thread, as you asked the same question on the first thread?

 

[The_Doc_Man]

Paul, I went through the gyrations because of the claim of getting a error trap firing when he cancels the input box without answering. The claim occurs in the other thread to which Dave refers.

 

[pbaldy]

Doc, I don't see that your code would get into the error trap. In my experience, and in a brief test just done, hitting Cancel or the "X" returns a ZLS. The test just done:

Dim strAnswer As String
strAnswer = InputBox("test")
Debug.Print strAnswer

which didn't error, no matter what I did with the input box. It returned a ZLS if I didn't enter anything.

 

[navi95]

Private Sub Umschaltfläche13_Click()
Dim strInput As String
strInput = InputBox("Bestellnummer Eingeben")
If StrPtr(strInput) > 0 Then
    TempVars.Add "bestnr", strInput
    DoCmd.OpenReport "druck_liefer", acViewReport
    DoCmd.PrintOut
    DoCmd.Close
    TempVars.RemoveAll
End If
End Sub

This worked like a dream! Thank you so much!

And everyone else too

 

[The_Doc_Man]

Paul, the only reason I went to that length is because the OP stated (in the other post) that he got a trap. Having tripped more than a few such traps myself, I know how to stop them from stopping me. That's all I was addressing.

 

[Gasman]

This worked like a dream! Thank you so much!

And everyone else too

Bear in mind what others have advised regarding undocumented.?

I found from your code that it returned 0 if nothing entered or cancelled and some large number if entered.

If testing for a zero length string does the job just as well, I would go that route.