IF Then Statement Macro in VBA

[richardwo]

Morning,

I'm a VBA novice and am having difficulties writing a simple code in VBA. I
have designed a form using Microsoft Access, one field entitled "ContactType"
asks how the patient made contact with the service. This is linked to a
separate table and displays the options ("home visit", "telephone" and
"clinic"). When "telephone" is selected I want the next field in the form
entitled "Location" to display "N/A". I would also like this macro to run
automatically each time "ContactTye" is entered into the form.

I have tried the following, with no luck:

Sub Loc()
If ContactType = Telephone Then
Location = "N/A"
Else
End If
End Sub

I think it may be due to how I'm referencing the "Location" field in the form.

Any help would really be appreciated.

Cheers

 

[Douglas J. Steele]

You need to make sure that Access knows you're talking about controls on the
form by using the Me keyword:

Sub Loc()
If Me.ContactType = Telephone Then
Me.Location = "N/A"
End If
End Sub

This assumes that Loc is in the class module associated with the form. If
it's not, you'll need to refer specifically to the form:

Sub Loc()
If Forms("MyForm").ContactType = Telephone Then
Forms("MyForm").Location = "N/A"
End If
End Sub

 

[richardwo]

Hi,

I have inserted a class module and added the following code, and although it
runs without returning an error message, when I select "Telephone" in the
contactType field nothing happens.

Sub Loc()
If Forms!frmclinic.ContactType = Telephone Then
Forms!frmclinic.Location = "N/A"
Else
Forms!frmclinic.Location = " "
End If
End Sub

Also how do you make the macro run automatically upon selecting text in the
ContactType field?

Thanks again fro your help.

Cheers

 

[Paul Csornok]

Try this...

Private Sub ContactType_AfterUpdate()
If Me!ContactType = "telephone" Then
Me!Location = "N/A"
Else
End If
End Sub