Create Multiple Records on One Form

[kyoch]

Hello All,

I've developing a QA/QC database for testing chemical products and I'm stuck on the best way to continue with adding multiple records by using one form.

I have two tables:
tblProductSpecs with two primary keys, "ProductName" and "TestName"
tblResults with three primary keys, "ProductName", "TestName", and "LotNumber" and a number field named "Value"

A product can have multiple tests associated with it, e.g:
ProductName - TestName
XXX - Density, pH
ZZZ - Density
YYY - % soluble, cloud point , freeze point
This is my tblProductSpecs table

I want to store the "Value" of each "TestName" of the "LotNumber" of that "ProductName" in tblResults by a form. (All TestName values are number values).

Here's what I'm trying to accomplish...

I want a form where I can select "ProductName" and have the "TestName"s displayed for that "ProductName". Then I want to store the value of that "TestName" for that "ProductName" and "LotNumber" in tblResults.

The problem is that each test per product per lot number is a record and I can't figure out a way to create multiple records from one form.

If there is some better logic to storing these values then I am open to suggestions.

Please help!

 

[MarkK]

Check out subforms. You can essentially nest forms to model your parent/child relationships between tables. Make sure the parent and child forms both expose the field on which they are linked, and drag the child onto the parent in design view, drop it, and Access links them.

 

[kyoch]

I've tried the subform and couldn't get it to work. Here's what I did...

Parent form has "ProductName" and "Lot Number" from tblResults.
Child form displays "TestName" from a qry based on tblProductSpecs
Then I tried a subform in the child form with "Value" from tblResults.

My problem is that the parent form required 3 primary keys, (ProductName, LotNumber, and TestName) but I have the TestName and value fields in the subform. I get an error saying "Index or primary key cannot contain a Null value" after I enter ProductName and LotNumber and try to input the "Value" in the subform.

 

[Mihail]

I want a form where I can select "ProductName" and have the "TestName"s displayed for that "ProductName". Then I want to store the value of that "TestName" for that "ProductName" and "LotNumber" in tblResults.

Unless I misunderstand, you try to save something that you already have (is calculated) in a new table.
If so, DO NOT DO THIS.

Database Normalization and Table Structures - Microsoft Access / VBA

 

[pr2-eugin]

How is that setup even possible? Compound PK using Child and Parent fields?

 

[kyoch]

You're right Mihail, I don't think my logic on how to store this data is correct so I will gladly accept suggestions.

I have multiple products and each product has tests associated with it. A value needs to be stored for that test, for that product, for that lot number. What is the best way to store that data?

 

[Mihail]

If you can "calculate", is no need to store. You will "calculate" this every time you need to see this by using a query or other approach.

What about to show us the entire structure of your DB ?

 

[kyoch]

I've attached the tables with some sample data.

 

[MarkK]

I would expect to see tables that look more like those in the DB I posted.

A table represents a real-world type of thing, and related tables "should" model the actual real-world relationships that exist between the different objects that your system is concerned with.

A common pattern is that there is an abstract object and a corresponding concrete object, like a College Course as it might exist in a catalog, and the class that starts at 7pm on Wednesday in room 9. They each need a table to be accurately represented, because they have very different properties.

I think this is the pattern with your Product and your Lot. Lot is a real-world quantity of an abstract Product. Similarly, there is an abstract Density test. Then there is the Density Test that is performed on Lot 217 on Thursday by Phil.

 

[kyoch]

Thank you for taking a look at it for me MarkK.

I think I've been working on this too long but I am still stuck on how to create the form to enter a test value for a product with a lot number.

 

[kyoch]

I'm still working on this guys...any help would be appreciated.

Also, the tblProductSpecs is linked from another table in a separate database so I can't seperate those fields into separate tables.

Please help!

 

[JHB]

Could you create a picture/Print Screen how you input form should look like, just now we are only guessing, you can draw it in Excel/Powerpoint or another program?

 

[kyoch]

Here's a screenshot of what I'm looking for.

I'd like to select the product name, then have the corresponding test names display in the subform. Next to the test name, I'd like to enter the value of the test. Then save the record as product name, lot number, test name, and test result.

 

[MarkK]

It seems impossible to advise you on this without knowing the structure of your tables. Where should we advise you to draw test names from, for instance, without you telling us where you stored them?

 

[JHB]

Take a look at the attached database, is it so you expected it to work?
Open the "Mainform" and choose a product.

 

[kyoch]

That is exactly what I was looking for JHB. Thank you for taking the time to look at this for me. I would not have been able to figure out the SQL language or thought of the temporary table design to to make this possible.

After making a record, I couldn't create a new record so I added a line after your Save results click() code where the form closes and reopens again.

Thank you again JHB! Much appreciated.

 

[JHB]

..
After making a record, I couldn't create a new record so I added a line after your Save results click() code where the form closes and reopens again.

Thank you again JHB! Much appreciated.

You're welcome.
I do not have any problem creating different test result for different products/ LotNo without closing/opening the form.

 

[kyoch]

That's interesting. I just downloaded your database again and it's still not letting me add a new product after creating one. What could be the issue there? I'm running 2010, would that have anything to do with it?

 

[JHB]

I don't think so, because I'm also using MS-Access 2010!
Maybe we are talking about different things.
How do you add a new product - try to tell step by step what you are doing, also the different inputs you made.

 

[kyoch]

Ok, I open the MainForm.
Select "Product A" from the Product drop down box
Input a LotNo (1111)
Input "1" in the Density TestResult
Input "5" in the pH Test Result
Select the Save Result button

The record is successfully created but my record source navigator at the bottom will not let me go to add a new record. I have to close the form and open it back up if I want to add a new product, lot number, and test results.