SetValue from a subform to a new form

[Puddlypooh]

I have a form "frmBusinessMarketing" with a subform
"subfrmBusinessContactsMarketingDate". In the sub form I need to pick up the
first and last name of the business contact. I have the SetValue working to
pick up the business ID from the form but everytime I run the macro to pick
up the names from the subform I get the following message.

"Microsoft Office Access can't find the form
'subformBusinessContactsMarketingDate' referred to in a macre expression to
Visual Basic Code.

*The form you referenced may be closed or may not exist in this database.
* Microsoft Office Access may have encountered a compile error in a Visual
Basic module for the form."

I couldn't even find a module for the form.

Any help will be wonderful.

 

[Steve Schapel]

Puddly,

Can you please give details of what you have tried so far, and what is
in the macro that is producing the problem.

Maybe you could also explain something about your purpose or what you
are trying to achieve here... on the face of what you have said so far,
it would appear to be a very unusual requirement.

 

[Puddlypooh]

Steve,

Ok some background. In this business each client may have a number of
contacts, eg the local council has a number of departments and in some
departments it may have 2 or 3 contacts. So the form I am working on now is a
data entry form for marketing items that will be sent out. So the idea is
that the data entry person will find the correct business in the
"frmBusinessMarketing" form and then the correct contact in the
"subfrmBusinessContactsMarketingDate" subform. Then to ensure that the
marketing materials goes to the correct contact (noting that marketing
materials are sent a number of times during the year) I want the Business ID
and the contact name to show on the marketing form before the rest of the
data entry can be done.

At this point I have used the setValue for the Business ID with no problems.
Yeat using SetValue and the expression
[Forms]![subfrmBusinessContactsMarketingDate]![First Name] I get the error
message I mentioned before. I have rewritten the macro a number of times,
since I have found that macros can be tempremental; and redone the form. I am
stumped.

 

[Steve Schapel]

Puddly,

I see. Well, if you don't mind me saying so, it looks like you have
made a fundamental misunderstanding of how databases work.

You have given a description of the business processes, which is nice,
but I would really need you to present it in data structure language
(i.e. tables and fields and relationships) before I can make real
specific comments.

But do I understand you correctly that the marketing items form is a
standalone form, i.e. it is not a subform on one of the other forms you
mentioned? So you are recording details of the sending out of marketing
materials. So presumably there is a table where this information is
recorded? Is the distribution event to more than one business, and/or
more than one contact? Or is your model that sending the marketing
materials to one contact person within one business is regarded as a
distinct distribution event? Either way, the *only* information you
need in this table to identify who the material is sent out to, is the
ID of the contact.

And you would not use a SetValue macro to transmit data from one form to
another in this way. You will end up in all sorts of tangles doing
that. The best way probably is to completely ignore the other
form/subform assembly, and provide data entry via comboboxes on your
"SendOut" form to allow for the identification of the recipient, and
recording of their ID, and the display of the related data such as
business and name from the related tables.

If for some reason it is in fact senseible to show data from other forms
on the SendOut form, then you would use unbound controls, with their
Control Source properties set to reference the controls on the other forms.

--
Steve Schapel, Microsoft Access MVP

Steve,

Ok some background. In this business each client may have a number of
contacts, eg the local council has a number of departments and in some
departments it may have 2 or 3 contacts. So the form I am working on now is a
data entry form for marketing items that will be sent out. So the idea is
that the data entry person will find the correct business in the
"frmBusinessMarketing" form and then the correct contact in the
"subfrmBusinessContactsMarketingDate" subform. Then to ensure that the
marketing materials goes to the correct contact (noting that marketing
materials are sent a number of times during the year) I want the Business ID
and the contact name to show on the marketing form before the rest of the
data entry can be done.

At this point I have used the setValue for the Business ID with no problems.
Yeat using SetValue and the expression
[Forms]![subfrmBusinessContactsMarketingDate]![First Name] I get the error
message I mentioned before. I have rewritten the macro a number of times,
since I have found that macros can be tempremental; and redone the form. I am
stumped.

 

[Puddlypooh]

Steve,

