Invoke Update Query when a field changes on a Form

[Guest]

I know this is probably easy, but I am too new to Access.
I have an update query that I would like to invoke on the current record
when the field changes in a combo box field on the form.

For examplee: when the user makes a choice in the Method field, the update
query is invoked for that record and the corresponding fields are updated.

How do I do this?

 

[Guest]

I do something similar for Companies, when a company name is picked from a
list it automatically inserts the address information for that company. On
the form properties under row source query:

SELECT DISTINCTROW [Companies].[Company], [Companies].[Address],
[Companies].[City], [Companies].[St], [Companies].[ZIP] FROM [Companies];

Then the After update - Event Procedure Code:
Private Sub Company_AfterUpdate()
Dim ctl As Control
Set ctl = Me!Company
If Not IsNull(ctl) Then
Me!Address = ctl.Column(1)
Me!City = ctl.Column(2)
Me!St = ctl.Column(3)
Me!Zip = ctl.Column(4)
End If
'inserts company info when company name is chosen from list
End Sub

Hope this helps.

 

[Guest]

I'm going to treat this as a 'road to Dublin' question, as it suggests to me
that you might well have some redundancy in the table underlying the form.

If the value selected in the combo box determines values in other columns in
the table then you don't need to have those columns in the table, and
moreover should not have them as the redundancy implied by this leaves the
door open to update anomalies. The values can be shown in unbound controls
on the form.

I could be wrong of course, but while to do what you want is straightforward
enough, on the basis of the information you've given I could be giving you
bad advice were I to tell you how. If you could post back with a more
detailed explanation of what's involved then we can probably advise further.

Ken Sheridan
Stafford, England

 

[Guest]

There are 8 methods. There are 12 other fields that are different for each
method. Each person has one of the 8 methods. However, sometimes there are
exceptions for a person in one of the fields ie: Even though the Method field
is still the same, some of the fields may vary.

I just want this to work for the initial population of the form for each
person.

Later changes can be made in the various fields.

Thus the reson for the redundancy

 

[Guest]

I told you I could be wrong! There is no redundancy (in the jargon the
non-key columns are said to irreducibly functionally dependent on the key of
the table, not transitively dependent on it, which would have been the case
if their values could not be legitimately changed).

Greg has given you an excellent solution; one minor point the DISTINCTROW is
not necessary in the SQL statement. Access used to insert this
automatically; it stems from the days before Doris Day was a virgin and
Access didn't support subqueries.

Notice that the Column property is zero-based so Column(1) is the second
column returned by the SQL statement and so on.

Ken Sheridan
Stafford, England

 

[Guest]

Excellent reply in the context of the post, but are you sure that in the
context of your own application you are not introducing redundancy? I'd have
thought it unlikely that you'd want to change company address data per
related record and that unbound computed controls would be the correct
approach, e.g. for Address:

=[Company].Column(1)

BTW if you add 'ORDER BY Company' (without the quotes and before the
terminating semi-colon) to the RowSource query the list will always be in
alphabetical order.

Ken Sheridan
Stafford, England

I do something similar for Companies, when a company name is picked from a
list it automatically inserts the address information for that company. On
the form properties under row source query:

SELECT DISTINCTROW [Companies].[Company], [Companies].[Address],
[Companies].[City], [Companies].[St], [Companies].[ZIP] FROM [Companies];

Then the After update - Event Procedure Code:
Private Sub Company_AfterUpdate()
Dim ctl As Control
Set ctl = Me!Company
If Not IsNull(ctl) Then
Me!Address = ctl.Column(1)
Me!City = ctl.Column(2)
Me!St = ctl.Column(3)
Me!Zip = ctl.Column(4)
End If
'inserts company info when company name is chosen from list
End Sub

Hope this helps.

I know this is probably easy, but I am too new to Access.
I have an update query that I would like to invoke on the current record
when the field changes in a combo box field on the form.

For examplee: when the user makes a choice in the Method field, the update
query is invoked for that record and the corresponding fields are updated.

How do I do this?

 

[Guest]

