One Form 2 Another and Check

[Charles L. Phillips]

Hello,
I am using MS-Access 97.
I have created 2 forms:
1. frmCustomers
2. frmMailingList

The frmCustomers is used to send brochures, data, marketing info, promotions
& etc.
The frmMailingList is used to store entries of potential customers for
future business.
Information is sent to members in the frmMailingList. Once a member or group
of members buys products or services, they are considered customers.
I need a button/process or technique that allows me to automatically move
data (member or group of members) from the frmMailingList to the
frmCustomers... Then I need a button/process or technique, that
automatically checks the frmMailingList for NEW entries at 5 minutes after
Startup & print that data, every Thursday...

I know I'm asking alot, but can/will someone point me to in the right
direction or some sample code...


Thank you,

 

[Jeff Boyce]

Charles

It may be just a semantic quibble, but I don't see anything about your
underlying tables. You aren't really putting data into forms, are you?
They would have to be unbound forms, and you would still need to then
marshal the data into and out of the underlying tables.

And I may not be fully following, but it sounds like you are attempting to
make duplicate copies of the same data. MS Access is a relational database,
so you don't need to do that in most cases.

If you want a simple way to track which of your contacts is a "customer",
consider adding a Yes/No field to the underlying contact table -- "This
contact is a Customer (y/n)".

 

[Charles L. Phillips]

Hello,
Did you not understand the question? Or maybe I worded the problem poorly...
I am putting data in both forms. The form has fields. I did not think I
would have to explain that. I do not want to manually copy or move data from
frmMailingList to frmCustomers. If I am duplicating data, the data is
between 2 different forms. If you have a setup where, a group of people are
making entries to the frmMailingList everyday, you want that information
available to your sales staff. I believe, feel & think you need another
form, frmCustomers, once a sale has been made. The sales person(s) can go
back to frmMailingList to find a new member or group of members to work on,
without worrying or guessing if that member or group of members are already
customers. Maybe if I stated, members moved from frmMailingList to
frmCustomers to be deleted once moved & saved. I do not want sales people do
the checking, I want the "system" to do the checking & reporting...


Charles L. Phillips

Charles

It may be just a semantic quibble, but I don't see anything about your
underlying tables. You aren't really putting data into forms, are you?
They would have to be unbound forms, and you would still need to then
marshal the data into and out of the underlying tables.

And I may not be fully following, but it sounds like you are attempting to
make duplicate copies of the same data. MS Access is a relational database,
so you don't need to do that in most cases.

If you want a simple way to track which of your contacts is a "customer",
consider adding a Yes/No field to the underlying contact table -- "This
contact is a Customer (y/n)".

 

[Douglas J. Steele]

Perhaps it is that you haven't worded the problem adequately, but forms do
NOT have data: they provide a means to store data in tables.

There's nothing at all that prevents you from storing the data from two
different forms in a single table. As Jeff suggested, all you need to do is
have a flag that indicates whether or not the specific entry is a customer.

You can create two queries on that single table, one that returns only those
rows where the Customer flag is False, and another returning only those rows
where the Customer flag is True, and use those two separate queries as the
record sources for the individual forms.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,
Did you not understand the question? Or maybe I worded the problem
poorly...
I am putting data in both forms. The form has fields. I did not think I
would have to explain that. I do not want to manually copy or move data
from
frmMailingList to frmCustomers. If I am duplicating data, the data is
between 2 different forms. If you have a setup where, a group of people
are
making entries to the frmMailingList everyday, you want that information
available to your sales staff. I believe, feel & think you need another
form, frmCustomers, once a sale has been made. The sales person(s) can go
back to frmMailingList to find a new member or group of members to work
on,
without worrying or guessing if that member or group of members are
already
customers. Maybe if I stated, members moved from frmMailingList to
frmCustomers to be deleted once moved & saved. I do not want sales people
do
the checking, I want the "system" to do the checking & reporting...


Charles L. Phillips

 

[Guest]

May I provide another view of this question? Jeff and Douglas are right on
track. I'm making an assumption that you may have inherited this database
and are familiar with pre-existing forms in this database. The data that
users view in both the CUSTOMERS form (used to view, edit, or enter dew data)
and the MAILING LIST form are actually stored in the same underlying (record
source) table which identifies personal information about PERSONS (individual
customers or potential customers or contact persons representing
organizations that your firm is identifying as customers or potential
customers). This table probably has a long list of fields identifying name,
address, telephone/fax/email infromation, etc.

