Get warning message for related tables

[AshikHusein]

Hello folks!

I have been breaking my head on this problem and after three days of agony (and a broken head!) I have decided to ask the forum.

I have two tables related by a field called "IPO". The database is meant to track the inventory of the "IPO".

When a part of an "IPO" inventory is sold, the entry is made through a form which has the "IPO" field and other fields for the amount being sold etc. The "IPO" entry is made thru a combobox with the related table as the source for the particular field.

Now with the idea of user friendliness in mind, if a certain "IPO" is selected using the combobox and if the user decides to delete it (by pressing the backspace keys or whatever) and so effectively if the value of the "IPO" is null and you try to close the form, there is a warning message which says someting to the effect that "it cannot take a null value since it is a key field and the data is of variant type". I dont want this message to show up and I have tried to do it programatically by using docmd.setwarnings False for all events possible (Afterupdate, on close, beforeupdate etc.) I have also programmed a sendkey macro to enter Yes to (which now seems stupid because I think it only sends the keys to the forms!) but the message keeps on appearing.

Is there someting I am doing wrong or is there a different approach to this thing? Or can these warnings be disabled by an API call or some other way?
Will appreciate a response.

 

[AshikHusein]

Thank you for your reply. Appreciate the input.

Once the form is dirtied by someone typing something or code of your that places a value in a form field, Access will attempt to save the record when you close the form or scroll to a different record. If your problem is simply someone typing a value and then backspacing, you can train your users to use the escape key to clear an unwanted entry. Then hitting escape again, resets the form's values by undoing all changes.

You can trap the error in the form's BeforeUpdate event. You'll need to decide what to test for and whether to prompt the user to decide what to do or to make a silent decision to discard the changes.

If IsNull(YourKeyField) Then
    Cancel = True
    msgbox "the update has been discarded", vbOKOnly
End If