Adding records in DETAIL table automatically from master

[MikeF]

Have one master called tblCompanies.
This contains name/address/phone and other general info [only] relevant to
its records.

In Companies is a field called CompanyTypes, which is a lookup to
tblCompanyTypes.
As an example, one of those CompanyTypes is Restaurant [there are several
others, but will use just "restaurant" for this example].

Accordingly, there is a another table called tblRestaurantDetails.
It contains an indexed CompanyID field from tblCompanies.
Also in this table are detail fields such as type [ie Japanese/Mexican/etc],
tax rate, website, head chef, a memo field for favorite dishes, and so on.

Here’s the question:
When I add a record to tblCompanies, and designate it as Restaurant in the
CompanyTypes field, would like a corresponding record to be automatically
added to tblRestaurantDetails, where I can enter the detail info.

Can this be easily accomplished?

Any assistance will be sincerely appreciated.
Thanx,
- Mike

 

[PJFry]

Mike,

It is unclear why you need to insert information into tblRestaurantDetails.
For example, in tblRestaurantDetails, you have the head chef, tax rate,
website, etc. Will the resturant in question have more than one of each of
these items? Or are you dealing with a chain of resturants, say Chilis,
where there would be one chilis but many locations, hence many tax rates,
chefs, etc.

In the case of the latter, you could simply use a form for the tblCompanies
and a subform for the tblRestaurantDetails, and set your master-child
relationship on CompanyID. Make sure your subform is a continuous.

PJ

 

[MikeF]

tblCompanies contains many more CompanyTypes than just "restaurant".
So only some of its records should feed tblRestaurantDetails.
Are you saying set up a form, that "queries" only the restaurant CompanyType
from tblCompanies, and only the relevant records from tblRestaurantDetails
will automatically be in the subform? ... Including any record I "just
added"??

Thanx,
- Mike

Mike,

It is unclear why you need to insert information into tblRestaurantDetails.
For example, in tblRestaurantDetails, you have the head chef, tax rate,
website, etc. Will the resturant in question have more than one of each of
these items? Or are you dealing with a chain of resturants, say Chilis,
where there would be one chilis but many locations, hence many tax rates,
chefs, etc.

In the case of the latter, you could simply use a form for the tblCompanies
and a subform for the tblRestaurantDetails, and set your master-child
relationship on CompanyID. Make sure your subform is a continuous.

PJ

Have one master called tblCompanies.
This contains name/address/phone and other general info [only] relevant to
its records.

In Companies is a field called CompanyTypes, which is a lookup to
tblCompanyTypes.
As an example, one of those CompanyTypes is Restaurant [there are several
others, but will use just "restaurant" for this example].

Accordingly, there is a another table called tblRestaurantDetails.
It contains an indexed CompanyID field from tblCompanies.
Also in this table are detail fields such as type [ie Japanese/Mexican/etc],
tax rate, website, head chef, a memo field for favorite dishes, and so on.

Here’s the question:
When I add a record to tblCompanies, and designate it as Restaurant in the
CompanyTypes field, would like a corresponding record to be automatically
added to tblRestaurantDetails, where I can enter the detail info.

Can this be easily accomplished?

Any assistance will be sincerely appreciated.
Thanx,
- Mike

 

[PJFry]

Couple of questions:
When you say there are many company types, does that mean that there are
also many company type tables? For example, you have a company type of
restaurant and tblRestaurantDetails. Next you might have a company type
grocery store and tblGroceryStoreDetails.

If that is the case, is there something unqiue about each of the company
type tables, or do they all contain the same information, just in seperate
tables?

As for the form-subform approach, the idea is to your tblCompanies feed the
parent information to the form and the subfrom feeds the child information.
Chilis is the parent, the multiple locations are the children. So, in a way,
yes, you are 'querying' the relevant records from the restaurant table.

Have you ever created a form-subform before?

PJ

tblCompanies contains many more CompanyTypes than just "restaurant".
So only some of its records should feed tblRestaurantDetails.
Are you saying set up a form, that "queries" only the restaurant CompanyType
from tblCompanies, and only the relevant records from tblRestaurantDetails
will automatically be in the subform? ... Including any record I "just
added"??

Thanx,
- Mike

Mike,

It is unclear why you need to insert information into tblRestaurantDetails.
For example, in tblRestaurantDetails, you have the head chef, tax rate,
website, etc. Will the resturant in question have more than one of each of
these items? Or are you dealing with a chain of resturants, say Chilis,
where there would be one chilis but many locations, hence many tax rates,
chefs, etc.

In the case of the latter, you could simply use a form for the tblCompanies
and a subform for the tblRestaurantDetails, and set your master-child
relationship on CompanyID. Make sure your subform is a continuous.

PJ

Have one master called tblCompanies.
This contains name/address/phone and other general info [only] relevant to
its records.

In Companies is a field called CompanyTypes, which is a lookup to
tblCompanyTypes.
As an example, one of those CompanyTypes is Restaurant [there are several
others, but will use just "restaurant" for this example].

Accordingly, there is a another table called tblRestaurantDetails.
It contains an indexed CompanyID field from tblCompanies.
Also in this table are detail fields such as type [ie Japanese/Mexican/etc],
tax rate, website, head chef, a memo field for favorite dishes, and so on.

Here’s the question:
When I add a record to tblCompanies, and designate it as Restaurant in the
CompanyTypes field, would like a corresponding record to be automatically
added to tblRestaurantDetails, where I can enter the detail info.

Can this be easily accomplished?

Any assistance will be sincerely appreciated.
Thanx,
- Mike