Required Fields (Fill out at least one of the 2 fields)

[Skaven]

Hi there,

I have a table called ENGTIME. (Access 2003)

Fields in question are PartNum & QuoteNum

On my form, I need to make sure one of these 2 fields is populated for every record. On records with a Part Number listed, there will never be a Quote Number and Vice Versa.

I have set all of the other fields in the form to be REQUIRED fields, but I can't seem to find any help relating to requiring one of 2 fields to be populated.

Thanks for your valued help.

JG

 

[DCrake]

First of all there can be 4 variations

Part Yes
Quote No

Part No
Quote Yes

Part No
Quote No

Part Yes
Quote Yes

You first need to decide which field is dominant

If at the point of saving it find Yes Yes which should be saved which should be ignored?

If neither have a value what next?

Once you have established the rules then you can code the outcome at the point of saving the record.

 

[Skaven]

I'm not really concerned if someone enters both a Part number and a Quote number - it might happen once in a while.

What I'm really concerned with is the
Part No
Quote No
scenario. Do you have sample code I could look at to prevent the No/No event?

 

[Pat Hartman]

You need to place your code in the Form's BeforeUpdate event. You can't effectively edit multiple related controls at the control level.

If Me.[Part No] & "" = "" Then
    If Me.[Quote No] & "" = "" Then
        Msgbox "Either Part No or Quote No is required.",vbokOnly
        Cancel = True
        Me.[Part No].SetFocus
        Exit Sub
     End IF
End If

I used field & "" rather than checking for null because if someone enters a value in a field and then deletes it, the control is no longer null, it is a zero length string so concatenating a zls to a field turns a null into a zls so only one check needs to be done. When data fails an edit, it is CRITICAL that the update event be cancelled and that is what "cancel = true" is doing. I also moved the focus to the part no field. I also normally exit the event once an error is found to avoid giving the users multiple errors at one time which may be confusing.