auto fill based on another field

[Alpruett]

I'm fairly new to access. I have one table with various individuals contact
info with field such as Company, Name, Phone, Fax, Address, etc. Then I have
another table that is to record the letters and other documents that we
receive from these infidividuals. So, I want to be able to have a form in
which my colleagues can type in the company name and have the phone, fax,
address, etc. fill into those fields within the form automatically. The only
suggestions I've found involve some lengthy VBA coding that is WAY over my
head. Any simpler suggestions? Thanks for any help.

 

[Jeff Boyce]

If the data is already recorded in your [tblCompany], you can have your form
display that data after the company is selected. Note that I said
'selected'. Forcing your users to remember and correctly type the name of a
company is ... unnecessary!

Instead, use a combobox that lists the companies. Let the user pick the one
they want. Then use that company's ID to "load" the form.

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I'm fairly new to access. I have one table with various individuals contact
info with field such as Company, Name, Phone, Fax, Address, etc. Then I have
another table that is to record the letters and other documents that we
receive from these infidividuals. So, I want to be able to have a form in
which my colleagues can type in the company name and have the phone, fax,
address, etc. fill into those fields within the form automatically. The only
suggestions I've found involve some lengthy VBA coding that is WAY over my
head. Any simpler suggestions? Thanks for any help.

It is neither necessary nor appropriate to copy the phone, fax, etc. from the
company table into the documents table. ONLY the CompanyID should exist in
this second table; the other fields can be looked up as needed using a Query,
or using a combo box.

 

[Alpruett]

Jeff-
Thanks for the response. I already had the companies listed in a combobox to
avoid typos. But how do I get the rest of the info to "load" the form?
-Alison

If the data is already recorded in your [tblCompany], you can have your form
display that data after the company is selected. Note that I said
'selected'. Forcing your users to remember and correctly type the name of a
company is ... unnecessary!

Instead, use a combobox that lists the companies. Let the user pick the one
they want. Then use that company's ID to "load" the form.

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I'm fairly new to access. I have one table with various individuals
contact
info with field such as Company, Name, Phone, Fax, Address, etc. Then I
have
another table that is to record the letters and other documents that we
receive from these infidividuals. So, I want to be able to have a form in
which my colleagues can type in the company name and have the phone, fax,
address, etc. fill into those fields within the form automatically. The
only
suggestions I've found involve some lengthy VBA coding that is WAY over my
head. Any simpler suggestions? Thanks for any help.

 

[Jeff Boyce]

As John points out, if you are doing this to duplicate the data in a second
table, stop now!

If you are doing this to see/inspect the data that is stored in your Company
table, create a query based on the table. In the CompanyID selection
criterion (you DO have an ID field for a primary key, right?!), point to the
combobox on the form, with something like (untested):

Forms!YourForm!cboYourComboboxControl

For the combobox, use a query to get the CompanyID and the CompanyName
(whatever you call these). Hide the CompanyID value by setting the width to
0.

Now put a bit of code into the AfterUpdate event of that combobox -- you
need to requery the form with something like (untested):

Me.Requery

This has the net effect of opening the form "empty" (nothing selected in the
combobox, so the form has no record to display). Once a company is selected
in the combobox, the .Requery tells the form to check its (query) source
again, this time finding the record that goes with the selected company.

The form itself displays the fields in the Company table.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff-
Thanks for the response. I already had the companies listed in a combobox
to
avoid typos. But how do I get the rest of the info to "load" the form?
-Alison

If the data is already recorded in your [tblCompany], you can have your
form
display that data after the company is selected. Note that I said
'selected'. Forcing your users to remember and correctly type the name
of a
company is ... unnecessary!

Instead, use a combobox that lists the companies. Let the user pick the
one
they want. Then use that company's ID to "load" the form.

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I'm fairly new to access. I have one table with various individuals
contact
info with field such as Company, Name, Phone, Fax, Address, etc. Then
I
have
another table that is to record the letters and other documents that we
receive from these infidividuals. So, I want to be able to have a form
in
which my colleagues can type in the company name and have the phone,
fax,
address, etc. fill into those fields within the form automatically. The
only
suggestions I've found involve some lengthy VBA coding that is WAY over
my
head. Any simpler suggestions? Thanks for any help.