Design of subform

[plantfinder]

I am going to try and simplify this. I may need changes in the underlying tables but here is what I'm trying to do:

I am building a form for my business do enter Job information for a specific customer. I have the main form as the general job information and it is tied to the customer table with the customer ID.

What I need is a subform to enter job details where I provided ifferent and multiple "regular services" for 1 job. That is not aproblem and I have made a datasheet subform to enter this information including service, price, details, etc.

The problem I'm having is trying to figure out how to design in "sub"services (if they are needed) for each "regular service" that I enter.I have have various services (sub services) which would be tied to each service that I list in the "services subform". The rest of the information associated with the sub service would need the same additional fields that I have in Services )i.e Quote, Date, Details).The only difference is that these sub services would be dependent on doing a regular service. It may not even have to have it's own table??? but it should look like it is a subset of the service above it on the form. I"m having a hard time figuring out how to achieve this design or if there is a better way to tackle this. Any help would
be appreciated.

Thanks

Mike

PS - If it matters, I don't know VBA. I'm not a programmer and just do this for my business

 

[ck1999]

This would be my approach.

I would make a table like
table1
fields service subservice

then on the main form have a button that will call a popup form. On this form have a combobox that list services. Upon clicking a service and pressing an add button all subservices (queried from above table) would be added to the subform.


ck1999

 

[plantfinder]

Thanks for your response.I may notunderstand understand it completely but I don't think that will work as 1 service might have multiple sub services associated with it, the way I'd like to design it.

 

[MajP]

You can keep it in one table. Ihe service table you can add a field "parentServiceID" which is the ID of the Service that the sub services relate to. This is called a self referencing table.

Main form: job information

subform 1: A continous subform bound to the the services table where "parentServiceID" is null. Linked to the mainform by job ID

Subform 2: A continous sub form that when you click on a service in subform 1 you see all the subservices, or add a sub service.

There is a FAQ on linking multiple contnous subforms.

 

[MajP]

See FAQ702-5860

 

[ck1999]

plantfinder,

if you table with 2 col has this data

oil change filter
oil change 5 qts oil
oil change labor
oil change gasket
Radiator Flush 1 gallon antifreeze
radiator flush labor


then when you select oil change in the popup form I mentioned earlier it would display in your subform
filter
5 qts oil
labor
qasket

The query would place all the subservice that correspond to the regular service in the subform.

I hope this explains it somewhat better

ck1999