Auto Fill

[Elaine]

I was wondering if there was a way to make my database take one set of
information in a table and automatically fill it in another table. For
instance if I have a person, and in one form I have his address is there a
way I can get Access to transfer that address over into another table by
using his name (such as a shipping form)? I thought I could do it by
relationships but am finding problems with that. Thank you all for any help
you can give.

 

[Jeff Boyce]

Elaine

You are describing how you are attempting to solve a problem ... but not the
problem itself.

What will having a second table with duplicate information from the first
allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Elaine]

Jeff

I want to be able to create a list of contacts and then have Access refer
back to that list when I am trying to create a form- such as a fax form- in
Access. This way I do not have to continually look up the contact info of
the person. Does this clarify my problem? Thanks again.

 

[Jeff Boyce]

Elaine

Sorry, I'm still not getting why you'd need a second table to do this.

Are you attempting to work directly in the tables instead of via forms?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Elaine]

Jeff,

I'm sorry I'm not explaining this properly. I am working with tables,
forms, and reports. I am trying to create a database that I can use for a
small company. Because of liability I like to track every item that leaves
the company, for instance faxes. I have created a fax table, form, and
report so that I can know the date, person, and material faxed. I have also
designed it so that the report can be printed out and sent as the fax cover
sheet. To make things easier for others and myself I am wondering if there
is a way to have the fax form automatically fill in items when given a key
word. For instance if I put in the company name it would then fill in their
address and fax number.

This way my information would be accurate and I wouldn't have to do a lot of
duel entering. I hope this explains things better. Thank you so much for
trying to help me.

 

[John W. Vinson]

Jeff,

I'm sorry I'm not explaining this properly. I am working with tables,
forms, and reports. I am trying to create a database that I can use for a
small company. Because of liability I like to track every item that leaves
the company, for instance faxes. I have created a fax table, form, and
report so that I can know the date, person, and material faxed. I have also
designed it so that the report can be printed out and sent as the fax cover
sheet. To make things easier for others and myself I am wondering if there
is a way to have the fax form automatically fill in items when given a key
word. For instance if I put in the company name it would then fill in their
address and fax number.

This way my information would be accurate and I wouldn't have to do a lot of
duel entering. I hope this explains things better. Thank you so much for
trying to help me.

I think you're conflating two quite different functions of Access. It is NOT
necessary to have a separate table of "fax cover stuff" in order to generate
the fax cover page! In fact it would almost certainly be a bad idea to have
such a table, as it would contain redundant, duplicate data.

Instead, consider the "fax cover sheet" as a Report, which would be based on a
Query joining whichever tables contain the information needed on the cover
sheet. The name, address, etc. would be stored only once, in the appropriate
table; the Query would pull the information from various locations as needed.

 

[Elaine]

Jeff,

Thank you for your suggestion but I need to be able to track every fax that
I send out. Therefore I need a table to be able to keep a record off all
faxes that I have sent and created, please know that my fax cover sheet
changes every time. So even if I do the query, as per your suggestion, I
would still have to go and grab that information anyways and put it into the
table.

Can you or anyone else answer my original question? The question was if
there was a way to take information from one table and have it automatically
fill another table from a key word. An example of this would be typing a
company name and have Access pull the address and other designated fields
from the original table.

 

[Jeff Boyce]

Elaine

Perhaps one reason why the "original question" you posed hasn't been
answered is because we don't understand the situation.

Another possibility is that the specific way you've decided to do something
is more likely to cause you long-term problems that some other available
options.

If you'll continue helping the folks here understand the specifics of your
situation, they'll be better able to offer specific suggestions.

For instance, "...my fax cover sheet changes every time" could mean that
there is no pattern to your fax cover sheets, or could mean that you use a
basic template and the DATA in each fax cover sheet changes (i.e., to whom,
phone number, subject, ...).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Elaine]

Ok I will try again to explain this as simply as possible so we have no more
confusion.

