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.
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
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.
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.
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.