Simple code, yet frustating for me!

[soundsfishy]

I have 2 Contractors that dont supply Accommodation. Ive managed to write the following for "GECC" but im not sure how to include "SVDP". So if " GECC" or"SVDP" are selected as the contractors to supply accommodation, the 3 bound fields become disabled.

I know its something simple but I havent done any VBA course to know how simple the solution is. Please help a dummy someone!! hehe.

Private Sub Contractor_AfterUpdate()
If Me![Contractor] = "GECC" Then
Me![DIMAFlatAddress].Enabled = False
Me![DateDepart].Enabled = False
Me![DepartAddress].Enabled = False

Else
Me![DIMAFlatAddress].Enabled = True
Me![DateDepart].Enabled = True
Me![DepartAddress].Enabled = True

End If
EndSub

 

[crucial_gear]

Try using a Select Case:

Select Case Me.Contractor

Case is = "GECC"
Me.DIMAFlatAddress.enabled = False

Case Is = "SVDP"
Me.DIMAFlatAddress.enabled = true

End Case

Just add a new Case Is statement for each condition you need to test for.

 

[soundsfishy]

Thanks for your reply!

Oh no! I havent ever had to use Select Case before so Im not familiar with it. Do I use the If statement after select case. You many have to help me out a bit here! sorry. It wouldnt be until March of next year that I'll learn basic VBA.

I tried having a go but I can seem to make it work. Im getting a runtime error 438. what the heck im i doing wrong.

Private Sub Contractor_AfterUpdate()
Select Case Me.Contractor

Case Is = "GECC"
Me![DIMA Flat Address].Enable = False
Me![Depart Date].Enable = False
Me![Depart Address].Enable = False

Case Is = "SVDP"
Me![DIMA Flat Address].Enable = False
Me![Depart Date].Enable = False
Me![Depart Address].Enable = False

End Select

End Sub

 

[Newman]

Use this:

Private Sub Contractor_AfterUpdate() 
    If Me![Contractor] = "GECC" or Me![Contractor] = "SVDP" Then
        Me![DIMAFlatAddress].Enabled = False
        Me![DateDepart].Enabled = False
        Me![DepartAddress].Enabled = False
    Else
        Me![DIMAFlatAddress].Enabled = True
        Me![DateDepart].Enabled = True
        Me![DepartAddress].Enabled = True 
    End If
EndSub

 

[Newman]

If you do want to use the select case, which is a good idea too, do not use «Case Is = "GECC"» but simply «Case "GECC"». You were close!

 

[soundsfishy]

Thanks Newman!

Works nicely!

I was damm close, but no cigar!. I was lacking this part: or Me![Contractor] = "SVDP" .


I knew it was starring me in the face!.

Thank for tip with the select Case too! It will come handy some day.

Thank you again!!!!!

 

[Pat Hartman]

The Case statement has lots of options. Look them up.

Private Sub Contractor_AfterUpdate()
Select Case Me.Contractor

Case "GECC", "SVDP"
Me![DIMA Flat Address].Enable = False
Me![Depart Date].Enable = False
Me![Depart Address].Enable = False
Case Else
Me![DIMA Flat Address].Enable = True
Me![Depart Date].Enable = True
Me![Depart Address].Enable = True

End Select

End Sub

 

[ListO]

Another Approach...

This should be a little more elegant

Private Sub Contractor_AfterUpdate()
Dim boChoice as Boolean

[COLOR=crimson]'The following evaluates to either True or False[/COLOR]
boChoice = (Me![Contractor] = "GECC" or Me![Contractor] = "SVDP")   

Me![DIMAFlatAddress].Enabled = boChoice
Me![DateDepart].Enabled =  boChoice
Me![DepartAddress].Enabled = boChoice

End Sub

Fewer lines of code, fewer branches.