Check if part selected in combo box has associated record in another table

[DaniePosthumus]

Hi

My database has the following tables and fields regarding parts and associated bills of materials:

-tblParts


PartID, PartName etc.
-tblBOMS
PartID, ComponentID, ComponentName, Qty etc

I also have a form - frmPickList - where the user capture certain data which is written to a table - tblBOMPickList. There is a command button for printing once the data is captured which generates a picking slip for the shop floor which tells them which part to manufacture (PartName) and which components to use and their quantity's (ComponentName, Qty)

On the form -frmPickList - I have a combo box where the user can select the main part. The idea is that the report used for printing the pick list, retrieves the components from the query on which the report is based and it works fine as long as there is an associated record in tblBOMS.

My problem is that I need to have a control that alerts the user if the main part selected in the dropdown combo box does not have components associated with it (in other words - it does not have a corresponding record in tblBOMS).

I guess it will have to be a snippet of code in the 'After Update' property of the combo box but I'm completely clueless with VBA. What I need is a msg box to pop up alerting the user that there isn't a bill of material associated with the part selected and the user then has an option to open a new form from which a new bill of material can be added to the table tblBOMS on the fly.

Can someone please help me with the VBA code to achieve this?

Kind regards

Danie

[ItsMe]

How about DLookup?
http://support.microsoft.com/kb/208786

You could use it in an If Else statement.

[DaniePosthumus]

Thank you very much!