Subform Data filled with selection on main form (1 Viewer)

[masselin]

Hello, I am not very experienced with access so I will try to have this question make as much sense as possible. What I am building is an estimating system and what I am trying to do is to get my main form and subforms to display a quote relating to a quote number. I have seven forms the main being the "QuoteFrm" the subforms being "InfoFrm" "WeightsandMaterialFrm" "LaborFrm" "OutsideCostFrm2" "AdditionalChargesFrm" and "CommentsFrm". What I am trying to do is make it so that when the user selects a quote number on the main form "QuoteFrm" that it fill pull all the data related to that quote number into the subforms from the respective tables and or queries. I am not sure how to do this as my attempts to form a relationship have resulted in the quote number being saved into the subforms tables and the data that I entered into the subforms themselves is also in their tables but then I am not able to retrieve the data when I return to the forms. I have the forms linked by the quote number as the parent and child fields. I hope this makes some sort of sense. If not I will ty to provide some clarity if I can.

 

[jdraw]

Instead of starting with forms, it would be helpful if you gave a description of the business in simple English. Perhaps you could use a quote for something and tell us about which and how business processes handle the quote from start to finish. This will show the "work steps" in context and will help identify tables and relationships; and then the user interface(s)/forms involved.

 

[MajP]

What you describe sounds correct.
main form has a quote number.
Subforms are linked to main form by quote number
It sounds like you can enter new data into the subs and it creates the proper parent child link in the subform.

Can you explain what this means?

but then I am not able to retrieve the data when I return to the forms

A screen shot of better yet a sample db would help us see the problem.

 

[masselin]

Instead of starting with forms, it would be helpful if you gave a description of the business in simple English. Perhaps you could use a quote for something and tell us about which and how business processes handle the quote from start to finish. This will show the "work steps" in context and will help identify tables and relationships; and then the user interface(s)/forms involved.

Putting it simply my company is a foundry that makes metal parts from ceramic molds providing mostly "raw" castings, or parts that have no or very little work done, beyond their creation, to them by us. To some customers we do provide more services such as machining, heat treatment, non destructive testing, plating and sometimes passivating. The nature of those services isn't important in itself mostly just the fact that we need to cost for them when quoting a job for a customer.

The quote is generated by the estimating department and is used primarily by them and the quality department. The quality department releases the job to go on to be processed into the plant, they use the quote to build the job "traveler" that follows the job through the plant, which is created in a different system. So the quote itself is generally seen by few people at the company.

The quote is created when a customer requests one for a new part/casting, we would then have to create a new "die" number which is what we use internally to identify the part/casting and a new quote would also be created for that die number for that customer. Unless the part is one that we have done previously then we would choose to either use the existing quote used when the part was previously run or copy and create a new one that will be updated to reflect a change in costs that may have occurred. If a new die number was created that number will be attached to the customer part number which is provided by the customer on their drawing or print. Meaning that one customer could and generally does have many die and quote numbers attached to them.

The quote is then populated to include information that relates to that specific die numbers design requirement, such as the material to be used specification to which the material is built weight of the part cost of the raw material components. Each of these aspects have their own tables from which the user can select the appropriate option for that part/casting. The material price per pound is pulled from an excel workbook that is updated by our receiving/purchasing department as the cost of alloys change over time. This information is pulled from either the customer print or PO to be entered into our quote.
When the estimator finishes the quote one will go to the customer with information pertaining to the job and one is printed and kept in our file for our reference. The second, internal quote contains more details that the other does not, it includes all of the labor times and costs, weights for the castings, vendors to be used for out side services and a break down of the cost for the process by number of parts ordered.
Comments are provided to the customer so that they know clearly what processes they are or are not being charged for. Some of the parts/castings require testing and certification and this section is where we provide that information and have a selection of comments that are frequently used so that the estimators do not have enter the comment each time a new quote is generated.
There are also costs that come as a standard that are calculated outside of the database and are entered into the master data form and then go into each quote. Things like labor cost factors, material cost factors, minimum charges assessed by outside vendors and also the percentage of scrap to be calculated for the job, commission and overhead.

I don't know if I clarified anything at all here or if I made it more confusing for you. If there are any points hanging you up please let me know I will try to clear them up. I will post a sample of what I have created so far so that you can see maybe a little better what I am talking about. Thank you for your help.

 

[masselin]

What you describe sounds correct.
main form has a quote number.
Subforms are linked to main form by quote number
It sounds like you can enter new data into the subs and it creates the proper parent child link in the subform.

Can you explain what this means?

A screen shot of better yet a sample db would help us see the problem.

Yes I can explain it better, hopefully, it's just that when I return to the form and select a quote number it doesn't show any of the information that I entered when I had previously been in the form, the fields that were filled on the subforms are now empty and I selected the same quote number as before. This is despite the fact that the data shows up in each of the respective subform tables with that quote number.

I will post a sample Db soon. Thank you for your help.

 

[masselin]

Here is the sample Db as promised and if you find anything else that you see that needs fixing, I know you will, please let me know. Thank you for your help.

 

[MajP]

You need to make quote number in quotetbl a primary key. The form is set to data entry so you always see a new record.

 

[jdraw]

Further to MajP's suggestion,
-you do not have PKs on any tables
-no referential integrity
Appears to be some opportunity for improved Normalization (CostMach2,CostMach3,CostMach4..)

I have reviewed your business description and taken some key statements that may help with some restructuring, relationships and testing/vetting. I recommend you work on your tables and relationships to ensure your data structure meets your requirements before jumping too quickly into physical database. You can mock up some test data and test scenarios to vet/prove your model before building the physical database. This approach will give you a blue print for your tables and relationships. See stump the model for more info.

company is a foundry
makes metal parts from ceramic molds
providing mostly "raw" castings, or parts
may provide more services
such as machining,
heat treatment,
non destructive testing,
plating
passivating. (nature of those services isn't important
but we need to cost for them when quoting a job

quote is generated by the estimating department
is used primarily by them and the quality department.
The quality department releases the job to be processed into the plant
use the quote to build the job "traveler" that follows the job through the plant

quote is created when a customer requests one for a new part/casting
we would then have to create a new "die" number (use internally to identify the part/casting)
new quote would also be created for that die number for that customer.
If part is one that exists, then
use the existing quote used when the part was previously run, or
copy and create a new one that will be updated to reflect change(s) in costs
If a new die number was created, then attach to the customer part number which is provided by the customer on their drawing or print.
one customer could and generally does have many die and quote numbers attached to them.

quote(or traveller?) is populated to include information
specific die numbers design requirement
material to be used
specification to which the material is built
weight of the part
cost of the raw material components.
** The material price per pound is pulled from an excel workbook that is updated by our receiving/purchasing department as the cost of alloys change over time. This information is pulled from either the customer print or PO to be entered into our quote.

When estimator finishes the quote
one copy goes to the customer
with information pertaining to the job one is printed and kept in our file for our reference.
second, internal quote contains more details that the other
it includes all of the labor times and costs
weights for the castings
vendors to be used for out side services
break down of the cost for the process by number of parts ordered.

Customer is provided comments showing what processes they are or are not being charged for.
Some of the parts/castings require testing and certification and this section is where we provide that information
we have a selection of frequently used comments for estimators to apply to new quote as needed

There are standard costs calculated outside of the database and
entered into the master data form and then go into each quote.
Things like labor cost factors
material cost factors,
minimum charges assessed by outside vendors
the percentage of scrap to be calculated for the job,
commission
overhead.

Here is a png of your current tables/relationships with tables extended and an attempt to reduce overlapping lines.