Reference 3 fields so that all 3 are not null

[burrina]

Ok, I have 3 fields on my form named, OnCallDte OnCallNexttDte and finally OnCall



both OnCallDte and OnCallNexttDte are date fields and cant have a null value if user checks the checkbox which is name OnCall Yes/No value. Also if user checks the checkbox then dates are necessary so that finally all 3 are required.

So my dilemna is that how do I make it so all 3 are necessary. Itried the below code, but it obviously does not work since it wont allow anything. I am close but no banana.

If IsNull([OnCallDte] Or [CallNexttDte]) Then ' Checks to see if date fields are Null
[OnCall] = False
MsgBox "If Employee On Call Status is Yes, Then Start and End Dates Are Required"
End If


Also tried this but did not work. I am referring to a sub-form from my main form. Main form is Employees_on_Call_What_Order

'If Forms!Employees_on_Call_What_Order!Forms!frm_Eligi ble_For_On_Call![eligible] = True And [OnCall] = True Then
'[OnCallDte].Enabled = True And [CallNexttDte].Enabled = True
'End If



__________________________________________________ ____________________
Will this work ???

If Not IsNull(DLookup("OnCallDte", "Employees", "OnCall = True AND "CallNexttDte" , 'Employees",)) Then
MsgBox "Date Fields Must Have a Date"
Cancel = True
End If
Else
If Not IsNull (DLookup("OnCallDte", "Employees", AND "CallNexttDte Then
MsgBox "OnCall Box Must Be Checked."
Cancel = True
End If

If [OnCall] = True Then ' Checks to see if checkbox is set to yes
[OnCallDte].Value = Null
[CallNexttDte].Value = Null
MsgBox ""
End If

[June7]

What event do you have this code in? The checkbox Change event? Review this recent thread on similar issue https://www.accessforums.net/forms/c...ion-29218.html

[burrina]

It is on the main forms current event which is named Employees_on_Call_What_Order

Since that is where the fields are located that that must not be null, all 3 if both date fields are not null then checkbox must be true and if checkbox is true both date fields must not be null. or any combination.

__________________________________________________ ____
What I am trying to accomplish is this; If a employee is eligible to be on call and they have their status set to be on call and they also have their start and end dates containing a value then all is well.

The 4 fields that must have there criteria met are; OnCall and eligible, both of these are Yes/No checkboxes.
And the 2 other fields are OnCallDte and CallNexttDte and both of these are date fields.

So I need to check that all 4 criteria have been met. Otherwise cant save record or fields dimmed out or something so as to not allow it.

[June7]

DLookup should not be necessary (syntax is wrong anyway). The fields are on the form and are therefore available for reference by code.

What do you mean by 'wont allow anything'?

The If IsNull Then syntax is wrong. Try:

If IsNull(Me.OnCallDte) Or IsNull(Me.CallNexttDte) Then ' Checks to see if date fields are Null

Step debug. Refer to link at bottom of my post for debugging guidelines.

[burrina]

I just meant That really 4 fields must contain a value. The employee must be eligible, i.e. eligible = True , the employee must be be checked off as to be on call, OnCall = True
and both date fields must contain a date, i.e. OnCallDte and CallNexttDte

[June7]

Did you try revised code?

[burrina]

Yes, it did not work, can I send DB for you too look at? Will this help?

[June7]

Yes, follow instructions at bottom of my post. Indicate which objects are involved in issue.

[burrina]

Ok, here is the db. Please take a look at the OnCurrentEvent code for the main form where I have tried all kinds of code.

[June7]

So 'main form' is frm_Employees_on Call_What_Order?

There is only one table in the db. All queries are based on this one table. All forms and subforms essentially have the same data source or are unbound. Never seen anything like this.

The Eligible (misspelled as Eligibile in label) field is on subform but then is also in the main form RecordSource but for some reason code is not recognizing the main form field. This form opens filtered to only those employees who are eligible. Can't refer to the Eligible field in subform because it might not be on the same employee record that the main form is on. Consider this code in Current event:

Private Sub Form_Current()
With Me
.OnCallDte.Enabled = .OnCall
.CallNexttDte.Enabled = .OnCall
If .OnCall = True Then
    If IsNull(.OnCallDte) Or IsNull(.CallNexttDte) Then
        MsgBox "Both OnCall Start and End boxes must have a date"
    End If
End If
If Date >= .Set_On_Call_Week_Day_Date!one And Date <= .Set_On_Call_Week_Day_Date![seven] Then
    .Next_Weeks_Start_End_Days.Enabled = False
    .Following_Weeks_On_Call_Period.Enabled = False
    .Final_Weeks_On_Call_Period.Enabled = False
End If
End With
End Sub

If Eligible status is changed to False then run code in its Change event to set Oncall to False, remove dates, disable controls, and refresh forms.

If Oncall status is changed then run code in its Change event to remove dates and disable boxes or notify user needs dates and enable boxes.

What is Check6 for?

[burrina]

Check6 is an update query that sets all employee on call status to NO. I am trying code now. Thanks