As Jeff suggested, the master table should have a field marked as something
like "Customer_YN" (a yes/no field). Each of your two forms, CUSTOMERS and
MAILING LIST, has a query using the same master "PERSONS" table. In the
CUSTOMERS query, you will check, "yes" in the field, "Customer_YN". When you
run that query, Microsoft will provide you only those records (PERSONS) where
the "Customer_YN" field is checked off as "yes". I'm assuming your sales
people, in talking directly with persons in the MAILING LIST, will check off
"Customer_YN" when they make a sale from the potential customer in the
MAILING LIST.

The MAILING LIST form that the sales people use to make their calls to
potential customers is based on a second query using the PERSONS table where
the the "Customer_YN" field is filtered by your writing in "No" (instead of
"YES").

If you do not have sales persons making direct calls to prospective
customers but simply depend on mail orders to come in, someone will have to
enter the MAILING LIST form to check "Yes" for the new customers. But I'm
assuming you're getting new customers either from new orders or direct calls.


So, with all that said, your MAILING LIST form will only show potential
customers, while your CUSTOMERS form will show only customers --- based on
the "Customer_YN" field being either checked off by staff as either "yes" or
"no".

To address your other concern about producing a report of new entries, you
can create a REPORT with the same underlying query for the MAILING LIST.
Your "PERSONS" table should have an automatic "CreateDate" field providing
the date the record was created. You can sort the output data in your
MAILING LIST REPORT in descending order, so that the latest ("new") entries
(potential customers) are at the top of your list. The list, of course, will
not show "customers", only the potential customers. This list in the REPORT
(as in the FORM) will provide all potential customers from "Day 1". To limit
the list, you can set the CreateDate field in the query to display all new
records with a time frame, such as the last seven calendar days. You can
otherwise control the date range by inserting a phrase in the CreateDate
field of the query for the REPORT, such as "BETWEEN [Enter beginning date]
AND [Enter ending date]".

You suggested that the REPORT be produced "automatically", which can be done
as you suggested, shortly after the individual opens the MAILING LIST form.
I won't go into details now with how that can be done, but a better way would
be to have a control button on either the MAILING LIST form or the MENU form
preceding the MAILING LIST form that, when clicked, would produce the REPORT.
The person producing the REPORT would respond to the pop-up dialog box, once
he/she clicks on the control button to open the report for print/preview,
"Enter beginning date" followed by "Enter ending date". The REPORT produces,
in descending order, the "new" entries (records) fitting into the date range
specified.

Another issue to address are the records in the master PERSONS table of
those persons who remain long term as "potential cusotmers", never reaching
the CUSTOMER status. At some point, you will of course want to archive these
contacts or simply delete them from the table if the information in those
records becomes obsolete or forever unproductive.

Well, this has been a mouthfull, but I hope it offers some clarity, or at
least something for you to think about when studying Jeff's and Douglas'
helpful comments in how to tackle your problem. Perhaps Jeff or Douglas can
amplify, and of course, I'd be glad to clarify, as I'm sure further
discussion may be in order.

 

[Charles L. Phillips]

Hello,
"Tank" you are hitting the nail on the head, pitching dollars not pennies,
flying with eagles & not running with turkeys... You are absolutely right!!!
First, let me say, I was not discarding Jeff or Douglas... I just needed
someone with MORE experience to validate my logic in this approach.
Tank everything you pointed is 97.9999% correct. How did you know???
The company is getting new potential customers from canvassing, phones call,
internet surveys & etc.
As for as "potential customers", that have been in the database an over
extended time, but have not become customers, I leave that status up to the
client, and will build into the database how they want that information
handled...


Thank you,
Charles L. Phillips

May I provide another view of this question? Jeff and Douglas are right on
track. I'm making an assumption that you may have inherited this database
and are familiar with pre-existing forms in this database. The data that
users view in both the CUSTOMERS form (used to view, edit, or enter dew data)
and the MAILING LIST form are actually stored in the same underlying (record
source) table which identifies personal information about PERSONS (individual
customers or potential customers or contact persons representing
organizations that your firm is identifying as customers or potential
customers). This table probably has a long list of fields identifying name,
address, telephone/fax/email infromation, etc.

