Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inputbox Cancel (Access 2002)

    Hi

    Basic question, but the solution has me stumped.

    In a function for a module I have created an inputbox to prompt a user to add a number, which is used as a selection criteria for a report. No problems here. The issue is handling the error messages when either a user hits the cancel button for the inputbox or hits ok button but has not entered any value. The error handling appears to be the same for both, which is the problem as in the latter situation I wish to advise the user that they did not enter anything.

    So can anyone suggest code I could use to distinguish between the two events. The current code (cutdown version) is:

    Function PrintReport_IntakeIssue()
    On Error GoTo Err_PrintReport_IntakeIssue
    Dim PercentADE As Double
    PercentADE = InputBox("Enter value eg 50 for 50% or more of ADE")

    PercentADE = PercentADE / 100
    QueryResult1 = "SELECT qrynediactive_all.* "
    QueryResult1 = QueryResult1 + "FROM qrynediactive_all "
    QueryResult1 = QueryResult1 + "WHERE(qrynediactive_all.SumNZAverSTMR / qrynediactive_all.ADI_WHO) >= " & PercentADE & ";"
    ReportCaption1 = "Dietary Intake Values for Active Ingredients where Total Intake is >= " + Str(PercentADE * 100) + " of ADE"
    DoCmd.OpenReport "rptnediactive", acViewPreview

    Exit_PrintReport_IntakeIssue:
    Exit Function

    Err_PrintReport_IntakeIssue:
    If Err.Number = 13 And IsNull(PercentADE) Or PercentADE = 0 Then
    MsgBox "You have not entered a valid value, please try again"
    Call PrintReport_IntakeIssue
    ElseIf Err.Number = 13 Then
    Resume Exit_PrintReport_IntakeIssue
    Else
    MsgBox Err.Description
    End If
    End Function

    Many thanks & Regards
    WTH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox Cancel (Access 2002)

    If the user clicks Cancel in an input box, an empty string "" is returned. If the user doesn't enter anything and clicks OK, the result is the same. Since you have declared PercentADE as a Double, the empty string causes a problem. You could handle it as follows:

    Dim PercentADE As Double
    Dim strInput As String
    strInput = InputBox("Enter value eg 50 for 50% or more of ADE")
    If IsNumeric(strInput) = False Then
    MsgBox "You didn't enter a valid number.", vbExclamation
    Exit Function
    End If
    PercentADE = CDbl(strInput)
    ...

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox Cancel (Access 2002)

    Hi Hans

    Thanks for the answer. Unfortunately, it is not working. The new message box pops up for when a user enters nothing or when they click cancel. As stated in my earlier post, I only want the message box to occur when they don't enter a value and hit ok. Hitting the cancel button, just cancels the event. Perhaps I' m doing something wrong. Any pointers on what???

    Thanks & Regards
    WTH

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox Cancel (Access 2002)

    There is no way to distinguish between clicking Cancel and clicking OK without having entered anything. The InputBox returns an empty string "" in both cases without an indication as to the button clicked.

    If this is really important to you, you shouldn't use InputBox, but create a custom form with a text box, an OK button and a Cancel button. If the user clicks OK in this form, the On Click event of the OK button occursm and if the user clicks Cancel, the On Click event of the Cancel button occurs, so you know which one the user clicked.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox Cancel (Access 2002)

    Hi Hans

    Thanks for the advice, it solves the problem in that there is no way around it. I can live with this, I just presumed that there would be a way to distinguish between the two events.

    Regards and thanks for the prompt reply (as usual)

    WTH

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Inputbox Cancel (Access 2002)

    Actually, it is possible to distinguish between clicking Cancel and clicking OK without having entered any text - if you cheat by using "undocumented" VB StrPtr (as in String Pointer) function, which returns the address of the first character of a VB BSTR string (as opposed to VarPtr (Variable Pointer) function, which returns the address of a string (or other type) variable). Example:

    <code>Public Sub TestInputBox()</code>

    <code> Dim strInput As String</code>
    <code> strInput = InputBox("Enter Name:", "ENTER NAME DUMMY")</code>

    <code> If StrPtr(strInput) = 0 Then</code>
    <code> MsgBox "User clicked Cancel."</code>
    <code> Else</code>
    <code> If Len(strInput) = 0 Then</code>
    <code> MsgBox "User clicked OK, no text entered."</code>
    <code> Else</code>
    <code> MsgBox "User clicked OK, text entered."</code>
    <code> End If</code>
    <code> End If</code>

    <code>End Sub</code>

    Apparently clicking the OK button with no text results in empty string, but clicking Cancel results in equivalent of vbNullString. You can demonstrate distinction in Immediate window like this:

    ? StrPtr(vbNullString)
    0
    ? StrPtr("")
    1477276

    Note that the special vbNullString constant does not return an address, while an empty (zero-length) string does. For more info, see MSKB 205277:

    How to pass arrays and strings between Visual Basic and C functions or between Visual Basic and C++ functions by using Visual Basic 6.0

    Brief quote:

    "The StrPtr function returns the address of a Unicode string. The StrPtr function is used to distinguish between an empty string ("") and a NULL string (vbNullString). The function StrPtr("") returns the address of the memory location where the empty string is stored. However, the StrPtr(vbNullString) function returns zero."

    See article for additional arcane information.

    HTH

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox Cancel (Access 2002)

    Thanks, I didn't know that!

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Inputbox Cancel (Access 2002)

    I regret to inform you, your official title of "MyTechs Wizard" has been hereby revoked.... <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox Cancel (Access 2002)

    Where is that [humble] smilie when you need it? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  10. #10
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox Cancel (Access 2002)

    Hi

    Thanks for code, I will give it a go. I would have replied earlier, but the work firewall system seems to be blocking Woody's email automatic notification system.

    Regards
    WTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •