Problems with OnChange Macro

[richardwo]

Morning,

With the help of people on this forum I've written the following macro:

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

which is designed to change the location field in a form to "N/A" when
ContactType of "Telephone" is selected.

I would like this macro to start each time "Telephone" is selected in the
ContactType field. I went into the design view of the relevant form
("frmclinic"), selected ContactType and then its properties, went to the
Event tab and then OnChange. I seleceted Macro Builder and in RunMacro in the
Action Builder cell. I typed in the name of the Macro
("ContactType_UponChange") and exited.

When I enter the form and select "Telephone" in the ContactType field it
brings up an error message stating that "A macro can call itself a maximum of
20-times ....". I have tried entering a repeat count of 1 but this does not
work. I would appreciate any help in sorting this.

Is this is the easiest way to run a macro dependent upon the outcome of a
combo box selection?

Thank you in advance.

Cheers

 

[Jeff Boyce]

Try the AfterUpdate event instead.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[BruceM]

That's a VBA procedure. If ContactType is the name of the combo box (list
box?) in which Telephone is selected, you already have the code you need
(although it is the Change event; I'm not sure if UponChange will work, but
more on that in a moment). If the combo box is in frmClinic then all you
need (other than maybe some error handling) is:

Private Sub ContactType_Change()

If Me.ContactType = "Telephone" Then
Me.Location = "N/A"
End If

End Sub

Also, the After Update event for the combo box would probably be a better
place to run the code; in the Change event it will run after every key
stroke.

Macros and VBA procedures are two different things, in ways I cannot really
explain. Macros have limited use in Access 2000 and later: compatability
with earlier versions of Access and a few specialized circumstances, of
which your situation is not one.

 

[richardwo]

Thanks very much Bruce,

I wonder if you could help with a related problem. After using the VBA
procedure, after entering "Telephone" in the ContactType field the Location
field automatically updates and displays "N/A" as required. However, the form
"frmclinic" is a subform conatined in "frmpatient". Therefore, data will be
inputted into the "frmpatient" form. When i enter "Telephone" inot the
ContactType field on the "frmpatient" form it brings up an error message:
Run-time error '2450'. The database cannot find the form "frmclinic" referred
to.

Is there any way that I can resolve this?

Thanks again for your help.

Kind regards, richard

 

[BruceM]

In general you reference a subform control from the main form with syntax
like the following:

Forms!MainFormName!SubformName.Form!ControlName

However, I can't make out what you're trying to do. If you need the value
of a main form control to show up on the subform you should reference the
control, not copy its value. The control source of an unbound text box on
the subform could be:
=Me.Parent.ControlName

The code I provided does not mention the form name, so other code must be
calling for frmClinic.

When you are referencing a field or control on the same form as the one in
which the code is running, all you need to do is use the syntax:
Me.ControlName
It's sort of like an inter-office memo. You don't need the whole address
for somebody who is already in the building.

If you still have questions, please provide additional information,
remembering while you do that I cannot see your database.

 

[richardwo]

Thanks again Bruce for your help. I still have not got to the bottom of this
problem and have created a new simple database to identify the problem.

The table contains 3 fields: "Autonumber", "ContactType" (combo box -linked
to a lookup table "LU_ContactType", containing three options: home visit,
clinic and telephone), and the last field is "Location" (free text field). I
then designed a form "Clinic" containing these 3 fields. In the design view I
selected ContactType properties then Event and in the AfterUpdate field
selected EventProcedure. I clicked the dots next to this and entered VBA
window, in which i wrote:

Private Sub Location_AfterUpdate()

If Me.ContactType = "Telephone" Then
Me.Location = "N/A"
End If

End Sub

I then exited the VBA window and entered the "Clinic" form and proceeded to
select "Telephone" in the ContactType combo box but nothing happened in the
"Location" field. I then took a different approach and inserted a module with
the following code:

Sub FingersCrossed()

If Forms!Clinic.ContactType = "Telephone" Then
Forms!Clinic.Location = "N/A"
End If

End Sub

Whilst in the VBA window I clicked F5 to run the procedure and it duly
inserted "N/A" in the record of the open form, which had "Telephone" selected
in the "ContactType" field. This is far from ideal with having to select the
code and press F5 for each entry on the form. What I would like is for the
procedure to run automatically, your help would be very welcome is sorting
these issues.

Thanks again.