I am working on a database that needs to keep track of everything we do
because of liability. Therefore everything from phone calls to faxes to
documents have to be logged into our database. To help make this more
efficient for the people in my office I want to make it so that if they type
in a key word (such as the company name) other fields on the form will
automatically fill in (such as the address, fax, etc).
I NEED to have a contact list in my database because I need to be able to
store all of their contact information in the same place. I also NEED a
table to keep track of all of the faxes we create. From the fax table I
already have a report that will be generated so that they can print the
report out and use it as a cover sheet. Therefore please do not tell me to
use a query or anything else.

I NEED both of these tables, as they are with all of their information so
that I can see exactly where the item was faxed and to avoid people
accidentally creating a wrong query and me never knowing it until it is too
late OR having it be too complex and time consuming for them- it is much
easier for them to be able to fill out a form and create the report.

Can Microsoft Access 2007 do this?

 

[gls858]

Ok I will try again to explain this as simply as possible so we have no more
confusion.

I am working on a database that needs to keep track of everything we do
because of liability. Therefore everything from phone calls to faxes to
documents have to be logged into our database. To help make this more
efficient for the people in my office I want to make it so that if they type
in a key word (such as the company name) other fields on the form will
automatically fill in (such as the address, fax, etc).
I NEED to have a contact list in my database because I need to be able to
store all of their contact information in the same place. I also NEED a
table to keep track of all of the faxes we create. From the fax table I
already have a report that will be generated so that they can print the
report out and use it as a cover sheet. Therefore please do not tell me to
use a query or anything else.

I NEED both of these tables, as they are with all of their information so
that I can see exactly where the item was faxed and to avoid people
accidentally creating a wrong query and me never knowing it until it is too
late OR having it be too complex and time consuming for them- it is much
easier for them to be able to fill out a form and create the report.

Can Microsoft Access 2007 do this?

Elaine

Perhaps one reason why the "original question" you posed hasn't been
answered is because we don't understand the situation.

Another possibility is that the specific way you've decided to do something
is more likely to cause you long-term problems that some other available
options.

If you'll continue helping the folks here understand the specifics of your
situation, they'll be better able to offer specific suggestions.

For instance, "...my fax cover sheet changes every time" could mean that
there is no pattern to your fax cover sheets, or could mean that you use a
basic template and the DATA in each fax cover sheet changes (i.e., to whom,
phone number, subject, ...).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

While Access may be able to do this, you may actually end up spending
more to develop it than it would cost to buy something off the shelf.
A packaged product would probably end up being a much more flexible
application.

The function you are talking about are usually included in CRM packages
such as Goldmine (just to name one).

That said it's no small undertaking to get a CRM package up and running.
Just do a search for CRM software and check a few out.

gls858

 

[John W. Vinson]

Jeff,

Thank you for your suggestion but I need to be able to track every fax that
I send out.

Fine, I understand that.

Therefore I need a table to be able to keep a record off all
faxes that I have sent and created, please know that my fax cover sheet
changes every time.

So you need to store not only the ID of the company, but *EVERYTHING*,
redundantly? Store the company's name, fax number, phone number, etc. etc.
repeatedly, every time you send a fax? OK... but it sounds like you're wasting
a lot of space and introducing a LOT of redundancy.

So even if I do the query, as per your suggestion, I
would still have to go and grab that information anyways and put it into the
table.

Can you or anyone else answer my original question? The question was if
there was a way to take information from one table and have it automatically
fill another table from a key word. An example of this would be typing a
company name and have Access pull the address and other designated fields
from the original table.

Put VBA code in the AfterUpdate event of the combo box or other control which
lets you select the company. Include all the fields that you want to store
redundantly in the combo's rowsource query and have other textboxes on the
form bound to the fax cover page table. In the AfterUpdate event put code like

Private Sub cboCompany_AfterUpdate()
Me!txtCompanyFax = Me!cboCompany.Column(1)
Me!txtCompanyName = Me!cboCompany.Column(2)
Me!txtCompanyPhone = Me!cboCompany.Column(3)

<etc etc>

End Sub

Note that the Column property is zero based so this would store the second
through fourth fields in the rowsource query.