As Jeff suggested, the master table should have a field marked as something
like "Customer_YN" (a yes/no field). Each of your two forms, CUSTOMERS and
MAILING LIST, has a query using the same master "PERSONS" table. In the
CUSTOMERS query, you will check, "yes" in the field, "Customer_YN". When you
run that query, Microsoft will provide you only those records (PERSONS) where
the "Customer_YN" field is checked off as "yes". I'm assuming your sales
people, in talking directly with persons in the MAILING LIST, will check off
"Customer_YN" when they make a sale from the potential customer in the
MAILING LIST.

The MAILING LIST form that the sales people use to make their calls to
potential customers is based on a second query using the PERSONS table where
the the "Customer_YN" field is filtered by your writing in "No" (instead of
"YES").

If you do not have sales persons making direct calls to prospective
customers but simply depend on mail orders to come in, someone will have to
enter the MAILING LIST form to check "Yes" for the new customers. But I'm
assuming you're getting new customers either from new orders or direct calls.


So, with all that said, your MAILING LIST form will only show potential
customers, while your CUSTOMERS form will show only customers --- based on
the "Customer_YN" field being either checked off by staff as either "yes" or
"no".

To address your other concern about producing a report of new entries, you
can create a REPORT with the same underlying query for the MAILING LIST.
Your "PERSONS" table should have an automatic "CreateDate" field providing
the date the record was created. You can sort the output data in your
MAILING LIST REPORT in descending order, so that the latest ("new") entries
(potential customers) are at the top of your list. The list, of course, will
not show "customers", only the potential customers. This list in the REPORT
(as in the FORM) will provide all potential customers from "Day 1". To limit
the list, you can set the CreateDate field in the query to display all new
records with a time frame, such as the last seven calendar days. You can
otherwise control the date range by inserting a phrase in the CreateDate
field of the query for the REPORT, such as "BETWEEN [Enter beginning date]
AND [Enter ending date]".

You suggested that the REPORT be produced "automatically", which can be done
as you suggested, shortly after the individual opens the MAILING LIST form.
I won't go into details now with how that can be done, but a better way would
be to have a control button on either the MAILING LIST form or the MENU form
preceding the MAILING LIST form that, when clicked, would produce the REPORT.
The person producing the REPORT would respond to the pop-up dialog box, once
he/she clicks on the control button to open the report for print/preview,
"Enter beginning date" followed by "Enter ending date". The REPORT produces,
in descending order, the "new" entries (records) fitting into the date range
specified.

Another issue to address are the records in the master PERSONS table of
those persons who remain long term as "potential cusotmers", never reaching
the CUSTOMER status. At some point, you will of course want to archive these
contacts or simply delete them from the table if the information in those
records becomes obsolete or forever unproductive.

Well, this has been a mouthfull, but I hope it offers some clarity, or at
least something for you to think about when studying Jeff's and Douglas'
helpful comments in how to tackle your problem. Perhaps Jeff or Douglas can
amplify, and of course, I'd be glad to clarify, as I'm sure further
discussion may be in order.
--
Tank

Hello,
I am using MS-Access 97.
I have created 2 forms:
1. frmCustomers
2. frmMailingList

The frmCustomers is used to send brochures, data, marketing info, promotions
& etc.
The frmMailingList is used to store entries of potential customers for
future business.
Information is sent to members in the frmMailingList. Once a member or group
of members buys products or services, they are considered customers.
I need a button/process or technique that allows me to automatically move
data (member or group of members) from the frmMailingList to the
frmCustomers... Then I need a button/process or technique, that
automatically checks the frmMailingList for NEW entries at 5 minutes after
Startup & print that data, every Thursday...

I know I'm asking alot, but can/will someone point me to in the right
direction or some sample code...


Thank you,

 

[Guest]

