Testname Append query that then brings you to proper form to enter in test result (1 Viewer)

I am developing a Certificate of Analysis (COA) database that will contain the test results of all incoming raw materials we use.

Each raw material always has a certain number of tests in each of their respective COAs. I have a table of all the raw materials we use and their tests.

I want the database to have an append query that adds the test names when a raw material criteria is entered (so the lab tech doesn't need to do it manually every time).

I am able to design the query correctly but want the data entry form to bring the lab tech to the newly appended test names so he/she can enter the test results. Is this possible?

Virtually anything is possible with enough code. I expect this will require code in some event running INSERT SELECT action SQL. You need a table that defines which tests associate with which material. Then code copies this pre-defined set of test IDs to the COA_Tests table then opens form filtered to this new set to allow user to input the pertinent test results. Since don't know your data structure and processes, can't advise specifics.

So what is the query you built?

Sounds something like a laboratory database I built. Laboratory tested quality of construction materials (gravel, bitumens, soil, etc).

June7 said:

Virtually anything is possible with enough code. I expect this will require code in some event running INSERT SELECT action SQL. You need a table that defines which tests associate with which material. Then code copies this pre-defined set of test IDs to the COA_Tests table then opens form filtered to this new set to allow user to input the pertinent test results. Since don't know your data structure and processes, can't advise specifics.

So what is the query you built?

Sounds something like a laboratory database I built. Laboratory tested quality of construction materials (gravel, bitumens, soil, etc).

Click to expand...

I have the table that have the tests associated with material. The SQL for my code is

INSERT INTO tblCOATestResults ( TestName )
SELECT tblCOASpecifications.TestName
FROM tblCOASpecifications, tblCOALots INNER JOIN tblCOATestResults ON tblCOALots.COALotNumber = tblCOATestResults.COALotNumber
GROUP BY tblCOASpecifications.TestName, tblCOASpecifications.RawMatStockCode
HAVING (((tblCOASpecifications.RawMatStockCode)=[Enter a Stock Code]));

[Enter a Stock Code] being the raw material the lab tech is entering the data for.

I would like a button to perform this append query for the COA's Lot Number (the primary key of the COA table) he is entering the data for and bring him to that data (in the form view) As it stands now, it adds the test names to the results table but the tech must manually go in, find the blank records, and enter in the the results.

I don't use dynamic parameters in query objects - especially popup input prompts as it's too hard to validate user input. Reference controls on form for user input.

Should save TestID instead of TestName. I would use VBA to build and execute the INSERT SELECT. Why are you GROUPING? Also not sure why you are joining tables.
The new LotNumber parent record must first be committed to table before dependent records in child table. Run code to save record before executing SQL. One way is:
DoCmd.RunCommand acCmdSaveRecord

Are you using autonumber field as PK/FK?

June7 said:

I don't use dynamic parameters in query objects - especially popup input prompts as it's too hard to validate user input. Reference controls on form for user input.

Should save TestID instead of TestName. I would use VBA to build and execute the INSERT SELECT. Why are you GROUPING? Also not sure why you are joining tables.

Code:Copy to clipboard

CurrentDb.Execute "INSERT INTO tblCOATestResults (LotNumber, TestName) " & _
"SELECT " & Me.tbxLotNum & ", tblCOASpecifications.TestID " & _
"FROM tblCOASpecifications " & _
"WHERE tblCOASpecifications.RawMatStockCode=" & Me.tbxStockCode

The new LotNumber parent record must first be committed to table before dependent records in child table. Run code to save record before executing SQL. One way is:
DoCmd.RunCommand acCmdSaveRecord

Are you using autonumber field as PK/FK?

Click to expand...

I am grouping because without "Total" the query returns 5200 repeating values rather than just the dozen or so tests specific to the product I want to append.

Looking at it further I don't need to join tables. Now I have
INSERT INTO tblCOATestResults ( TestName )
SELECT tblCOASpecifications.TestName
FROM tblCOASpecifications, tblCOATestResults
GROUP BY tblCOASpecifications.TestName, tblCOASpecifications.RawMatStockCode
HAVING (((tblCOASpecifications.RawMatStockCode)=[Enter a Stock Code]));

The specification table has an autonumber has the primary key. I don't see how the ID field in the testresults table would be relevant?

It has been awhile since i have messed around with access so I may be fundamentally misunderstanding something

Instead of saving long string of alpha characters, which is what I presume the test name is, into TestResults, save the test ID. Then when you want to retrieve test name, build query that joins tables.

I think you need LotNumber saved as foreign key into tblCOATestResults. Did you try my suggested query? See that tblCOATestResults is not in the SELECT subquery. Grouping should not be required.