SetValue Help

[A.C]

Help with SetValue Action

I have a relational database in access 2003 with 2 tables Customers and
Lawn Treatment Service Dates. In the customer table I have a text field
named “1 completed” that I want to populate along with the underlying
table with the word “Complete” when I enter the number 104 in a data
entry form for the field named app# also text field from Lawn
Treatment Service Dates Table for the specific customers record that I
am entering.
I have another form named customer Information that has the field named
“1 completed”from my customer table.
I have created a macro and attached it to the after update property of
the app# field in my data entry form, with the Condition- [app#]=104,
Action -SetValue , Item- [Forms]![Customer Information]![1 Completed]
Expression- “complete”. This work to the extent of populating the field
, but it is populated in more than the one specific customers record.

In addition it will only work if the customer information form is
open.

Does anyone have any ideas of what I need to change!



Thanks A.C.

 

[Steve Schapel]

A.C.,

There are a number of aspects to your question, and a number of
considerations.

With a scenario such as this, probably the most common form design would
be that the Treatment Service Dates form would be a continuous view
subform on the Customer Information form. This would mean that whenever
you were accessing the app# field on the subform, the Customer
Information form would always be open at the current record of the
relevant customer. In this case, you can refer in your macro to the '1
completed' field on the main form, using the Parent syntax, i.e.
Item: [Parent]![1 completed]
Expression: "complete"

If you do not have this setup, and if the Customer Information form will
not necessarily be open at the time, I would suggest dropping the idea
of using a SetValue macro, and use an Update Query instead. You can
make an Update Query to update the [1 completed] field to "complete" and
in the criteria of the CustomerID field (or whatever your primary key
field is), you can refer to the value from the current record of the
Treatment Service form, using syntax such as
[Forms]![NameOfForm]![CustomerID]
You can still put your macro on the AfterUpdate event of app# but this
time use an OpenQuery action to run the update query.

Having said all this, let me say that in database work, it is extremely
unusual that you would need to allocate the value of one field based on
the value of another field, such as what you are doing. I don't know,
of course, as I only have a very litle information about your
database... but I can't help but be suspicious that we are barking up
the wrong tree here. If the value in the '1 completed' field in the
Customers table will always be "complete" whenever there is an entry in
the app# field of one of the records for that customer in the Treatment
Service table, then this information is redundant/duplicated, and it
really shouldn't be done that way in a database, i.e. the '1 completed'
field probably shouldn't even exist. The concept in a database is that
you will always be able to obtain the information as to 1 treatment
completed for any given customer via a very simple query on the
Treatment Service table.

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field or control or database object, and I recommend that this
be changed.

--
Steve Schapel, Microsoft Access MVP

Help with SetValue Action

I have a relational database in access 2003 with 2 tables Customers and
Lawn Treatment Service Dates. In the customer table I have a text field
named “1 completed” that I want to populate along with the underlying
table with the word “Complete” when I enter the number 104 in a data
entry form for the field named app# also text field from Lawn
Treatment Service Dates Table for the specific customers record that I
am entering.
I have another form named customer Information that has the field named
“1 completed”from my customer table.
I have created a macro and attached it to the after update property of
the app# field in my data entry form, with the Condition- [app#]=104,
Action -SetValue , Item- [Forms]![Customer Information]![1 Completed]
Expression- “complete”. This work to the extent of populating the field
, but it is populated in more than the one specific customers record.

In addition it will only work if the customer information form is
open.

Does anyone have any ideas of what I need to change!



Thanks A.C.