Thanks for the comments, Charles. Re your inquiry about producing a list of
"potential customers" (or "Prospects" in fund raising lingo) after extracting
the customers, I suggested a chronological descending sort order for the
report using a record "CreateDate" field. If your report list is quite long,
you could use another method to separate out the "prospects" by using a
prospect status field. Example, P1, high-priority potential customer, P2,
mid-priority potential customer, P3, low-priority potential customer, etc.
Then, include that status in your sort, so that your P1 high priority
potential cusomters are at the top of the list in chronological descending
record create date order. P2 prospects then follow with P3 prospects after
that.

In fact, in lieu of using a "Customer_YN" yes/no field, you could use a
"customer/prospect status" code system such as, P1, P2, P3, C1, C2, C3, etc.

C1 could be a new Customer; C2, a steady or valuable customer; C3, a
seasoned, advanced, highly valuable customer, etc.

When the prospect's status reaches C1, the query will exclude that person
from the MAILING LIST data entry form and include the person in the CUSTOMER
data entry form, based on the query set-up I suggested earlier and summarized
below:

F1_MailingList_DataEntry controled by
Q1_MailingList_Query from T1_PERSONS_Table
with field, Customer_YN marked as NO or field Status as between P1 and P3

F2_CUSTOMERS_DataEntry controled by
Q2_Customers_Query from T1_PERSONS_Table
with field, Customer_YN marked as YES or field Status as between C1 and C3
--
Tank

Hello,
"Tank" you are hitting the nail on the head, pitching dollars not pennies,
flying with eagles & not running with turkeys... You are absolutely right!!!
First, let me say, I was not discarding Jeff or Douglas... I just needed
someone with MORE experience to validate my logic in this approach.
Tank everything you pointed is 97.9999% correct. How did you know???
The company is getting new potential customers from canvassing, phones call,
internet surveys & etc.
As for as "potential customers", that have been in the database an over
extended time, but have not become customers, I leave that status up to the
client, and will build into the database how they want that information
handled...


Thank you,
Charles L. Phillips

May I provide another view of this question? Jeff and Douglas are right on
track. I'm making an assumption that you may have inherited this database
and are familiar with pre-existing forms in this database. The data that
users view in both the CUSTOMERS form (used to view, edit, or enter dew data)
and the MAILING LIST form are actually stored in the same underlying (record
source) table which identifies personal information about PERSONS (individual
customers or potential customers or contact persons representing
organizations that your firm is identifying as customers or potential
customers). This table probably has a long list of fields identifying name,
address, telephone/fax/email infromation, etc.

As Jeff suggested, the master table should have a field marked as something
like "Customer_YN" (a yes/no field). Each of your two forms, CUSTOMERS and
MAILING LIST, has a query using the same master "PERSONS" table. In the
CUSTOMERS query, you will check, "yes" in the field, "Customer_YN". When you
run that query, Microsoft will provide you only those records (PERSONS) where
the "Customer_YN" field is checked off as "yes". I'm assuming your sales
people, in talking directly with persons in the MAILING LIST, will check off
"Customer_YN" when they make a sale from the potential customer in the
MAILING LIST.

The MAILING LIST form that the sales people use to make their calls to
potential customers is based on a second query using the PERSONS table where
the the "Customer_YN" field is filtered by your writing in "No" (instead of
"YES").

If you do not have sales persons making direct calls to prospective
customers but simply depend on mail orders to come in, someone will have to
enter the MAILING LIST form to check "Yes" for the new customers. But I'm
assuming you're getting new customers either from new orders or direct calls.


So, with all that said, your MAILING LIST form will only show potential
customers, while your CUSTOMERS form will show only customers --- based on
the "Customer_YN" field being either checked off by staff as either "yes" or
"no".

To address your other concern about producing a report of new entries, you
can create a REPORT with the same underlying query for the MAILING LIST.
Your "PERSONS" table should have an automatic "CreateDate" field providing
the date the record was created. You can sort the output data in your
MAILING LIST REPORT in descending order, so that the latest ("new") entries
(potential customers) are at the top of your list. The list, of course, will
not show "customers", only the potential customers. This list in the REPORT
(as in the FORM) will provide all potential customers from "Day 1". To limit
the list, you can set the CreateDate field in the query to display all new
records with a time frame, such as the last seven calendar days. You can
otherwise control the date range by inserting a phrase in the CreateDate
field of the query for the REPORT, such as "BETWEEN [Enter beginning date]
AND [Enter ending date]".

