Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Drop Down Menu Not In List Custom Message (Access 2003)

    I have a Form that uses several drop down lists.

    The fields are not restricted to the list as some fields may temporarily change from time to time. In addition to temporary changes, the information is stored and personnel change. We do not want the information to change in the old records when the tables are updated.

    To enable the end user this capability, I need to create a custom error message to ask if they would like to update the table they are pulling the list from or continue with the information they have temporarily assigned to the field. I looked for references on this, but have only found ways to replace the messages built into access. Thanks for your help.

    Example--
    NAME: Jane Doe
    EMAIL: JaneDoe@email.com (field is auto filled based on the entry made in the NAME field)

    user enters "JaneDoe@travel.com" into the EMAIL field (a temporary email for a specific request)

    Error message: "You have entered a different email address than the one that is on file for this person, would you like to update this information in the personnel table?"
    IF Yes, Open Personnel Table, to this NAME record
    IF No, Return to form, and tab to the next feild

    (Lastly, I am not sure if it is possible, but once they have answered the question for the email address, is there a way to keep it from asking them every time they tab through the feild?)

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

    Re: Drop Down Menu Not In List Custom Message (Access 2003)

    I'm not sure I understand. It appears to me that you have a combo box (dropdown list) to select a name from, and a text box for the corresponding e-mail address. If that is correct, there will be no 'not in list' message if the user modifies the e-mail address, for 'not in list' applies to combo boxes only. Or have I misunderstood your description?

  3. #3
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu Not In List Custom Message (Access 2003)

    I think I may have stated it wrong. You helped me to make my fields auto fill the other day.
    When I insert the Building # for the Request I am submitting, my form automatically fills in the "NAME" and "EMAIL" for the person responsible for that building.
    There are times where one person is gone and someone else is filling in for there buildings.
    In these situations they will change the Name of the person responsible for the building for this specific request only.
    But I need it to prompt them for the change. If the name they enter is NOT in List, then I want them to add it to the personnel table. If the name they enter is in the list, but is not tied to the building they selected, then I want them verify they do not want to make a permanent change.

    I was going to make forms and call the forms from the Not in List Event, but I was hoping there was a way to build it into the table, without creating a bunch of forms.

    {Please let me know if this is more confusing, and I will make another attempt to explain, thanks for your help}

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

    Re: Drop Down Menu Not In List Custom Message (Access 2003)

    If the Limit to List property of a combo box has been set to No, the use can enter anything, and Access will not display any message if the entry doesn't occur in the list.
    If the Limit to List property of a combo box has been set to Yes, Access will *not* allow the user to enter a value that is not in the list. You can use the On Not in List event to display a customized Yes/No message box and ask if the user wants to add the value to the list.
    If the user answers Yes, you as programmer are responsible for adding the value to the appropriate table; Access will then update the list to include the new value and accept the entry.
    If the user answers No, the entry will not be accepted.

    I hope that you see the dilemma: you cannot use the On Not in List event *and* allow entries that are not list items.
    Instead, you should set Limit to List to No.
    Use the Before Update event to check whether the entry occurs in the list.
    If the entry does occur, you don't need to do anything special.
    If not, display a Yes/No/Cancel message box asking the user whether it should be added to the list.
    If the user answers Yes, add a record to the appropriate table and requery the combo box.
    If the user answers No, you don't need to do anything more.
    If the user answers Cancel, set Cancel = True to prevent the value from being updated.

    All this has to be done on a form, it cannot be done in the table itself.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu Not In List Custom Message (Access 2003)

    What's the difference between answering Cancel and answering No?

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

    Re: Drop Down Menu Not In List Custom Message (Access 2003)

    The user enters a value that is not in the list. The Yes/No/Cancel message box asks whether the new value should be added to the list.

    Yes = the new value is added to the list, and the value of the combo box is set to the new value.
    No = the new value is *not* added to the list, but the combo box is still set to the new value.
    Cancel = the new value is *not* added to the list, and the combo box is *not* set to the new value, i.e. the update is canceled, giving the user a chance to select a value from the list or to enter another value.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu Not In List Custom Message (Access 2003)

    Thank you Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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