Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Test InputBox Results (Acc 2000 (9.0.4402) SR-1)

    The following function asks the operator to select options 1 through 3 from an InputBox

    If operator selects 1, nothing happens. However Debug.Print "strReturn: "; strReturn displays a value of 1

    If operator select 2, 3, Cancel, or enter nothing, the code works OK

    What am I missing?

    Thanks, John Graves

    =========================================
    Function DisplayResults(lngRecords As Long) As Boolean

    Dim strReturn As String
    Dim strMsg As String

    Select Case lngRecords
    Case 0
    strMsg = "No records matched the criteria you specified."
    MsgBox strMsg, vbExclamation, Application.Name
    lblRecordCount.caption = "Record Count: 0 "
    DisplayResults = True
    Exit Function
    Case 1
    strMsg = "1 record matched the criteria you specified."
    strMsg = strMsg & vbCrLf & vbCrLf & "Would you like to see it?"
    lblRecordCount.caption = "Record Count: 1 "
    Case Is > 1
    strMsg = lngRecords & " records matched the criteria you specified."
    strMsg = strMsg & vbCrLf & vbCrLf & "Would you like to SEE them?"
    lblRecordCount.caption = "Record Count: " & lngRecords
    End Select

    strReturn = InputBox(lngRecords & "records matched the criteria you specified." & _
    vbLf & vbLf & vbCr & _
    "Select your choice" & vbLf & vbLf & vbCr & _
    "1 = Full Record View" & vbLf & vbCr & _
    "2 = Data Sheet View" & vbLf & vbCr & _
    "3 = Print Report")

    Debug.Print "strReturn: "; strReturn

    'Check to see if condition entered. If none entered, present message box and exit sub
    If IsNull(strReturn) Or strReturn = " " Then
    strMsg = "Must enter a value, Select an item 1 through 3."
    MsgBox strMsg, vbExclamation, Application.Name
    Exit Function
    Else
    If srtReturn = "1" Then
    ' following code changes record source for form from tblBPermit to qryResultdBP
    DoCmd.OpenForm "frm_TOC_BP_ACD", WindowMode:=acHidden
    Forms!frm_TOC_BP_ACD.RecordSource = "qryResultsBP"
    Forms!frm_TOC_BP_ACD.Visible = True
    Else
    If strReturn = "2" Then
    ' following code changes record source for form from tblBPermit to qryResultdBP
    DoCmd.OpenForm "frmqrytblBPermitColSelectedMainForm", WindowMode:=acHidden
    Forms!frmqrytblBPermitColSelectedMainForm!frmqrytb lBPermitColSelectedSubForm.Form.RecordSource = "qryResultsBP"
    Forms!frmqrytblBPermitColSelectedMainForm.Visible = True
    Else
    If strReturn = "3" Then
    DoCmd.OpenForm "frmSelectReport", acNormal, acDialog
    Else
    If strReturn < "1" Or strReturn > "3" Then
    strMsg = "Select an item 1 through 3."
    MsgBox strMsg, vbExclamation, Application.Name
    Exit Function
    End If
    End If
    End If
    End If
    End If

    DisplayResults = True

    End Function
    =================================

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Test InputBox Results (Acc 2000 (9.0.4402) SR-1)

    I don't know about Access, but your nested Ifs are confusing *me*. I'd try a select case instead:

    'Check to see if condition entered. If none entered, present message box and exit sub

    Select Case strReturn

    Case Null, vbNullString
    strMsg = "Must enter a value, Select an item 1 through 3."
    MsgBox strMsg, vbExclamation, Application.Name

    Case "1"
    ' following code changes record source for form from tblBPermit to qryResultdBP
    DoCmd.OpenForm "frm_TOC_BP_ACD", WindowMode:=acHidden
    Forms!frm_TOC_BP_ACD.RecordSource = "qryResultsBP"
    Forms!frm_TOC_BP_ACD.Visible = True

    Case "2"
    ' following code changes record source for form from tblBPermit to qryResultdBP
    DoCmd.OpenForm "frmqrytblBPermitColSelectedMainForm", WindowMode:=acHidden
    Forms!frmqrytblBPermitColSelectedMainForm!frmqrytb lBPermitColSelectedSubForm.Form.RecordSource = "qryResultsBP"
    Forms!frmqrytblBPermitColSelectedMainForm.Visible = True

    case "3"
    DoCmd.OpenForm "frmSelectReport", acNormal, acDialog

    Case Else
    strMsg = "Select an item 1 through 3."
    MsgBox strMsg, vbExclamation, Application.Name
    End Select

    Now stick a breakpoint into the code at the Select Case statement and step through it to see what's happening. Do you have NoData code in the first report? Is it possible to open the first form from the user interface if you manually set its recordsource to that query?

    BTW, you don't need all those Exit Functions in your code. Just set a boolean variable to True before you enter the Select case and set it to false if you hit any errors along the way, like the Case Else. Then return that boolean as the value of the function. That eliminates "popping the stack" by jumping out of the middle of the code and still gives your return value the appropriate result.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Test InputBox Results (Acc 2000 (9.0.4402) SR-1)

    Hi Charlotte

    Thanks again for proper coding technique, everything works great.

    Case Null, vbNullString (generated RTE 94, Invalid use of null)

    Case Null (worked OK)

    Any thoughts?

    Also

    InputBox function provides OK & Cancel buttons.

    Cancel returns zero length string and vbNullString seems to pick this up.

    Is there any way to detremine if user clicks OK without entering a value?

    Thanks, John Graves

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Test InputBox Results (Acc 2000 (9.0.4402) SR-1)

    Create a separate case for vbNullString or "", which will handle the Cancel button and their hitting OK without entering anything. I usually supply a default value so if they just hit OK, that value gets returned.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Test InputBox Results (Acc 2000 (9.0.4402) SR-1)

    Hi Charlotte

    Perfect, following is completed InputBox code for interest to others:

    Last question, InputBox creates a response box the length of the message. Is there a way to force a 1 character response box?

    Thanks, John Graves

    ================================================== ====
    strReturn = InputBox(lngRecords & " Records matched the criteria you specified." & _
    vbLf & vbLf & vbCr & _
    "Select your choice:" & vbLf & vbLf & vbCr & _
    " 1 = Full Record View" & vbLf & vbCr & _
    " 2 = Data Sheet View" & vbLf & vbCr & _
    " 3 = Print Reports", "Enter your Choice", " ")

    Select Case strReturn

    Case vbNullString
    'strMsg = "Must enter a value, Select an item 1 through 3."
    'MsgBox strMsg, vbExclamation, Application.Name

    Case "1"
    DoCmd.OpenForm "frm_TOC_BP_ACD", WindowMode:=acHidden
    Forms!frm_TOC_BP_ACD.RecordSource = "qryResultsBP"
    Forms!frm_TOC_BP_ACD.Visible = True

    Case "2"
    DoCmd.OpenForm "frmqrytblBPermitColSelectedMainForm", WindowMode:=acHidden
    Forms!frmqrytblBPermitColSelectedMainForm!frmqrytb lBPermitColSelectedSubForm.Form.RecordSource = "qryResultsBP"
    Forms!frmqrytblBPermitColSelectedMainForm.Visible = True

    Case "3"
    DoCmd.OpenForm "frmSelectReport", acNormal, acDialog

    Case Else
    strMsg = "Select an item 1 through 3."
    MsgBox strMsg, vbExclamation, Application.Name

    End Select
    =============================================

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Test InputBox Results (Acc 2000 (9.0.4402) SR-1)

    <hr>Is there a way to force a 1 character response box?<hr>
    If you're talking about the visible length of the textbox, only by creating and using a custom form instead of the inputbox. However, you can use the Left() function to grab only the first character from the return value if that would help.
    Charlotte

Posting Permissions

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