setvalue action changes record focus

[Larry]

after editing/adding a record in a subform,
I'm executing a macro to update field from the subform to the mainform with a
series of 'setvalue' actions. After executing the 4th setvalue, the record
selection jumps to the first record in the subform and continues executing
with the fields in the first record.

I have a form with a subform displaying several records. After adding or
editing a record in the subform, I have a macro that updates fields in
mainform from a subform with a series of setvalue actions.

After executing the 4th setvalue action, regardless of what that setvalue
is, the focus resets to the first record in the subform and continues
executing with the field values in that record.

Also it doesn't matter exactly what step in the macro, just that it's the
4th setvalue action. I've inserted other action steps between the setvalue
action and it doesn't change, it's always after the 4th setvalue.

I've changed around the order in which the setvalue step execute and it
doesn't matter what the setvalue step does, nothing changes, it always
happens after executing the 4th setvalue action.

This is driving me crazy. Any ideas? ANYONE?

 

[Steve Schapel]

Larry,

I can't throw any light on the problem itself, as it seems very strange
and unexpected. This would normally be the result of a Requery of the
subform, which does not appear to be the case.

However, I might also say that what you have described would in my
experience be a fairly unusual approach. A seriers of SetValue actions
to manipulate data on the main form? I feel there are sure to be better
ways of doing this. Would you mind to let us know the details of what
this is about, with some examples of what you are trying to achieve here?

 

[Larry]

Steve,
Thanks for looking into this. It does seem very strange. And actually I'm
glad this is strange, because it seems like there should be a better way and
I hope you can redirect my line of thought.

Here's the overview of what I'm doing. I'm working with new bank accounts.
Each new account has variety of activities that have to take place a certain
intervals. Typically there are 6 or 7 activities out of about 20 that would
apply to each account. For example: within a couple days of opening, an
address verification card has to be mailed, Account research must be done,
inquiries sent, and responses received, two weeks later 3 phone contacts are
attempted, and changes/orders made as a result. And each activity is likely
done by a different employee so each activity is documented.

This problem entails an Account Overview form (single form) with an
Activities subform (continuous form). (of course several other tables with
code descriptions etc).

As part of the Accounts table, I keep track several status: Status_Code,
Status_Date, Employee, Next_Activity, Next_Date.

As part of the Activities table, after documenting what was done, emps enter
the new Status_Code, Status_Date, Emp, and the Next_Activity to be done and
the Next_Date it should be done. For each of the variety of activities I
need to track what the employee decided for each of those fields, and back in
the Accounts table I track the fields based on the last activity.


This is where the problem occurs. The Activities subform (continuous) is
displaying the list of all previous activities. A new activity record is
added at the bottom. When an activities record is completed I run a macro
using the setvalue to copy each of the status fields from the Activity
subform back to the main Accounts form. When the 4th setvalue is ready to
execute, the record selector resets back to the first Activity record and
execution continues using the field values from the first record instead of
the new record.

As I mentioned, I've moved the setvalue statements all around and it doesn't
matter which field was acted on in the 3rd step or which will be acted on in
the 4th step. In stepping through the macro, when I hit the step button the
record selector jumps back to the first record and the remaining fields are
updated with the wrong values.

I would greatly appreciate any redirection you might suggest, as far as the
overall approach.

thanks

Larry

 

[Steve Schapel]

Larry,

Thanks for the further infromation, and my apologies for the delay in
responding - I hopee you're still around.

It sounds like the main problem here is that you are storing the same
information in two places at once.

If this was a valid thing to do, I would be recommening an Append Query,
rather than a series of SetValue actions.

However, I won't recommend that, because it is not a valid thing to do.
It flouts a basic rule of database design.

What you should do instead, as a basic concept, is to use a query to
return the Next_Activity and Next_Date from the most recent record for
that account in the Activities table, and then display the data directly
from that query onto the main form. In other words, this data should
not be in the Accounts table at all.

Another, perhaps simpler approach, would be to sort the records in the
subform Descending, so the most recent is at the top, and use
conditional fornmatting to highlight the Next_Activity and Next_Date of
the most recent record.

 

[Larry]

Steve,

Thanks! Since there are multiple forms and multiple reports based on the
New Accounts that will need the Next_Action Next_Date fields the query to
pull the most recent info from the Activities file makes the most sense.

Actually, a subform based on the query since after I look at it there a 4
fields to deal with the same was.

Thanks again. This was a bigger project than I wanted to start with. But I
mentioned to my boss I was working on it at home (meaning as a learning
process) ... she thought it was a good idea and suddenly I have a due date to
complete it ... LOL

 

[Steve Schapel]

Larry,

Yes, just a simple single-record subform would be a good approach. One
way to do the query would be like this...
SELECT TOP 1 Next_Action, Next_Date, Other_Field
FROM Activities
WHERE AccountNumber = [Forms]![Account Overview]![AccountNumber]
ORDER BY ActivityDateTime DESC

This assumes you have a timestamp field in the Activities table.
Otherwise, we need to know a "data driven" way of identifying which
Activity record is the most recent.

Enjoyed the story about the "due date" - I think a lot of people would
relate to that.