Required Field (Conditional)

[mintech]

I'd like to have a field (ExpirationDate) be a required field IF and ONLY IF for the previous field (Drop Down Menu), a particular option was selected (Temporary). How may I do this?

[kd2017]

You can enforce this logic in the table validation rule or you can use vba or a macro in your form that will check the fields in the before update event.

http://allenbrowne.com/ValidationRule.html

https://docs.microsoft.com/en-us/off...reupdate-event

[mintech]

I'll check those links out and give it a shot right now

[mintech]

([Field1] Is Null) OR ([Field2] Is Not Null)

This is the rule I thought would apply but it does nothing. I changed to the proper field names (I didn't use quotes though). Feel like I'm missing something.

[kd2017]

It depends on the specifics of your table structures and field names.

Try something like this:

Not (([Drop down menu's field name]=What ever the temporary value is) AND ([ExpirationDate] Is Null))

For more specific help we'll need to know the specifics of your database.

[mintech]

So there are three fields involved here: Perm_Lic_No, Temp_Lic_No and Lic_Exp_Dt . If an employee has a permanent license, it doesn't expire and clearly he doesn't have a temporary license so the other fields are to remain NULL (def not required). If they do not have a permanent license, the other two fields become required.

That explains?

[kd2017]

((Perm_Lic_No Is Not Null) And (Temp_Lic_No is null) and (Lic_Exp_Dt is null)) Or ((Perm_Lic_No Is Null) And (Temp_Lic_No is not null) and (Lic_Exp_Dt is not null))

[Bob Fitz]

((Perm_Lic_No Is Not Null) And (Temp_Lic_No is null) and (Lic_Exp_Dt is null)) Or ((Perm_Lic_No Is Null) And (Temp_Lic_No is not null) and (Lic_Exp_Dt is not null))

IMHO it might be better to do the validation in the form's BeforeUpdate event if the validation is complex.

[mintech]

How would it look if in BeforeUpdate event. same?

[Micron]

Is Null is for queries. IsNull is for vba. This is being done in form code, yes? I agree that the form BeforeUpdate is the place to validate this. Furthermore, if they have either a permanent OR temporary license, this should be one field, not two.

[kd2017]

(([Perm_Lic_No] Is Not Null) And ([Temp_Lic_No] Is Null) And ([Lic_Exp_Dt] Is Null)) Or (([Perm_Lic_No] Is Null) And ([Temp_Lic_No] Is Not Null) And ([Lic_Exp_Dt] Is Not Null))

Works as a table validation

For vba form code you can use the same logic just change the formatting around for VBA

    If (Not IsNull(Me!Perm_Lic_No) And IsNull(Me!Temp_Lic_No) And IsNull(Me!Lic_Exp_Dt)) _
       Or (IsNull(Me!Perm_Lic_No) And Not IsNull(Me!Temp_Lic_No) And Not IsNull(Me!Lic_Exp_Dt)) Then
        Cancel = True
        MsgBox "Invalid input"
    End If

You can probably simplify all that with an xor logical operator but my brain is tired and feeling pretty illogical at the moment.

Also, I agree with Micron about merging the two license number fields.

[mintech]

Is Null is for queries. IsNull is for vba. This is being done in form code, yes? I agree that the form BeforeUpdate is the place to validate this. Furthermore, if they have either a permanent OR temporary license, this should be one field, not two.

Initially I had it as one field, a drop down. I couldn't quite figure out how to then make the expiration date field a required field, if and only if Temporary was selected

[Micron]

It depends on how many columns your combo had. Often it is more than one e.g. a primary key values column that you don't show, and the column you show to the user as his/her selection. So if the primary (bound) column value was 3 and the display was "temporary" then it is your choice; refer to the combo value (3) or the first column (column(0)) or the column that holds the descriptive text (column(1)). Quite doable.

If the "thing" is a license and it can be one of 7 types (like mechanic, welder, pipefitter, electrician, etc), would you have 7 fields? Hopefully you'd say no. It makes no difference if there are only 2 possibilities, it should still be 1 field. Expired would be a status, and is best represented by a date field, which indicates both expired and when. A simple checkbox field doesn't tell you when, and having both would be superfluous.

[mintech]

The drop down menu has three values (Temporary, Permanent, Other). If "Temporary" is chosen, then a field called "Lic_Exp_Dt" needs to become a Required field. Therein lies my dilemna

[Micron]

So rather than just tell us that nothing works, post the code you tried when you checked out the second link in post 2 and saw the last example there where it shows how to require data in one control when one or more other controls also have data. That sounds like your issue to me. Then we'll have something to work with.