IF Then Statement Macro in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Try this...

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