Re: SetValue from a subform to a new form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Steve Schapel" wrote:

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

Puddlypooh wrote:
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.

.

Quantcast