Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56

    Question Unanswered: ComboBox NotInList Event

    Hello again people,

    I am trying to code a NotInList event for a combo box on a form.

    (The form is called "frmPROJECTS2" and the name of the combo box is "cboChiefID".

    When the user tries to enter a value that is not on the list I want a message box to come up as follows:

    "The name you have entered is not in the list. Would you like to add it?"

    If the user clicks on "Yes" then I want to launch a form called "frmAddPerson"

    If the user click on "No" then I want to clear the entry in the combobox

    Any ideas? I've tried several different pieces of code but without any luck – I just get loads of error messages, and even when I do get "frmAddPerson" to launch and add the entry from there, I close the form to go back to "frmPROJECTS2" and I then get the cr@ppy default message "the text you entered isn't an item on the list…"

    Is there a way of clearing the entry in the combobox and then launching the form called "frmAddPerson"?

    ================================================== ==
    NB – I am doing it this way because the combo box list is a list of names and displays surname, forename and title, but returns a unique person id number. So it has about 12 different "Jones" or "Smith" listed in there but not necessarily "Bob Jones" or "Lee Smith", Therefore the frmAddPerson enables the user to enter these values in one go.
    ================================================== ==

    Please help,

    Rusty

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    What you have to do is requery the combo box after you finish adding the person so you dont get the same message again.

    Have you tried that already?

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Might I suggest that you do a search in this forum for NotInList ... Izy and I have posted code on how to "safely" handle the NotInList event so that it doesn't blow up in your face ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56

    Unhappy Where is it?

    I've tried searching for the code on this forum using "NotInList" but no luck I'm afraid. Could you post the link or the code itself?

    Rusty

  5. #5
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125

  6. #6
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56
    Quote Originally Posted by JSThePatriot
    What you have to do is requery the combo box after you finish adding the person so you dont get the same message again.

    Have you tried that already?

    JS
    Kind of yeah...

    The code below works beautifully if yes is clicked and new person is added in the "frmAddPerson", but then when I go back into "frmProjects2" the standard error msg box comes up. This also happens if the user has selected "No" as an option even though I have used some 'Undo' code and I even tried the 'Requery' code here too but with no success - the standard error msg box comes up again...

    Any ideas?

    Rusty

    ================================================== =======
    Private Sub cboOverall_Lead_Investigator_ID_NotInList(NewData As String, Response As Integer)

    Const Message1 = "The name you have entered is not in the list."
    Const Message2 = "Would you like to add it?"
    Const Title = "Unknown entry..."
    Const NL = vbCrLf & vbCrLf
    ' show message box and evaluate if the user has selected Yes or No
    If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then

    cboOverall_Lead_Investigator_ID.Undo ' clear the entry in the combobox
    cboOverall_Lead_Investigator_ID.Requery
    DoCmd.OpenForm "frmAddPerson", acNormal, "qryAddNewPerson", "", acEdit, acDialog

    Else
    cboOverall_Lead_Investigator_ID.Undo ' clear the entry in the combobox
    End If
    End Sub

  7. #7
    Join Date
    Jul 2003
    Posts
    73
    I've done pretty much exactly the same thing you're trying in a form of mine. The key is to set the Response variable. I would change your code as follows:

    ================================================== =======
    Private Sub cboOverall_Lead_Investigator_ID_NotInList(NewData As String, Response As Integer)

    Const Message1 = "The name you have entered is not in the list."
    Const Message2 = "Would you like to add it?"
    Const Title = "Unknown entry..."
    Const NL = vbCrLf & vbCrLf
    ' show message box and evaluate if the user has selected Yes or No
    If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then

    'cboOverall_Lead_Investigator_ID.Undo ' clear the entry in the combobox
    'cboOverall_Lead_Investigator_ID.Requery

    DoCmd.OpenForm "frmAddPerson", acNormal, "qryAddNewPerson", "", acEdit, acDialog
    Response = acDataErrAdded
    Else
    'cboOverall_Lead_Investigator_ID.Undo ' clear the entry in the combobox
    Response = acDataErrContinue
    End If
    End Sub

    Look on the web for the NotInList method for more information - but here's some basic info for the Response variable:

    acDataErrDisplay (default) - let Access display an error to the user

    acDataErrContinue - allows you to show a custom message (and asks if they want to add the item to the list). If they select no - set this response field. This will then make them re-enter an entry.

    acDataErrAdded - As above - but when they select yes you set this as the response field. Surpresses any error message - and sets the combo to the added value. If you didn't add the value properly when they selected yes - an error message is displayed.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

Posting Permissions

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