Auto Fill Fields when listbox option selected - access 2007

[CypherUK]

Hi Guys n Gals



I am very new to Access 2007 so i'm sorry of this is a really newbie question.

I am developing an access form linked to a access db - what i have is a list
box of contractors that i use, what i would like to do, is when i select a
particular contractor for a job, i would like the contractor details
(address, phone, mobile email etc) to be auto input in to the fields i have
created.

I have created the options on the Database (contractor Address etc) but i
would love to auto fill the form fields when i select the listbox option.

can it be done?

Kind Regards

Mark

 

[John W. Vinson]

Hi Guys n Gals



I am very new to Access 2007 so i'm sorry of this is a really newbie question.

I am developing an access form linked to a access db - what i have is a list
box of contractors that i use, what i would like to do, is when i select a
particular contractor for a job, i would like the contractor details
(address, phone, mobile email etc) to be auto input in to the fields i have
created.

No, you actually do NOT want to do that.

I have created the options on the Database (contractor Address etc) but i
would love to auto fill the form fields when i select the listbox option.

It's easy to *display* the address, etc. on the form - but you certainly
should NOT store that information redundantly in a second table. It sounds
like you're making a very common mistake, starting with your Form design.
Forms are decidedly secondary; your Tables are the foundation of your
structure, and you need to get them correct first!

You should have a table of Contractors with fields for name, address, phone,
etc.; it should have a ContractorID as its primary key. The table of Jobs
should have a ContractorID field - and *NOTHING ELSE* from the contractor
table.

You can display the contractor information on the form by including the
desired fields in the rowsource of a Combo Box control on your form, and
putting textboxes on the form with control sources like

=cboContractorID.Column(n)

where cboContractorID is the name of the combo box control and (n) is the zero
based index of the desired field - e.g. if the contractor phone is the fifth
field in the combo, you would use (4).

 

[tina]

yes it can be done, but probably you shouldn't. if you have the contractors'
addresses stored in a table already, you shouldn't be entering that data
again in another table, as it violates normalization rules. recommend you
STOP working on your db, and read up/more on relational design principles.
then re-examine your tables/relationships structures, and make any necessary
adjustments to normalize the data. then, and only then, you can proceed with
building queries, forms, and reports. for more information, see
http://home.att.net/~california.db/tips.html#aTip1

hth