Linking two fields

T

Tlkproxy

Ok, I would like to be able to link to fields together. Im not sure how to do
this but it's not as simple as just linking in the relationships. I want to
make it so that Field 1 is a drop down list and when i select one of the
items it puts that information into Field 1 but also automatically puts
related information into Field2.

I dont know if there is something in table design for doing this, or whether
it requires a query or what. I have no clue on how to find this!! Please
help!!!
 
S

Steve Schapel

Tlkproxy,

In Access, a "drop down list" is called a Combobox. The list of items
shown in the combobox is determined by the entry in its Row Source
property. The Row Source can be a query or table that contains more
than one column. Only one of these columns will be the Bound Column,
but the values from the second corresponding column can be shown by
setting the Control Course property of an unbound textbox on the form to
the equivalent of...
=[NameOfCombobox].[Column](1)

If you need more specific help with this, please post back with more
details, maybe with examples.
 
6

'69 Camaro

Hi.
I want to
make it so that Field 1 is a drop down list and when i select one of the
items it puts that information into Field 1 but also automatically puts
related information into Field2.
Click to expand...

It depends upon how you've designed your database and how you want to design
your form, but your description sounds like you need to "autofill" the form,
based upon records already stored in related tables. For the steps on how
to do this, please see the tutorial on "How to 'auto-complete' a form, with
and without code" by using an auto-query to automatically fill in
information for new records:

http://www.Access.QBuilt.com/html/forms.html#AutoCompleteForm

For another idea on how to "autofill" the form, see Tom Wickerath's easy,
step-by-step tutorial on finding a record using a combo box on this Web
page:

http://www.Access.QBuilt.com/html/find_a_record.html

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
V

Van T. Dinh

I take a different tack from other respondents: If the selection of a value
in [Field 1] *completely* determines the value of [Field 2], then you
shouldn't even have [Field 2] stored in your Table. "[Field 2]" is what we
call "Calculated Value" and calculated values should not be stored in the
Table in general.
 
T

Tlkproxy

Thanks a lot for the replies everyone. Yeah, a bit more detail would really
help a lot as im relatively new to this.

As an example. I would like it so that the 'Company Name' (field1) appears
as a drop down list (Combo Box) and the 'Address' (field2) automatically
shows up in a text box beneath.

I've been trying a few of you ideas but unfortunately am not as skilled as
you are and can't get it to work!

Thanks for your help in advance.
 
S

Steve Schapel

Tlkproxy

I assume you have a table of Companies, which includes the Company Name
and Address fields. Is this correct? You might even also have an ID
number of some sort in this table to uniquely identify each company,
just in case there are ever 2 companies with the same name, or in case
you ever need to handle 2 branches of the same company separately. Do
you? And then, on this form you are referring to, there is the
allocation of a company to some other data table which has a one-to-many
relationship with the Companies? Is this correct?
 
T

Tlkproxy

Aaahh, i do now!!! So whats the next step??

Steve Schapel said:
Tlkproxy

I assume you have a table of Companies, which includes the Company Name
and Address fields. Is this correct? You might even also have an ID
number of some sort in this table to uniquely identify each company,
just in case there are ever 2 companies with the same name, or in case
you ever need to handle 2 branches of the same company separately. Do
you? And then, on this form you are referring to, there is the
allocation of a company to some other data table which has a one-to-many
relationship with the Companies? Is this correct?
Click to expand...
 
S

Steve Schapel

Tlkproxy,

Ok, let's suppose you have a Companies table with these fields...
CompanyID
CompanyName
Address

All you need in the second table is a CompanyID field to correspond with
the one in your Companies table. In the second table, if you know the
CompanyID, you automatrically know the CompanyName and Address via
reference to the Companies table... which is the whole point of
relational database programme like Access.

So, on the form, put a Combobox, look at the Properties of the combobox,
and set them as follows (for example)...
Name: CompanyID
Control Source: CompanyID
Row Source: Companies
Bound Column: 1
Column Count: 3
Column Widths: 0;x;0
(where x is whatever width needed to display the CompanyName)

Ok, now, in keeping with my earlier suggestion, put an unbound textbox
on the form, for the display of the Address. In the Control Source
property of this textbox, enter...
=[CompanyID].[Column](2)

Now open the form in Form view. You should be able to open the combobox
and see a drop-down list of the names of all the companies in your
Companies table. And when you select one, you should see that company's
address automatically shown in the Address textbox.
 
T

Tlkproxy

OH MY GOSH!!!!!!!! That works PERFECTLY!!!!!! Thank you thank you thank you
thank you!!!

You are a STAR!!! my deepest gratitude.....that was giving me a headache!!!!

Again, Many thanks!

Best regards

Tlkproxy!!!

Steve Schapel said:
Tlkproxy,

Ok, let's suppose you have a Companies table with these fields...
CompanyID
CompanyName
Address

All you need in the second table is a CompanyID field to correspond with
the one in your Companies table. In the second table, if you know the
CompanyID, you automatrically know the CompanyName and Address via
reference to the Companies table... which is the whole point of
relational database programme like Access.

So, on the form, put a Combobox, look at the Properties of the combobox,
and set them as follows (for example)...
Name: CompanyID
Control Source: CompanyID
Row Source: Companies
Bound Column: 1
Column Count: 3
Column Widths: 0;x;0
(where x is whatever width needed to display the CompanyName)

Ok, now, in keeping with my earlier suggestion, put an unbound textbox
on the form, for the display of the Address. In the Control Source
property of this textbox, enter...
=[CompanyID].[Column](2)

Now open the form in Form view. You should be able to open the combobox
and see a drop-down list of the names of all the companies in your
Companies table. And when you select one, you should see that company's
address automatically shown in the Address textbox.

--
Steve Schapel, Microsoft Access MVP

Aaahh, i do now!!! So whats the next step??
Click to expand...
Click to expand...
 
Top