IF code

[Matizo]

Hi guys,
I'm complete newbie with VBA.
I have this form with a combo box to select either "Residential" or "Commercial".
If "Residential" is selected I want Title, Forename and Surname fields enabled and Business Name field disabled and vice versa.
I have this VBA code:

Private Sub ClientType_BeforeUpdate(Cancel As Integer)
If InStr(1, [ClientType], Commercial) = 0 Then
Me.Title.Enabled = False
Me.Forename.Enabled = False
Me.Surname.Enabled = False
Me.BusinessName.Enabled = True
Else
Me.Title.Enabled = True
Me.Forename.Enabled = True
Me.Surname.Enabled = True
Me.BusinessName.Enabled = False
Cancel = True
End If
End Sub

however when I use the combo box nothing happens.

Please help. is this code even right??

Cheers

 

[pbaldy]

In describing the issue, you properly put Commercial in quotes. You didn't in the code. If the selections are simply those two, I would drop the InStr function and simply do :

If Me.ClientType = "Commercial" Then

I would also use the after update event rather than before, though it may work either way.

 

[georgedwilkinson]

Also, you're setting "Cancel" true if the value contained in the variable named "Commerial" isn't contained in the ddl, which I'm assuming is always. Put:

Option Explicit

at the top of your module and you'll notice it breaks (probably). That's what Paul was talking about.

Since that always happens (your condition is always false), the update will always fail (because you reset Cancel).

PS: you'll need to remove this line:

Cancel = True

for the code to do anything (for non-commercial) once you have the other problem fixed.

 

[Matizo]

Hi,
I didn't put anything in quotes so this is not a problem.
Also, I tried to use

If Me.ClientType = Commercial Then

but I'm not sure what to do with

Option Explicit

What's more.. I can input data into fields Title, Forename and Surname if I haven't used the combo box, but once I use it I can't write in any field, even though they are NOT disabled (shadowed)...

 

[pbaldy]

I didn't put anything in quotes so this is not a problem.

My point is that you need to:


If Me.ClientType = "Commercial" Then

 

[georgedwilkinson]

Hi,


but I'm not sure what to do with

Option Explicit

Put:

Option Explicit

at the top of your module

This will prove to you (via the error message that pops up) that what Paul has been trying to tell you (put quotes around "Commercial") is true and that you could have found this out for yourself much more easily than we could tell you. Further, I was trying to point out to you that you code still might not do anything.

And Wiklendt, this is a perfect example of why.

 

[Matizo]

Thanks! I made it work with this code:

Private Sub ClientType_BeforeUpdate(Cancel As Integer)
If Me.ClientType = "Commercial" Then
Me.Title.Enabled = False
Me.Forename.Enabled = False
Me.Surname.Enabled = False
Me.BusinessName.Enabled = True
Else
Me.Title.Enabled = True
Me.Forename.Enabled = True
Me.Surname.Enabled = True
Me.BusinessName.Enabled = False
End If
End Sub


Cheers guys!