Coding Question/Problem

[joe]

Hello Group,

I have a coding problem that I was hoping to get some feedback on.

I am building an application that tracks customer representative
activities. They are responsible for checking in with the customer
every "X" # of days (this # varies from one customer to the next).
Here is where the problem lies.

The first 3 times they contact the customer it needs to be by phone,
every 4th time they contact the customer it needs to be in person. So
I need logic that says if this is the first or second time contacting
the customer then calculate a date based on "X" # of days from the
last call date to determine the next call date. If, this is the 3rd
call then calculate a date to perform an in person contact based on
"X" # of days from the last in-person contact. These date values need
to auto-populate a field saying next date to contact and indicate if
it should be in person or a call. The customer contact process is
perpetual, meaning they never stop contacting the customer in this
fashion so long as they remain a customer.


Any help would be appreciated, thanks!

 

[kingston via AccessMonster.com]

Instead of doing a calculation every time, how about adding a field that
tracked this? For example, ContactVia = 1 for first phone call, 2 for second
phone call, 3 for third phone call, and resets to 0 for the face to face
meeting. You'd know when the next meeting would be based on your X parameter
and you could look up the type of contact required based on the current
ContactVia value.

 

[BWD]

Instead of doing a calculation every time, how about adding a field that
tracked this? For example, ContactVia = 1 for first phone call, 2 for second
phone call, 3 for third phone call, and resets to 0 for the face to face
meeting. You'd know when the next meeting would be based on your X parameter
and you could look up the type of contact required based on the current
ContactVia value.

Thanks for the reply.

I had thought of this approach but the problem I have with it is that
it has to look at the most recent record to determine what code it
is. Moreover, I need to store the date value that is created for
recall into a report. Any ideas of how I can save a calculated form
value into a table?

Thanks again!

 

[kingston via AccessMonster.com]

I'm not sure I understand what you explained, but in order to save a
calculated form value into a table, you need to use an event of some type to
trigger an action. You could add a button and an OnClick event, or you could
use the form's OnCurrent event, or any number of other options. The event
would trigger a procedure that opens a recordset and edits a value or it
could execute an action query. You'd reference the calculated form field
with something like:

[Forms]![FormName]![CalculatedField]

BTW, you wouldn't have to look in the most recent record as this flag could
be attached to the customer record and not necessarily the meeting logs. In
other words, add a field to your customers table and update this field every
time that customer is contacted. I hope you have only one record that
contains customer information and you're just referencing the customer's ID
elsewhere.

Instead of doing a calculation every time, how about adding a field that
tracked this? For example, ContactVia = 1 for first phone call, 2 for second

[quoted text clipped - 31 lines]

- Show quoted text -

Thanks for the reply.

I had thought of this approach but the problem I have with it is that
it has to look at the most recent record to determine what code it
is. Moreover, I need to store the date value that is created for
recall into a report. Any ideas of how I can save a calculated form
value into a table?

Thanks again!

 

[joe]

I'm not sure I understand what you explained, but in order to save a
calculated form value into a table, you need to use an event of some type to
trigger an action. You could add a button and an OnClick event, or you could
use the form's OnCurrent event, or any number of other options. The event
would trigger a procedure that opens a recordset and edits a value or it
could execute an action query. You'd reference the calculated form field
with something like:

[Forms]![FormName]![CalculatedField]

BTW, you wouldn't have to look in the most recent record as this flag could
be attached to the customer record and not necessarily the meeting logs. In
other words, add a field to your customers table and update this field every
time that customer is contacted. I hope you have only one record that
contains customer information and you're just referencing the customer's ID
elsewhere.

Instead of doing a calculation every time, how about adding a field that
tracked this? For example, ContactVia = 1 for first phone call, 2 for second

[quoted text clipped - 31 lines]

- Show quoted text -

Thanks for the reply.

I had thought of this approach but the problem I have with it is that
it has to look at the most recent record to determine what code it
is. Moreover, I need to store the date value that is created for
recall into a report. Any ideas of how I can save a calculated form
value into a table?

Thanks again!

OK, I am following what your response is here. The structure of the
database is that I have a customers table and a contact table. To
enter data I have a primary form (based on Company table) and a sub
Form (based on contact table) Following your logic I created an
update query that updates the (company table) [next date to contact]
field based on the field [current contact date +30] in the sub form.
Works well. BUT!

I tried to place this into VBA code as docmd.openquery (This allows me
to suppress error messages and such) which also works. However, it
only looks at the first record in the subform and will not calculate
based on subsequent data entries.

For example if I have company A and I visited them on 7/7/07 and then
run my VBA code (docmd.openquery) the process works fine. Next time I
update Company A with a visit on 10/1/07 and then run my
docmd.openquery the code insists on looking at the prior record (from
7/7/07). Is there some VBA command that forces VBA to look at the new
record in the sub form before running the code?

Many Thanks Again!

 

[joe]

I'm not sure I understand what you explained, but in order to save a
calculated form value into a table, you need to use an event of some type to
trigger an action. You could add a button and an OnClick event, or you could
use the form's OnCurrent event, or any number of other options. The event
would trigger a procedure that opens a recordset and edits a value or it
could execute an action query. You'd reference the calculated form field
with something like:

[Forms]![FormName]![CalculatedField]

BTW, you wouldn't have to look in the most recent record as this flag could
be attached to the customer record and not necessarily the meeting logs. In
other words, add a field to your customers table and update this field every
time that customer is contacted. I hope you have only one record that
contains customer information and you're just referencing the customer's ID
elsewhere.

On Jul 6, 11:18 am, "kingston via AccessMonster.com" <u27511@uwe>
wrote:
Instead of doing a calculation every time, how about adding a field that
tracked this? For example, ContactVia = 1 for first phone call, 2 for second
[quoted text clipped - 31 lines]
- Show quoted text -
Thanks for the reply.
I had thought of this approach but the problem I have with it is that
it has to look at the most recent record to determine what code it
is. Moreover, I need to store the date value that is created for
recall into a report. Any ideas of how I can save a calculated form
value into a table?
Thanks again!

- Show quoted text -

OK, I am following what your response is here. The structure of the
database is that I have a customers table and a contact table. To
enter data I have a primary form (based on Company table) and a sub
Form (based on contact table) Following your logic I created an
update query that updates the (company table) [next date to contact]
field based on the field [current contact date +30] in the sub form.
Works well. BUT!

I tried to place this into VBA code as docmd.openquery (This allows me
to suppress error messages and such) which also works. However, it
only looks at the first record in the subform and will not calculate
based on subsequent data entries.

For example if I have company A and I visited them on 7/7/07 and then
run my VBA code (docmd.openquery) the process works fine. Next time I
update Company A with a visit on 10/1/07 and then run my
docmd.openquery the code insists on looking at the prior record (from
7/7/07). Is there some VBA command that forces VBA to look at the new
record in the sub form before running the code?

Many Thanks Again!- Hide quoted text -

- Show quoted text -

Thought I would update you and tell you that I figured out a solution
using an append query with a command button that updated the relevant
field. Still not sure how to use a recordset within VBA to look at
the correct value from the subform but that is the beauty of Access,
you can do things multiple ways!