I suspose I haven't really given you a lot of information on the actual
database. This is to be a marketing and sales database. I am currently
working on the marketing side of things. So each business needs a
relationship to the marketing and sales sides, while being an individual
entity.

As each business can have none, one or multiple departments and one or more
contacts. So when we enter marketing information the de person looks up the
business and can then change departments and contact people. From there they
use a button to open a new form to enter the marketing details. (The previous
form I was talking about will also be used to check details before entering
sales data.) So after explaining that.....I have a table with Business ID as
the Primary Key and Trading Name data (Also has business specific codes), a
table with department Info using Business ID as a one to many relationship,
and a table with Business Contact details again using the Business ID as a
one to many relationship. My Marketing table uses the Trading Name table as a
one to many relationship using the Business ID. Hopefully you have a picture
of how the relationships work.

Now my Business form has the Trading Name table data in the main form and
the table data from department as a subform and the table data from Business
Contact as a subform (remembering that the trading name may have multiple
departments and/or contacts). So the Business ID goes across from the Trading
Name part of the form, but I need the contact name to go from the Business
Contact subform to the marketing form. That information will then be saved in
the table. Since the de person has already checked the information the
Business ID and Names won't show on the next form but will be added to the
table.

I hope this makes what I need to do a bit clearer.
--
At the end of the day a new one begins

Puddly,

I see. Well, if you don't mind me saying so, it looks like you have
made a fundamental misunderstanding of how databases work.

You have given a description of the business processes, which is nice,
but I would really need you to present it in data structure language
(i.e. tables and fields and relationships) before I can make real
specific comments.

But do I understand you correctly that the marketing items form is a
standalone form, i.e. it is not a subform on one of the other forms you
mentioned? So you are recording details of the sending out of marketing
materials. So presumably there is a table where this information is
recorded? Is the distribution event to more than one business, and/or
more than one contact? Or is your model that sending the marketing
materials to one contact person within one business is regarded as a
distinct distribution event? Either way, the *only* information you
need in this table to identify who the material is sent out to, is the
ID of the contact.

And you would not use a SetValue macro to transmit data from one form to
another in this way. You will end up in all sorts of tangles doing
that. The best way probably is to completely ignore the other
form/subform assembly, and provide data entry via comboboxes on your
"SendOut" form to allow for the identification of the recipient, and
recording of their ID, and the display of the related data such as
business and name from the related tables.

If for some reason it is in fact senseible to show data from other forms
on the SendOut form, then you would use unbound controls, with their
Control Source properties set to reference the controls on the other forms.

--
Steve Schapel, Microsoft Access MVP

Steve,

Ok some background. In this business each client may have a number of
contacts, eg the local council has a number of departments and in some
departments it may have 2 or 3 contacts. So the form I am working on now is a
data entry form for marketing items that will be sent out. So the idea is
that the data entry person will find the correct business in the
"frmBusinessMarketing" form and then the correct contact in the
"subfrmBusinessContactsMarketingDate" subform. Then to ensure that the
marketing materials goes to the correct contact (noting that marketing
materials are sent a number of times during the year) I want the Business ID
and the contact name to show on the marketing form before the rest of the
data entry can be done.

At this point I have used the setValue for the Business ID with no problems.
Yeat using SetValue and the expression
[Forms]![subfrmBusinessContactsMarketingDate]![First Name] I get the error
message I mentioned before. I have rewritten the macro a number of times,
since I have found that macros can be tempremental; and redone the form. I am
stumped.

 

[Puddlypooh]

Oops also forgot to say there will be at the start more than 5000 trading
names so closer to 6500 business contacts
--
At the end of the day a new one begins

Steve,

I suspose I haven't really given you a lot of information on the actual
database. This is to be a marketing and sales database. I am currently
working on the marketing side of things. So each business needs a
relationship to the marketing and sales sides, while being an individual
entity.

As each business can have none, one or multiple departments and one or more
contacts. So when we enter marketing information the de person looks up the
business and can then change departments and contact people. From there they
use a button to open a new form to enter the marketing details. (The previous
form I was talking about will also be used to check details before entering
sales data.) So after explaining that.....I have a table with Business ID as
the Primary Key and Trading Name data (Also has business specific codes), a
table with department Info using Business ID as a one to many relationship,
and a table with Business Contact details again using the Business ID as a
one to many relationship. My Marketing table uses the Trading Name table as a
one to many relationship using the Business ID. Hopefully you have a picture
of how the relationships work.

