One to many relationship problem and its errors

[swee]

I have four fields, ItemDesc, ItemCode, UnitPrice and UnitofMeasure(UOM). ItemDesc is a combobox and the rest r textfields. When the user types in ItemDesc and there's such an item in tblItemMaster, it will automatically fill up ItemCode, UnitPrice and UOM.

However, I have a problem if the user enters an ItemDesc that is not in tblItemMaster. I get an error when I try to go to the next record. The error goes,

"The Microsoft Jet Database Engine cant find a record in the table tblItemMaster with key matching field(s) ItemDesc"

How do I allow users to add new items yet at the same time allow them to be able to find existing records in the table?

Regards,
Swee

 

[Pat Hartman]

You need to use separate controls. An unbound control for searching and a bound control for updating.

 

[swee]

I have thought of a possible problem and that is when the user doesn't know whether the item they are going to type is new. They will proceed to type it in the combobox thinking this is an existing item in the table and the error will then occur. I have attached a sample db for you to try it out. Thanks for your time!

Regards,
Swee

 

[Pat Hartman]

I don't have time right now to look at your problem. What you need to do is to trap the error when a record is not found and ask the user if they want to add a new record.

 

[swee]

Yeah that's what I have in mind but sad to say, I don't know what to code to catch the error. Could someone guide me in this?