Test if control exists (1 Viewer)

[CanuckBuck]

I have a generic date order validation function in a code module that I'm using on a number of forms. Not all of the forms have all of the dates. Is there a way to test for the existence of a control before executing a particular line (in this case a CASE statement)?

Here's my function;

If there's a better way to do this (i.e. write the function differently I'm totally open to suggestions).

Thanks in advance for any assistance.

Function MilestoneDateOutOfOrder(frm As Form, ctl As Control) As Integer

'Check to ensure that Milestone Dates are in the
'correct (Received Date <= Assignment Date <= Assure Completeness End Date <= Technical Reivew End Date < Decision Date)
'order cancel the update and inform the user if they are not
    
    Select Case ctl.Name
        Case "txtReceivedDate"
            Select Case frm.txtReceivedDate.Value
                Case Is > frm.txtAssignmentDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Received Date cannot be greater than Assignment Date"
                Case Is > frm.txtAssureCompletenessEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Received Date cannot be greather than Assure Completeness End Date."
                Case Is > frm.txtTechnicalReviewEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Received Date cannot be greater than Technical Review End Date."
                Case Is > frm.txtDecisionDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Received Date cannot be greater than Decision Date."
                Case Else
                    MilestoneDateOutOfOrder = False
            End Select
        Case "txtAssignmentDate"
            Select Case frm.txtAssignmentDate.Value
                Case Is < frm.txtReceivedDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Assignment Date cannot be less than Received Date"
                Case Is > frm.txtAssureCompletenessEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Assignment Date cannot be greather than Assure Completeness End Date."
                Case Is > frm.txtTechnicalReviewEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Assignment Date cannot be greater than Technical Review End Date."
                Case Is > frm.txtDecisionDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Assignment Date cannot be greater than Decision Date."
                Case Else
                    MilestoneDateOutOfOrder = False
            End Select
        Case "txtAssureCompletenessEndDate"
            Select Case frm.txtAssureCompletenessEndDate.Value
                Case Is < frm.txtReceivedDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Assure Completeness End Date cannot be less than Received Date."
                Case Is < frm.txtAssignmentDate.Value
                    MilestoneDateOutOfOrder = True
                     MsgBox "Assure Completeness End Date cannot be less than Assignment Date."
                Case Is > frm.txtTechnicalReviewEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Assure Completeness End Date cannot be greater than Technical Review End Date"
                Case Is > frm.txtDecisionDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Assure Completeness End Date cannot be greater than Decision Date."
                Case Else
                    MilestoneDateOutOfOrder = False
            End Select
        Case "txtTechnicalReviewEndDate"
            Select Case frm.txtTechnicalReviewEndDate.Value
                Case Is < frm.txtReceivedDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Technical Review End Date cannot be less than Received Date."
                Case Is < frm.txtAssignmentDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Technical Review End Date cannot be less than Assignment Date."
                Case Is < frm.txtAssureCompletenessEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Technical Review End Date cannot be less than Assure Completeness End Date"
                Case Is > frm.txtDecisionDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Technical Review End Date cannot be greater than Decision Date."
                Case Else
                    MilestoneDateOutOfOrder = False
            End Select
        Case "txtDecisionDate"
            Select Case frm.txtDecisionDate.Value
                Case Is < frm.txtReceivedDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Decision Date cannot be less than Received Date."
                Case Is < frm.txtAssignmentDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Decision Date cannot be less than Assignment Date."
                Case Is < frm.txtAssureCompletenessEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Decision Date cannot be less than Assure Completeness End Date"
                Case Is < frm.txtTechnicalReviewEndDate.Value
                    MilestoneDateOutOfOrder = True
                    MsgBox "Decision Date cannot be less than Technical Review End Date."
                Case Else
                    MilestoneDateOutOfOrder = False
            End Select
    End Select
    
End Function

 

[June7]

Loop through form's controls collection and test each control name with the control name in question. If no match then does not exist.

Or error handler to trap error generated when code does not find control.

 

[theDBguy]

Most of the times, the quickest way is to do a test to force an error and then handle it. If no error, then the test passed.

 

[CanuckBuck]

I found this bit of code;

Public Function ControlExists(ByVal Name As String) As Boolean

'Since not all forms have all controls there are cases
'where, before we can do a thing with a control we need
'to determine if it exists.

    On Error Resume Next
        ControlExists = Not Me.Controls(Name) Is Nothing
    On Error GoTo 0
End Function

Here; <Unfortunately I can't include URLs since I don't have enough cred on this forum yet :banghead:>


I'm using it like this;

Function MilestoneDateOutOfOrder(frm As Form, ctl As Control) As Integer

'Check to ensure that Milestone Dates are in the
'correct (Received Date <= Assignment Date <= Assure Completeness End Date <= Technical Reivew End Date < Decision Date)
'order cancel the update and inform the user if they are not
    
    Select Case ctl.Name
        Case "txtReceivedDate"
            If ControlExists(frm.txtAssignmentDate) Then
                If frm.txtReceivedDate.Value > frm.txtAssignmentDate.Value Then
                    MsgBox "Received Date cannot be greater than Assignment Date"
                    MilestoneDateOutOfOrder = True
                    Exit Function
                End If
            ElseIf ControlExists(frm.txtAssureCompletenessEndDate) Then
                If frm.txtReceivedDate.Value > frm.txtAssureCompletenessEndDate.Value Then
                    MsgBox "Received Date cannot be greather than Assure Completeness End Date."
                    MilestoneDateOutOfOrder = True
                    Exit Function
                End If
            ElseIf ControlExists(frm.TechnicalReviewEndDate) Then
                If frm.txtReceivedDate.Value > frm.TechnicalReviewEndDate.Value Then
                    MsgBox "Received Date cannot be greater than Technical Review End Date."
                    MilestoneDateOutOfOrder = True
                    Exit Function
                End If
            ElseIf ControlExists(frm.DecisionDate) Then
                If frm.ReceivedDate.Value > frm.DecisionDate.Value Then
                    MsgBox "Received Date cannot be greater than Decision Date."
                    MilestoneDateOutOfOrder = True
                    Exit Function
                End If
            Else
                MilestoneDateOutOfOrder = False
            End If
        Case <rinse and repeat for the other dates...>

For the sake of completeness of this thread I'll report back if this works or not.

Thanks for your feedback

 

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.

 

[Gasman]

<Unfortunately I can't include URLs since I don't have enough cred on this forum yet :banghead:>

Simple text URL would have been fine?

 

[CanuckBuck]

Gasman;

I thought of that afterward... Here it is

stackoverflow.com/questions/44257145/check-if-textbox-exists-vba-using-name

 

[CanuckBuck]

As noted above, I said I'd report back on whether my solution worked or not.

For posterity's sake this might not be as satisfying as "...it worked perfectly and here's how I did it..."

I took an alternate, and simpler path. I put the "missing" controls on the form and simply set their visibility to No. This way they're there when the code looks for them. This works for my use case.

 

[theDBguy]

As noted above, I said I'd report back on whether my solution worked or not.

For posterity's sake this might not be as satisfying as "...it worked perfectly and here's how I did it..."

I took an alternate, and simpler path. I put the "missing" controls on the form and simply set their visibility to No. This way they're there when the code looks for them. This works for my use case.

Hi. Thanks for the update. Glad to hear you found a solution to fit your situation. Good luck with your project.