How to handle Required property on table (1 Viewer)

[Bob]

Hi

I have two fields on a table, let's call them Category and SubCategory. Both rightly should have 'Required' set to Yes.

Possible values for both fields are done with lookups so I use a combo in both places. When I select a category the subCategory will need to be requeried. All good when the user doesn't change his mind.

However, if the user selects a value for both fields and the changes his mind about the Category, the problem arises. When the new value is selected, the subCategory is no longer valid but the invalid value remains in the field. I would like to reset the value of the subCategory by assiging a null value, but Access will not let me do this (because of the Required property on the table).

Anyone with a nice solution to this?

 

[missinglinq]

Many developers, rather than setting the fields as Required at the Table level, use the Form_BeforeUpdate event to assure that each needed field has a value. They find that they can use warning messages that are much friendlier and informative than those that Access pops up when a Required field is left blank.

An example would be

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If IsNull(Me.Control1) Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If
 If IsNull(Me.Control2) Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If
End Sub

replacing the Control1 and Control2 with the actual names of your Comboboxes.

You can then reset an erroneous selection to Null, without popping the Required warning, as this validation is not done until the record is about to be saved.

Remember to change the Required Property at the Table level back to No.

Linq ;0)>