Now my Business form has the Trading Name table data in the main form and
the table data from department as a subform and the table data from Business
Contact as a subform (remembering that the trading name may have multiple
departments and/or contacts). So the Business ID goes across from the Trading
Name part of the form, but I need the contact name to go from the Business
Contact subform to the marketing form. That information will then be saved in
the table. Since the de person has already checked the information the
Business ID and Names won't show on the next form but will be added to the
table.

I hope this makes what I need to do a bit clearer.
--
At the end of the day a new one begins

Puddly,

I see. Well, if you don't mind me saying so, it looks like you have
made a fundamental misunderstanding of how databases work.

You have given a description of the business processes, which is nice,
but I would really need you to present it in data structure language
(i.e. tables and fields and relationships) before I can make real
specific comments.

But do I understand you correctly that the marketing items form is a
standalone form, i.e. it is not a subform on one of the other forms you
mentioned? So you are recording details of the sending out of marketing
materials. So presumably there is a table where this information is
recorded? Is the distribution event to more than one business, and/or
more than one contact? Or is your model that sending the marketing
materials to one contact person within one business is regarded as a
distinct distribution event? Either way, the *only* information you
need in this table to identify who the material is sent out to, is the
ID of the contact.

And you would not use a SetValue macro to transmit data from one form to
another in this way. You will end up in all sorts of tangles doing
that. The best way probably is to completely ignore the other
form/subform assembly, and provide data entry via comboboxes on your
"SendOut" form to allow for the identification of the recipient, and
recording of their ID, and the display of the related data such as
business and name from the related tables.

If for some reason it is in fact senseible to show data from other forms
on the SendOut form, then you would use unbound controls, with their
Control Source properties set to reference the controls on the other forms.

--
Steve Schapel, Microsoft Access MVP

Steve,

Ok some background. In this business each client may have a number of
contacts, eg the local council has a number of departments and in some
departments it may have 2 or 3 contacts. So the form I am working on now is a
data entry form for marketing items that will be sent out. So the idea is
that the data entry person will find the correct business in the
"frmBusinessMarketing" form and then the correct contact in the
"subfrmBusinessContactsMarketingDate" subform. Then to ensure that the
marketing materials goes to the correct contact (noting that marketing
materials are sent a number of times during the year) I want the Business ID
and the contact name to show on the marketing form before the rest of the
data entry can be done.

At this point I have used the setValue for the Business ID with no problems.
Yeat using SetValue and the expression
[Forms]![subfrmBusinessContactsMarketingDate]![First Name] I get the error
message I mentioned before. I have rewritten the macro a number of times,
since I have found that macros can be tempremental; and redone the form. I am
stumped.

 

[tina]

well, i'll leave it to Steve to make heads or tails of your table structure,
i didn't really follow along there. but to strictly answer your question
about the error: suggest you check the name of the subform object *within
the parent form*. i suspect that "subformBusinessContactsMarketingDate" is
the name of the form as you see it in the database window - but within the
main form, it may have a different name, depending on how you added the
subform into the main form. to find the name of the subform (actually the
subform control, which "houses" the subform object): open the main form in
Design view. within the main form, click *once* on the subform to select it;
make sure you do NOT click twice, because then you'll be "inside" the
subform, which you don't want. open the Properties box, click on the Other
tab, and look at the Name property. that's the name of the subform you'll
need to use when referring it in code and/or expressions. in a macro, the
syntax would probably be

Forms!MainFormName!SubformName.Form!ControlInTheSubform

hth

 

[Steve Schapel]

Puddley,

Thanks for the further explanation.

So can you give a list of the fields in the Marketing table?

 

[Puddlypooh]

Thanks both Steve and Tina.

Tina's suggestion works and my boss is very happy now.

 

[Steve Schapel]

Puddly,

Yes, it will work. But I beg you to re-consider. You will notice that
I steadfastly avoided telling you how to do a bad thing. And I will say
again, even though your boss is happy, that this sort of happiness is
destined to be short-lived.