Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2011
    Posts
    26

    Unanswered: combobox error problem

    Hi All,

    I have a simple navigation form with three things on it:

    1) a combobox that displays the concatenation of first and last names of people

    2) a "view person" button that goes to the demographics form of the person selected in the combobox

    3) a "new person" button that goes to a blank editable demographics form

    everything works fine if an existing person is entered into the combobox, but when someone who is not on the list is entered I am confronted with an error (this is expected). I can make an on error command that brings up a message and choices to enter the person if not found, but I don't know how to separate this error command to deal with the two buttons independently. The "view person" error response works fine as the message box is brought up, but I don't want this error to affect what the "new person" button does. I simply want the "new person" button to go to the editable demographics form and not respond to the error. Thanks for any input.

  2. #2
    Join Date
    Jun 2011
    Posts
    26
    I guess what I'm trying to do essentially is for the notinlist command to behave differently depending upon which button I am clicking.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Test for the error code in the error handling section:
    Code:
    If Err.Number = ... Then ...
    or
    Code:
    Select Case Err.Number 
        Case ...
        Case ...
        Case Else
    End Select
    Have a nice day!

  4. #4
    Join Date
    Jun 2011
    Posts
    26
    Thanks for the response Sinndho. I'm not sure how to direct the coding correctly once I find out the error code. Regardless of which button I click, I get the error code 2237 when I type in a name that is not in the list since my LimitToList property is set to True. If I create an If Then clause in the commandclick of the button, nothing seems to change (I'm not even sure if this is proper to do). I originally thought setting my LimitToList property to no would work, but it involved me showing part of the person ID number that I hid and ended up being more problematic with the other problems that it brought with it. Any other insight? Thanks.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't understand how clicking on one of the buttons comes into the perspective with the error 2237. This error occurs when you use the textbox part of a combo to type something that does not exist in its list part and that to LimitToList property is set to True (as you describe in your question). Could you post the offending code?
    Have a nice day!

  6. #6
    Join Date
    Jun 2011
    Posts
    26
    The point you brought up I believe is correct, the clicking of the button isn't the problem, it's that I don't know how to properly deal with the error from entering a name that is not recognized. When an unrecognized name is entered into the combobox and anything is done, I press enter, close, or either of the buttons, the 2237 error occurs. What I'm looking for is a way to ignore that error in most cases. That when I close the form or click new person, those commands are carried out without an error message, but when I click view person, and the person entered in the combobox doesn't exist, an error message comes up with a few options from there.

    Here is my coding for the form, its fairly simple as I've only been learning VBA for about 3 weeks:


    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Debug.Print "DataErr = "; DataErr
    End Sub

    Private Sub Combo36_NotInList(NewData As String, Response As Integer)
    intanswer = MsgBox("The person you entered is not in the database." & vbCrLf & _
    "Would you like to go to the add person form?" _
    , vbYesNo, "Person Not Found")
    If intanswer = vbYes Then
    DoCmd.OpenForm "demographics", acNormal, , , acFormAdd
    Else
    Response = acDataErrContinue
    End If
    End Sub

    Private Sub Command0_Click()
    DoCmd.OpenForm "demographics", acNormal, , , acFormAdd
    End Sub

    Private Sub Command1_Click()
    On Error GoTo errorresponse1

    DoCmd.OpenForm "demographics", acNormal, , , acFormReadOnly, , Me.Combo36
    errorresponse1:

    MsgBox "Please select a person from the list", vbInformation, "Person Not Found"
    Response = acDataErrContinue

    End Sub

    Thanks again for your time and help.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First and utmost, you should make the declaration of variables mandatory. To do so, open the VBA Editor then in the Tools menu select Options. In the Options window select the Editor tab and check "Require Variable Declaration" in the Code Settings section. This will automatically insert the following line in the Declarations section of every new Module:
    Code:
    Option Explicit
    When this option is turned on you have to explicitly declare every variable before using it and this feature will help you in avoiding very common but sometimes subtle errors.

    Second, when you decide to write an error handler, it means that this error handler must handle the error (duh!) and not do something then let the unhandled error free to propagate elsewhere in your code. With this code:
    Code:
    Private Sub Combo36_NotInList(NewData As String, Response As Integer)
    intanswer = MsgBox("The person you entered is not in the database." & vbCrLf & _
    "Would you like to go to the add person form?" _
    , vbYesNo, "Person Not Found")
    If intanswer = vbYes Then
    DoCmd.OpenForm "demographics", acNormal, , , acFormAdd
    Else
    Response = acDataErrContinue
    End If
    End Sub
    You do not handle the error properly.
    - If the answer to the MsgBox is Yes, you open another form and do nothing else --> the error is still there and the TextBox part of the combo still contains a value that's not in the List part.
    - If the answer is No, Response = acDataErrContinue means that you do not want Access to display the standard error message but again nothing else is done --> the error is still there and the TextBox part of the combo still contains a value that's not in the List part.

    The error is then trapped by the form error handler:
    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Debug.Print "DataErr = "; DataErr
    End Sub
    Again nothing is done to handle the error, apart from printing the error number in the Immediate window --> the error is still there and the TextBox part of the combo still contains a value that's not in the List part.

    Moreover, this is wrong too:
    Code:
    Private Sub Command1_Click()
    On Error GoTo errorresponse1
    
    DoCmd.OpenForm "demographics", acNormal, , , acFormReadOnly, , Me.Combo36
    errorresponse1:
    
    MsgBox "Please select a person from the list", vbInformation, "Person Not Found"
    Response = acDataErrContinue
    
    End Sub
    After the form "demographics" is open, the code will continue its execution and the message box "Please select a person..." will be displayed (and if an error occured it will not be handled nor cleared). This part should be:
    Code:
    Private Sub Command1_Click()
    
        On Error GoTo errorresponse1
    
        DoCmd.OpenForm "demographics", acNormal, , , acFormReadOnly, , Me.Combo36
    
    Exit_Command1_Click:
        Exit Sub
        
    errorresponse1:
        If Err.Number = 2237 Then
            MsgBox "Please select a person from the list", vbInformation, "Person Not Found"
            '
            ' Response = acDataErrContinue --> This does nothing: Response is not declared in this scope
            '                                  and will cause an error with Option Explicit turned on.        
            '
            Me.Combo36.Undo             '  --> Remove the cause of the error.
            Err.Clear                   '  --> Reset the Err object.
        Else
            ' Do something to handle other errors.
        End If
        Resume Exit_Command1_Click
        
    End Sub
    Here's a functional modified version of your code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Combo36_NotInList(NewData As String, Response As Integer)
    
        Dim intanswer As Long           ' Properly declare the variable.
        
        Response = acDataErrContinue    ' Do not display the standard Access error message.
        Me.Combo36.Undo                 ' Cancel the entry --> remove the cause of the error.
        intanswer = MsgBox("The person you entered is not in the database." & vbCrLf & _
                            "Would you like to go to the add person form?" _
                            , vbYesNo, "Person Not Found")
        If intanswer = vbYes Then DoCmd.OpenForm "demographics", acNormal, , , acFormAdd
        
    End Sub
    
    Private Sub Command0_Click()
    
        DoCmd.OpenForm "demographics", acNormal, , , acFormAdd
        
    End Sub
    
    Private Sub Command1_Click()
    
        DoCmd.OpenForm "demographics", acNormal, , , acFormReadOnly, , Me.Combo36
    
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Jun 2011
    Posts
    26
    I really can't say thank you enough. As a beginner I'm reading a lot and doing tons of trial and error, but unfortunately I not always sure if if I'm even in the ballpark of how to correctly handle some of the coding. Thank you for setting me on the right path and taking the time to help out a complete stranger. This is the most helpful forum I've ever come across.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome
    Have a nice day!

  10. #10
    Join Date
    Jun 2011
    Posts
    26
    One more thing. When I click "new person," command0, and an unrecognized name is entered into the combobox, I am directed to the message box listed under the combo36_notinlist coding. I tried the if err.number command and placed it inside the command0 statements but no luck. I understand why this occurs, but is there anyway to bypass it and allow me to go to the add new demographics page regardless of whatever is typed into the combobox? Thanks

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It can be done but not easily. The problem is that the NotInList error (or event if you prefer) occurs before the combo loose the focus (then before Command0 receives it). At that moment you have no easy way of knowing that the user clicked the Command0 button. You could try to use the MouseMove event of Command0 but this would yield a terribly complex code.

    A rather easy solution consist in delaying part of the treatment of the NotInList event (or error), namely the part asking the question. This implies using another prodecure to handle this part (i.e. ask the question and open the input form if the answer is Yes). The NotInList event will raise a flag (m_booNotInList) that must be checked by every control on the form that can receive the focus and call the appropriate procedure (i.e. the one that asks the question and open the input form if the answer is Yes) when the flag is raised (i.e. m_booNotInList = True). Here's how it can be done:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_booNotInList As Boolean   ' Member variable used as a flag set to True when the NotInList error occurs.
    
    Private Sub Combo36_NotInList(NewData As String, Response As Integer)
    
        Me.Combo36.Undo                 ' Cancel the entry --> remove the cause of the error.
        Response = acDataErrContinue    ' Do not display the standard Access error message.
        Err.Clear                       ' Clear the Err Object.
        m_booNotInList = True           ' Set the NotInList flag to True.
        
    End Sub
    
    Private Sub Command0_Click()
    
        ' The NotInList error case is processed locally because no
        ' question needs to be asked as the form is systematically open.
        '
        m_booNotInList = False          ' Systematically reset the NotInList flag to False
                                        ' (don't bother to test as no question will be asked).
        DoCmd.OpenForm "demographics", acNormal, , , acFormAdd
        
    End Sub
    
    Private Sub Command1_Click()
    
        ' Testing for the NotInList error case must be done
        ' for every control that can receive the focus.
        '
        If m_booNotInList = True Then   ' Process the NotInList error case if necessary.
            Handle_NotInList
        Else
            DoCmd.OpenForm "demographics", acNormal, , , acFormReadOnly, , Me.Combo36
        End If
    
    End Sub
    
    Private Sub Handle_NotInList()
    
        Dim intanswer As Long           ' Properly declare the variable.
    
        m_booNotInList = False          ' Reset the NotInList flag to False.
        If MsgBox("The person you entered is not in the database." & vbCrLf & _
                  "Would you like to go to the add person form?" _
                  , vbYesNo, "Person Not Found") = vbYes Then
            DoCmd.OpenForm "demographics", acNormal, , , acFormAdd
        End If
    
    End Sub
    The only problem is that this won't prevent the list from dropping down.

    Another far more complex solution would consist in setting the LimitToList property of the combo to False, thus disabling the NotInList event, and check the value entered in the text part of the combo (in the BeforeUpdate event handler) to determine whether this value is in the list or not (in doing so we create our own NotInList pseudo-event) and take the proper actions accordingly.
    Have a nice day!

  12. #12
    Join Date
    Jun 2011
    Posts
    26
    Oh yes, I see what you mean about the combobox still opening up after the error. I think what I'm going to do is get rid of the new person button and just have the user type in the name. If its in the system it'll go to that page, if not they'll get the message box asking if they would like to add them. Thanks for your help, I wouldn't have gotten this far without it.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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