Lookup fields to an extreme

S

Stacey

At this website you can find a screenshot of my form so
you can get an idea of what I'm talking about.
http://slpconsultinginc.com/misc/screenshot.jpg I can
also send you a copy of the database if that will help.
Just let me know.

The database was created to keep track of media contacts
in relation to certain clients. For example, Jane Smith
works for USA Today. We want to associate her as a media
contact with two of our mock clients-Apple Orchards, Inc.,
and Rain Bows.
When entering the information about Jane Smith, I want to
be able to enter all the information SPECIFICALLY relevant
to her, such as her personal address, phone, email, etc.
But I also want to list that she works for USA Today and
what her position is there. I have created a field called
Publication/Station in which to enter the company name
(i.e. USA Today).

Now here is where my question comes in. If I enter "USA
Today" into the Publication Field, I want it to bring up
all the information relevant to that organization (i.e.
address, phone numbers, general email, website, etc.) and
enter it into the Organization Information part of this
form. Now all of this information on this organization is
actually saved in a separate table that specifically holds
just the information on these organizations. It then
associates that record and address information of "USA
Today" with Jane Smith's record. So I need to figure out
how to choose a name/type in a name, and have it look it
up in the Organization Table and fill in the information
on the Media List form. Let it be noted that there may be
more than one general information field for USA Today,
since they have different satellite offices around the
US. So I would need to be able to choose which USA Today
I want out of all of those available, so I guess it might
need to involve a filter or something. So that's the
first question.

Now here is where it gets trickier. Let's say, for
instance, that half of the information listed under USA
Today is correct when we bring it up in association with
Jane Smith, but half of it is wrong. Now it's not that
the information is outdated, it is just wrong in
association with Jane Smith. I want to be able to edit
that information within the Media List form, and have it
create a new record automatically in the Organization
Table to list the new information. Along this same line,
I want to be able to enter an entirely new organization
directly into the form and have it save this new
information as a new record in the Organization Table, and
this new information would now be associated with the
contact, just as USA Today was associated with Jane
Smith. Is there a way to do all of this?

Also, if I should have to come back and change any of
Jane's Smith information, I want a date field that
automatically updates to the current date when something
is changed in a record, in order to show the last date a
record was modified, as in a "Date Modified" field. Do
you know how to create this? I'm sure it should be fairly
simple, I'm just not sure how.
 
H

HSalim

see comments inline


(i.e. USA Today).
This should be a combobox that lists all the publications/organizations
Now here is where my question comes in. If I enter "USA
Today" into the Publication Field, I want it to bring up
all the information relevant to that organization (i.e.
address, phone numbers, general email, website, etc.) and
enter it into the Organization Information part of this
form. Now all of this information on this organization is
actually saved in a separate table that specifically holds
just the information on these organizations. It then
associates that record and address information of "USA
Today" with Jane Smith's record. So I need to figure out
how to choose a name/type in a name, and have it look it
up in the Organization Table and fill in the information
on the Media List form.

There is no point in storing the information in multiple places is there?
Create an addresses table and associate the addressid with this contact
Each contact can have one or more address ID, as can a company
Let it be noted
Hear! Hear! (just kidding!)
that there may be
more than one general information field for USA Today,
since they have different satellite offices around the
US. So I would need to be able to choose which USA Today
I want out of all of those available, so I guess it might
need to involve a filter or something. So that's the
first question. use a combobox

Now here is where it gets trickier. Let's say, for
instance, that half of the information listed under USA
Today is correct when we bring it up in association with
Jane Smith, but half of it is wrong. Now it's not that
the information is outdated, it is just wrong in
association with Jane Smith. I want to be able to edit
that information within the Media List form, and have it
create a new record automatically in the Organization
Table to list the new information. Along this same line,
I want to be able to enter an entirely new organization
directly into the form and have it save this new
information as a new record in the Organization Table, and
this new information would now be associated with the
contact, just as USA Today was associated with Jane
Smith. Is there a way to do all of this?

Yes, use the not in list event of the combobox to open a new form where you
can enter all the new info

Also, if I should have to come back and change any of
Jane's Smith information, I want a date field that
automatically updates to the current date when something
is changed in a record, in order to show the last date a
record was modified, as in a "Date Modified" field. Do
you know how to create this? I'm sure it should be fairly
simple, I'm just not sure how.

Yes I know how.
Answer is kinda easy.
On the form's Dirty event (which indicates that the data has been modified)
populate the Date modified field, and the Date created if Date Created is
null
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top