Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2004
    Posts
    13

    Red face Unanswered: New to NotInList Event

    I have read the forums and it sounds like I need to use the NotInList event. I am new to Access and I have been learning these codes as I go.

    Could someone please talk me through how to make this process work. (i.e. what the code should be and where to enter it.)

    I have an "Inventory" form.

    I have a combo box that is linked to a "Unit Price" table for Item Description and Unit Price. When I pick an "Item" it automatically fills in the "Unit Price". Great! (I have a total of 4 of these combo boxes.) I would like the user to be able to edit or add to the "Unit Price" table without exiting the form by entering the required information into any of the Combo or Unit Price boxes.

    Please help. I am lost.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I would put code like this for your item field:

    Private Sub Item_NotInList(NewData As String, Response As Integer)
    MsgBox "You Must select a value in the list!", 48, "Search Error"
    Response = acDataErrContinue 'don't display not in list message
    End Sub

    I also somtimes ask if it should be added to the list. If so, I do an insert query and I would do item.requery so it is now in the list.

  3. #3
    Join Date
    Aug 2004
    Posts
    13
    To anyone as clueless as me at Access:

    Apparently there is a box called "On Not In List" under the Events tab in your properties for the selected Combo Box.

    If you click the builder button next to this "On Not In List" box you can use the "Code Builder". This opens a box where you can put all that code garbage that everyone has been writing about.

    I, out of dumb luck, stumbled apon it.

    Once I found this handy little box. My form works like a charm! Thanks to everyone for their help.

    Now, I have one more question.

    When I enter an Item in my combo box, it enters the corresponding unit price in the unit price box for me. Is there a way for me to edit the unit price and updating the table without leaving the form?

    Many thanks from the Access challenged.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    in the afterupdate event of the unit price field, you can add in the following command to save your change without having to change records:

    docmd.runcommand accmdsaverecord

  5. #5
    Join Date
    Aug 2004
    Posts
    13
    It's not working. It removes my Item Description when I change the unit price. I want to keep the item and update the unit price.

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    to what are the two fields bound? Are they bound to their own fields or the same field? Do you have any other vb code other than the not in list code from above?

  7. #7
    Join Date
    Aug 2004
    Posts
    13
    ???

    I have a Inventory table that I update for all the report.

    I have a unit price table that I use for the combo box to automatically pop in the prices.

    The only code I have done is the one above which works fine.

    I just need to be able to update the existing unit prices within the unit price table.

    Sorry, I am so clueless.

  8. #8
    Join Date
    Aug 2004
    Posts
    13

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    text boxes (as do many other field types) have a property called Control Source. To what is the Control Source property set for the list item and unit price fields? If changing one changes the other, then I suspect that they are bound to the same column.

  10. #10
    Join Date
    Aug 2004
    Posts
    13
    The control source for both are set to the corresponding unit price field.

    I'm starting to have the problem of when I pick an item from the inventory list in my combo box, if it matches the unit price of another item in the combo box, it automatically replaces it.

    i.e. Grab Hooks $2.45

    I add via NotInList Broom Handles $2.45

    When I hit enter it pops in Grab Hooks. I pull the menu down and reselect broom handle that is now in my list and right after I click... Grab Hooks!!

    Why does it do that??

    Thanks for your help.

  11. #11
    Join Date
    Aug 2004
    Posts
    13

  12. #12
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Bind them to separate fields if you want to be able to edit one without the other changing.

  13. #13
    Join Date
    Aug 2004
    Posts
    13
    Ok, that makes sense.

    Why does it select an item with the same unit price and enter in my combo box? I want one thing and it gives me another.

    i.e.

    blue paint - .89
    green paint - .89
    pink paint - .89

    Doesn't matter which paint I pick, it enters Blue Paint in my combo box. I need to specify which one was used.

    Sorry to be a pain...

  14. #14
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Your table should have two columns: Item and unitprice
    bind the item field to the item column, and the price field to the unitprice column. in the Afterupdate of the Item, assign the unitprice. For example, if your item combo box shows the item name and the price, then in the AfterUpdate event, put in the following code:

    me!unitprice = me!item.column(1)

    Note that Column(1) is the second column because (0) is the first. The reason the item changes is because they are bound to the same field. The first one in the list with that price will appear when you change the price.

  15. #15
    Join Date
    Aug 2004
    Posts
    13
    I have given up on changing the price. I'll just go into the table and edit the unit price.

    I want the item I select to stay put.

    I tried what you told me and it doesn't work. If I bound them to separate fields, the whole combo box doesn't work. My list isn't there anymore and my unit prices are gone too.



    How do I make it select the item I want and keep it in the combo box. Just update the unit price?

    The item description will always be different but the unit prices may be the same.

    Thanks so much.

Posting Permissions

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