You suggested that the REPORT be produced "automatically", which can be done
as you suggested, shortly after the individual opens the MAILING LIST form.
I won't go into details now with how that can be done, but a better way would
be to have a control button on either the MAILING LIST form or the MENU form
preceding the MAILING LIST form that, when clicked, would produce the REPORT.
The person producing the REPORT would respond to the pop-up dialog box, once
he/she clicks on the control button to open the report for print/preview,
"Enter beginning date" followed by "Enter ending date". The REPORT produces,
in descending order, the "new" entries (records) fitting into the date range
specified.

Another issue to address are the records in the master PERSONS table of
those persons who remain long term as "potential cusotmers", never reaching
the CUSTOMER status. At some point, you will of course want to archive these
contacts or simply delete them from the table if the information in those
records becomes obsolete or forever unproductive.

Well, this has been a mouthfull, but I hope it offers some clarity, or at
least something for you to think about when studying Jeff's and Douglas'
helpful comments in how to tackle your problem. Perhaps Jeff or Douglas can
amplify, and of course, I'd be glad to clarify, as I'm sure further
discussion may be in order.
--
Tank

Hello,
I am using MS-Access 97.
I have created 2 forms:
1. frmCustomers
2. frmMailingList

The frmCustomers is used to send brochures, data, marketing info, promotions
& etc.
The frmMailingList is used to store entries of potential customers for
future business.
Information is sent to members in the frmMailingList. Once a member or group
of members buys products or services, they are considered customers.
I need a button/process or technique that allows me to automatically move
data (member or group of members) from the frmMailingList to the
frmCustomers... Then I need a button/process or technique, that
automatically checks the frmMailingList for NEW entries at 5 minutes after
Startup & print that data, every Thursday...

I know I'm asking alot, but can/will someone point me to in the right
direction or some sample code...


Thank you,

 

[Charles L. Phillips]

Hello,
Sorry for the late response, had a family problem that needed my FULL
attention.
I'm letting the customer decide how they want to prioritize their customers
& how they want the info dist.
My next question, is their a book I can read on this???


Charles L. Phillips

Thanks for the comments, Charles. Re your inquiry about producing a list of
"potential customers" (or "Prospects" in fund raising lingo) after extracting
the customers, I suggested a chronological descending sort order for the
report using a record "CreateDate" field. If your report list is quite long,
you could use another method to separate out the "prospects" by using a
prospect status field. Example, P1, high-priority potential customer, P2,
mid-priority potential customer, P3, low-priority potential customer, etc.
Then, include that status in your sort, so that your P1 high priority
potential cusomters are at the top of the list in chronological descending
record create date order. P2 prospects then follow with P3 prospects after
that.

In fact, in lieu of using a "Customer_YN" yes/no field, you could use a
"customer/prospect status" code system such as, P1, P2, P3, C1, C2, C3, etc.

C1 could be a new Customer; C2, a steady or valuable customer; C3, a
seasoned, advanced, highly valuable customer, etc.

When the prospect's status reaches C1, the query will exclude that person
from the MAILING LIST data entry form and include the person in the CUSTOMER
data entry form, based on the query set-up I suggested earlier and summarized
below:

F1_MailingList_DataEntry controled by
Q1_MailingList_Query from T1_PERSONS_Table
with field, Customer_YN marked as NO or field Status as between P1 and P3

F2_CUSTOMERS_DataEntry controled by
Q2_Customers_Query from T1_PERSONS_Table
with field, Customer_YN marked as YES or field Status as between C1 and C3
--
Tank

Hello,
"Tank" you are hitting the nail on the head, pitching dollars not pennies,
flying with eagles & not running with turkeys... You are absolutely right!!!
First, let me say, I was not discarding Jeff or Douglas... I just needed
someone with MORE experience to validate my logic in this approach.
Tank everything you pointed is 97.9999% correct. How did you know???
The company is getting new potential customers from canvassing, phones call,
internet surveys & etc.
As for as "potential customers", that have been in the database an over
extended time, but have not become customers, I leave that status up to the
client, and will build into the database how they want that information
handled...


Thank you,
Charles L. Phillips

May I provide another view of this question? Jeff and Douglas are

right
on

