Vba dlookup code help!

[amsmakkah]

i have a form F_Sales and there is a subform F_Sale_subform containing field Item_id. When i type any number in that field if it exist in T_item table it will show description and quantity and price etc.but when i type any number that dooes not exist in items table then it does not show any thing there and go to next blank field and give me very long error of database jet something....



the thing i want is that when i enter any number in Itemid field it will search it in items table and if it exist it will go on and if not then displays a msgbox that item does not exist and return the focus on itemi_id..

i think it will be something on exit event code...


plz help me

[drexasaurus]

I think the best way to avoid people from entering item ids that are not proper IDs is to use a combo or list box for the id fields that is restricted to only the ids that are already entered into the system. Change the settings so that they cannot enter fields that are not ids in the system.

If you wanted, you could allow them to enter new ids, but use some event, like before update or whatever would work to check if the id existed in the table, and if not, cancel the update and give a warning to the user, saying that they must enter the ID and product info first before using this ID. Perhaps even open the form for them if they select OK after being asked: "Do you want to open the product information entry form now?"

You should avoid allowing users to enter bad IDs in the first place. Because of the relationships set in the tables, they will not be allowed to enter a new id unless it is entered into related product table first.