Greg, Thank you for responding. I really appreciate this and it is helpful,
however, I do not have a Row Source. I do have a Record Source. Would I do
the same thing here?

Let me give you my scenario
I have 2 tables ; One is Plan
The other is StudentPlan
I can run an update query to update all of the Student plans if I choose a
Method for every student.

However, this is how I really need it to work:
I have a form based on a Query that displays fields from the StudentPlan and
the StudentData Tables.
The Method field references the Method field in Plan via a ComboBox.

I need for the update to occur for each student as we meet with that student
and determine the proper Method for just him.

I want the fields in the StudentPlan to update to what is currently in the
Plan (with the same Method) when a Method is chosen, and I want this to occur
for only this student so that we can evaluate his Plan and make changes in
some of the fields if needed.

I do something similar for Companies, when a company name is picked from a
list it automatically inserts the address information for that company. On
the form properties under row source query:

SELECT DISTINCTROW [Companies].[Company], [Companies].[Address],
[Companies].[City], [Companies].[St], [Companies].[ZIP] FROM [Companies];

Then the After update - Event Procedure Code:
Private Sub Company_AfterUpdate()
Dim ctl As Control
Set ctl = Me!Company
If Not IsNull(ctl) Then
Me!Address = ctl.Column(1)
Me!City = ctl.Column(2)
Me!St = ctl.Column(3)
Me!Zip = ctl.Column(4)
End If
'inserts company info when company name is chosen from list
End Sub

Hope this helps.

I know this is probably easy, but I am too new to Access.
I have an update query that I would like to invoke on the current record
when the field changes in a combo box field on the form.

For examplee: when the user makes a choice in the Method field, the update
query is invoked for that record and the corresponding fields are updated.

How do I do this?

 

[Guest]

RowSource is the property of a combo box or list box control, whereas
RecordSource is a property of a form. Select the Method combo box in design
view and you should find the RowSource property on the Data tab of its
properties sheet.

You should find that the property is either the name of a query, or more
likely an SQL statement for a query which returns rows from the Plan table.
If you click on the 'build' button for this property (the one on the right
with 3 dots) this will open the query designer and you can add the additional
columns. You then need to change the ColumnCount property of the combo box
so it’s the number of columns returned by the RowSource query.

Once you've amended the control's properties as above you can then add the
code to its AfterUpdate event procedure to assign a value from each column of
the combo box's RowSource to the other bound controls on the form.

Ken Sheridan
Stafford, England

Greg, Thank you for responding. I really appreciate this and it is helpful,
however, I do not have a Row Source. I do have a Record Source. Would I do
the same thing here?

Let me give you my scenario
I have 2 tables ; One is Plan
The other is StudentPlan
I can run an update query to update all of the Student plans if I choose a
Method for every student.

However, this is how I really need it to work:
I have a form based on a Query that displays fields from the StudentPlan and
the StudentData Tables.
The Method field references the Method field in Plan via a ComboBox.

I need for the update to occur for each student as we meet with that student
and determine the proper Method for just him.

I want the fields in the StudentPlan to update to what is currently in the
Plan (with the same Method) when a Method is chosen, and I want this to occur
for only this student so that we can evaluate his Plan and make changes in
some of the fields if needed.

I do something similar for Companies, when a company name is picked from a
list it automatically inserts the address information for that company. On
the form properties under row source query:

SELECT DISTINCTROW [Companies].[Company], [Companies].[Address],
[Companies].[City], [Companies].[St], [Companies].[ZIP] FROM [Companies];

Then the After update - Event Procedure Code:
Private Sub Company_AfterUpdate()
Dim ctl As Control
Set ctl = Me!Company
If Not IsNull(ctl) Then
Me!Address = ctl.Column(1)
Me!City = ctl.Column(2)
Me!St = ctl.Column(3)
Me!Zip = ctl.Column(4)
End If
'inserts company info when company name is chosen from list
End Sub

Hope this helps.

I know this is probably easy, but I am too new to Access.
I have an update query that I would like to invoke on the current record
when the field changes in a combo box field on the form.

For examplee: when the user makes a choice in the Method field, the update
query is invoked for that record and the corresponding fields are updated.

How do I do this?