track. I'm making an assumption that you may have inherited this database
and are familiar with pre-existing forms in this database. The data that
users view in both the CUSTOMERS form (used to view, edit, or enter

dew
data)

and the MAILING LIST form are actually stored in the same underlying (record
source) table which identifies personal information about PERSONS (individual
customers or potential customers or contact persons representing
organizations that your firm is identifying as customers or potential
customers). This table probably has a long list of fields identifying name,
address, telephone/fax/email infromation, etc.

As Jeff suggested, the master table should have a field marked as something
like "Customer_YN" (a yes/no field). Each of your two forms,

CUSTOMERS
and

MAILING LIST, has a query using the same master "PERSONS" table. In the
CUSTOMERS query, you will check, "yes" in the field, "Customer_YN".

When
you

run that query, Microsoft will provide you only those records

(PERSONS)
where

the "Customer_YN" field is checked off as "yes". I'm assuming your sales
people, in talking directly with persons in the MAILING LIST, will

check
off

"Customer_YN" when they make a sale from the potential customer in the
MAILING LIST.

The MAILING LIST form that the sales people use to make their calls to
potential customers is based on a second query using the PERSONS table where
the the "Customer_YN" field is filtered by your writing in "No"

(instead
of

"YES").

If you do not have sales persons making direct calls to prospective
customers but simply depend on mail orders to come in, someone will

have
to

enter the MAILING LIST form to check "Yes" for the new customers. But I'm
assuming you're getting new customers either from new orders or direct calls.


So, with all that said, your MAILING LIST form will only show potential
customers, while your CUSTOMERS form will show only customers --- based on
the "Customer_YN" field being either checked off by staff as either

"yes"
or

"no".

To address your other concern about producing a report of new entries, you
can create a REPORT with the same underlying query for the MAILING LIST.
Your "PERSONS" table should have an automatic "CreateDate" field providing
the date the record was created. You can sort the output data in your
MAILING LIST REPORT in descending order, so that the latest ("new") entries
(potential customers) are at the top of your list. The list, of

course,
will

not show "customers", only the potential customers. This list in the REPORT
(as in the FORM) will provide all potential customers from "Day 1".

To
limit

the list, you can set the CreateDate field in the query to display all new
records with a time frame, such as the last seven calendar days. You can
otherwise control the date range by inserting a phrase in the CreateDate
field of the query for the REPORT, such as "BETWEEN [Enter beginning date]
AND [Enter ending date]".

You suggested that the REPORT be produced "automatically", which can

be
done

as you suggested, shortly after the individual opens the MAILING LIST form.
I won't go into details now with how that can be done, but a better

way
would

be to have a control button on either the MAILING LIST form or the

MENU
form

preceding the MAILING LIST form that, when clicked, would produce the REPORT.
The person producing the REPORT would respond to the pop-up dialog

box,
once

he/she clicks on the control button to open the report for print/preview,
"Enter beginning date" followed by "Enter ending date". The REPORT produces,
in descending order, the "new" entries (records) fitting into the date range
specified.

Another issue to address are the records in the master PERSONS table of
those persons who remain long term as "potential cusotmers", never reaching
the CUSTOMER status. At some point, you will of course want to

archive
these

contacts or simply delete them from the table if the information in those
records becomes obsolete or forever unproductive.

Well, this has been a mouthfull, but I hope it offers some clarity, or at
least something for you to think about when studying Jeff's and Douglas'
helpful comments in how to tackle your problem. Perhaps Jeff or

Douglas
can

amplify, and of course, I'd be glad to clarify, as I'm sure further
discussion may be in order.
--
Tank



:

Hello,
I am using MS-Access 97.
I have created 2 forms:
1. frmCustomers
2. frmMailingList

The frmCustomers is used to send brochures, data, marketing info, promotions
& etc.
The frmMailingList is used to store entries of potential customers for
future business.
Information is sent to members in the frmMailingList. Once a member

or
group

of members buys products or services, they are considered customers.
I need a button/process or technique that allows me to automatically move
data (member or group of members) from the frmMailingList to the
frmCustomers... Then I need a button/process or technique, that
automatically checks the frmMailingList for NEW entries at 5 minutes after
Startup & print that data, every Thursday...

I know I'm asking alot, but can/will someone point me to in the right
direction or some sample code...


Thank you,