Run-Time Error 91- If Statement

[breznyak1]

I have a maintenance Database with Main Form as header containing Vehicle Information and Controls (Eg. Year, make, Model etc. And Navigation Controls and Buttons To Edit Information for selected Unit)
My Sub Form is data sheet view for the Service History (Eg. Start Date, Engine Hrs, Deadline, Fault Description, Corrective Action, End Date, tech)



I am trying to have a label visibe in my main form [FormServiceHistory] only when Criteria is met from my subform [SubFormServiceHistory] Object Name[TBLservicehistory]

Criteria: Visible Only If: DEADLINE= True and EndDate Is Null

I have a Query that will generate the unit numbers that meet that criteria to use for my Deadline report if that could be used to make things easier.

But In wold Also Like DEADLINE Label to be Visible in the header Only if that Criteria is met.

I was able to get this to work eliminating the Date criteria, however I cannot Seem to get this to work using the Date criteria (Alone or with Deadline Criteria)

I was Not able to use And in the original code so I Dimmed as per code below.

I was getting a mis-match error. EndDate is a Date/Time Field in the underlying table so I Dim EndDate as Date. It still had mis-match error.

Now I am getting Run-Time error 91
I Click on Debug and VBA Highlights my [Deadline=] Line in the Code Builder. Any help would be appreciated.

Also If Some time a code to add to this to refresh Automatically without having to leave record and go back to record to see results would be nice.

Any Help would be appreciated. Thank You

MS Access 2007 Win 7 Pro x64

Private Sub Form_Current()
Dim Deadline As CheckBox
Dim EndDate As Date


Deadline = Forms![FormServiceHistory]![TBLservicehistory].Form![Deadline]


EndDate = Forms![FormServiceHistory]![TBLservicehistory].Form![EndDate]


If Deadline = True And (EndDate = Null) Then


Me.Label299.Visible = True


Else
Me.Label299.Visible = False
End If
End Sub

[ranman256]

you dont = null, (EndDate = Null)
use:

if IsNull(EndDate)

[breznyak1]

Ok, I Tried That. Still Same Issue. Debugger Highlights my Deadline= Line of Code

Can I not use "And" to join 2 If Statements?

[ranman256]

yes, but it could be
Deadline

you defined it as a checkbox, and didnt need to.
You cant assign controls as control = value
you must SET them, Set chkBox = me.checkbox

you should have just assigned,Deadline as variant (to get the value, not clone the control)
dim Deadline
then
Deadline = Forms![FormServiceHistory]![TBLservicehistory].Form![Deadline].value

[breznyak1]

Thank You So Much. I then had to Dim EndDate as variable as well It now Works.

Is There A Way to Have it Automatically Display Once the record has been entered and criteria met as opposed entering record and going to Navigating to next record and then back to previous record in main form?

Here is Current Code After Fix:

Private Sub Form_Current()
Dim Deadline
Dim EndDate


Deadline = Forms![FormServiceHistory]![TBLservicehistory].Form![Deadline].Value


EndDate = Forms![FormServiceHistory]![TBLservicehistory].Form![EndDate]


If Deadline = True And IsNull(EndDate) Then


Me.Label299.Visible = True


Else
Me.Label299.Visible = False
End If
End Sub

[ranman256]

if you mean the Label299.visible?
it SHOULD display immediately everytime you go to a record.

but if you mean, when you checkmark the box, then the checkbox_AFTERUPDATE event must fire, you probably want more events so,:

Private Sub Form_Current()
    ShowLabel299
end sub


Private Sub Deadline_AfterUpdate()
    ShowLabel299
end sub


Private Sub EndDate_AfterUpdate()
    ShowLabel299
end sub


sub ShowLabel299()
Dim Deadline
Dim EndDate


Deadline = Forms![FormServiceHistory]![TBLservicehistory].Form![Deadline].Value
EndDate = Forms![FormServiceHistory]![TBLservicehistory].Form![EndDate]


If Deadline = True And IsNull(EndDate) Then
   Me.Label299.Visible = True
Else
   Me.Label299.Visible = False
End If
end Sub

[breznyak1]

I tried the Code. It does and did display it immediately after the record is selected, However It does not display it When the Criteria is met Eg.(checkbox and No EndDate) on the current record before leaving record in my main form and then going back to it. (eg. If I had 6125 and I was entering a service record into the sub form Enter Start Date, Engine hrs, Fault Description and Clicked in a different field, it would not display until I viewed a different unit and went back to that unit. Then it would display Immediately. Which That is Really not a big deal to me, but would be nice.

However I did notice If I created a deadline fault for a service record And added a new service record for a Non deadline Condition I would lose my Deadline Label in the Main Form for that unit. this is a larger concern for me. Is there a way To have those conditions set for All Service Records for Current Unit Number in Main Form. My Main Form and Sub Form are linked by unit number