Auo change record contacts

[Mike]

Hello everyone

I monitor this news group every day and picked up some great tips well
done guys thanks a billion.

I have a vehicle servicing database form AC2000 with 60 records
containing details of when the vehicle is due for its next service
both in miles and date.

The contact details include branchcode, location, telnumber,fax
no,plus vehicle details and due at miles and date boxes.

The user types in the registration number into the combo box and all
the details are displayed for that vehicle.

My question is I would like to create a combo for the branch code AB.
CD. EF,Etc
and have it auto asign the contact details only with that brach code.
In other words if the vehicle is transfered to a new location intead
of having to type in all the contact details again, just type in the
branch code prefix and auto asign the contact details to this vehicle
record. The methods I have tryed so far defaults to the same for every
record.

Any ideas!

Thanks Mike

 

[Michel Walsh]

Hi,


You should use three tables, instead of one. One for Contacts, with just the
data relevant to the contact (probable the name, the occupation, that's
all). One with the Branches (addresses, etc) and finally, one table
describing the "association", ContactsBranches, with two fields, ContactID
and BranchID.

Next, build a query involving the three tables, Contacts join to
ContactsBranches through the common field ContactID, similar, Branches and
ContactsBranches through BranchID.

Build the form on the query you just made. If an association ever change,
just change it in the ContactsBranches table, and all the other information
will follow (through the join) ! Just be sure you change the appropriate
table, ContactsBranches that is.

Sure, if there is a computed field, like distance between home and work, and
you don't have MapPoint (or don't have the time to explore its potential),
that "computed" field seems to belong to the table ContactsBranches, and
would have to be manually modified with each relevant modification too.



Hoping it may help,
Vanderghast, Access MVP

 

[Mike]

Hi,


You should use three tables, instead of one. One for Contacts, with just the
data relevant to the contact (probable the name, the occupation, that's
all). One with the Branches (addresses, etc) and finally, one table
describing the "association", ContactsBranches, with two fields, ContactID
and BranchID.

Next, build a query involving the three tables, Contacts join to
ContactsBranches through the common field ContactID, similar, Branches and
ContactsBranches through BranchID.

Build the form on the query you just made. If an association ever change,
just change it in the ContactsBranches table, and all the other information
will follow (through the join) ! Just be sure you change the appropriate
table, ContactsBranches that is.

Sure, if there is a computed field, like distance between home and work, and
you don't have MapPoint (or don't have the time to explore its potential),
that "computed" field seems to belong to the table ContactsBranches, and
would have to be manually modified with each relevant modification too.



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

Thanks thats great. Thought I would acknowledge now incase it takes
me some time to impliment this. Will post the results when finished.

It was a while ago when I first posted this question.

Today I noticed someone was asking the same thing. Then found it was
my question.

Thanks again for your help.

Mike.