Make code more efficient: Dlookup

[moscgama]

Hi Everyone,

I have written code to look up a value in a table that then enables or disables a subform in my main form. The code works, but I know it is now as efficient as it can be. The main problem is that I have multiple values that determine if the subform should be enabled or disabled. I would like to use an IN statement but I'm pretty sure this doesn't work for Dlookup. Below is an example of the code I currently have:

Sub enablecontrols(setting As Boolean)
    Inv_subform.Enabled = setting
End Sub

Private Sub Form_Current()

    If DLookup("Description", "Line Items", "ID=" & Me.ID) = "Test1" Then
    enablecontrols (True)
    Else: enablecontrols (False)
    End If
        
End Sub

Private Sub Form_Current1()

    If DLookup("Description", "Line Items", "ID=" & Me.ID) = "Test2" Then
    enablecontrols (True)
    Else: enablecontrols (False)
    End If
        
End Sub


Private Sub Form_Current2()

    If DLookup("Description", "Line Items", "ID=" & Me.ID) = "Test3" Then
    enablecontrols (True)
    Else: enablecontrols (False)
    End If
        
End Sub

Private Sub Form_Current3()

    If DLookup("Description", "Line Items", "ID=" & Me.ID) = "Test4" Then
    enablecontrols (True)
    Else: enablecontrols (False)
    End If
        
End Sub

Like I said, this works fine, but I am concerned if I need to add more items to look up and the stability of the code in general.

Please let me know if there is a way to condense the code and make it more efficient, or if you have any questions. I appreciate any help you can give!

Thanks!

 

[BlueIshDan]

I'm sorry, but I have no clue what you're trying to accomplish even if your code does work, the description just wasn't enough for me to catch on.

For my sake:

What are you trying to do, on which event(s)?

 

[sxschech]

Not fully tested:

Dim stTest as String

stTest = DLookup("Description", "Line Items", "ID=" & Me.ID)

Select Case stTest
    Case "Test1", "Test2", "Test3"
        enablecontrols = True
    Case Else 
        enablecontrols = False
End Select

 

[plog]

This really sounds like you don't have a normalized table. Is there a way to create a new field in Line Items that designates if the controls should be enabled? So instead of adding new Description values to the code, you simply flag the record with a Yes/No to designated if the controls should be enabled.

enablecontrols (DLookup("[ControlSettings]", "Line Items", "ID=" & Me.ID))