Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not in List property problem (Access 97 Win 2k)

    Hello again

    I've set up a form to input new quote request data, one of the things I'm trying to handle is the addition of customers who the company has not done business with before. The system uses a combo box to make all the currently known customers available, where a customer is not known I've set the combo box 'not in list' event property to open a form for the user to add the customers details. With the setwarnings argument sorted out the form opens and allows the required data to be added to the customer table. The problem occurs when the form is closed, an error message pops up informing me that the item typed into the combo box is not in the list (I know this, it's why the form opened :-) ). The only way the add customer form will close is with a basic close statement (macro or VBA), but it leaves the Access error message, and I then have to delete the 'wrong' entry and press F9 to requery the combo box to get the new customer details to appear.

    As the system will be used in a fairly busy environment I'd like to make this happen automatically, without the error message etc. What should I be doing to smooth the operation out?

    I've tried using the setwarnings method in a VBA module to close the form (setwarnings false), and as an alternative, in a macro. Neither work unless the combo box contents are deleted so is there a way I can delete the contents of the combo box automatically, a 'set to null' statement or something similar?

    Thanks

    Ian

  2. #2
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in List property problem (Access 97 Win 2k)

    Doh!!!

    As I closed the first post I saw the requery post on this page, I think it may solve my problem. If not I'll post again later. Wonder why it didn't show up in my searches for not in list, notinlist?

    Ian

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

    Re: Not in List property problem (Access 97 Win 2k)

    The thread on Requery started by gsmith is not necessarily relevant to your problem. George not only wants to add an item to the row source of the combo box, but to the record source of the form too, in one action.

    If you want to add an item to the row source of the combo box, you need to set the Response argument of the NotInList event procedure to acDataErrAdded. This tells Access that the row source has been updated. The combo box is then requeried automatically. See <post#=146637>post 146637</post#> for an example.

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in List property problem (Access 97 Win 2k)

    Hans

    Thanks for the quick reply, the first reply in the requery post pointed me in the right direction, I set the form to dialogue mode and then put the 'response' line into my VBA module. This now works fine.

    I've looked at the linked post you put up and it would work fine, I think, except that I'm adding other information as well, customer's name, contact name, contact phone number, address etc. This information is needed later when the actual quote is produced from the system or Sales wont know who to send the thing to :-)

    Thanks again

    Ian

Posting Permissions

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