Help with form please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database (ms access 97) to enable employees to enter
inspection data for any given part. I have them type in the part number and
the revision of the part and the description, inspection code and supplier
auto fill. The part I need help with is this...Each part has certain criteria
that needs to be inspected. I would like to have that criteria auto enter on
the subform. The criteria could change from one revision to the other so I
would control that by having them enter the part number and revision. What I
can't figure out is how to show the criteria to measure and then have a field
for them to put their findings. Any and all help would be greatly appreciated.
 
Sheila,

You will need some master table (in a one-to-many relationship with Parts)
that defines the tests required for a given Part Number/Revision combination.
Something like:

TestMaster
--------------------------------
ID AutoNumber (PK)
PartNumber
RevisionNumber
TestID (Foreign Key to Tests)

You will also need a detail table (the many side of a 1-to-many with
Inspections) that list the tests and their results associated with that
inspection.

InspectionTestResults
-------------------------
TestResultID AutoNumber (PK)
InspectionID Integer (Foreign Key to Inspections)
TestID Integer (Foreign Key to TestMaster)
Value Single

Your code can populate the detail table with an Insert query after the user
has entered the PartNumber and RevisionNumber, either in the AfterUpdate
event of the RevisionNumber, or via a command button (after appropriate
error-checking, of course).

Dim strSQL As String

' Turn off warnings so user is not prompted to confirm query
DoCmd.SetWarnings False

strSQL = "INSERT INTO InspectionTestResults ( InspectionID, TestID )" & _
"SELECT Inspections.InspectionID, TestMaster.TestID " & _
"FROM Inspections INNER JOIN TestMaster ON
(Inspections.RevisionNumber = TestMaster.RevisionNumber) " & _
"AND (Inspections.PartNumber = TestMaster.PartNumber) " & _
"WHERE
(((Inspections.InspectionID)=[Forms]![Inspections]![InspectionID]));"

DoCmd.RunSQL strSQL
Me![qryTests].Requery
DoCmd.SetWarnings True

You will need to do appropriate error-checking to ensure a subsequent button
push does not add additional records to the detail table.

When complete, the subform should display the tests needed to be filled in.
If you base the subform on a query that includes the test description from
the Tests table, you can display the text rather than just its TestID. For
example,

Test Value
------------------ ------------
Viscosity
Specific Gravity

Hope that helps.

Sprinks
 
Thanks Sprinks. I appreciate the help.

Sprinks said:
Sheila,

You will need some master table (in a one-to-many relationship with Parts)
that defines the tests required for a given Part Number/Revision combination.
Something like:

TestMaster
--------------------------------
ID AutoNumber (PK)
PartNumber
RevisionNumber
TestID (Foreign Key to Tests)

You will also need a detail table (the many side of a 1-to-many with
Inspections) that list the tests and their results associated with that
inspection.

InspectionTestResults
-------------------------
TestResultID AutoNumber (PK)
InspectionID Integer (Foreign Key to Inspections)
TestID Integer (Foreign Key to TestMaster)
Value Single

Your code can populate the detail table with an Insert query after the user
has entered the PartNumber and RevisionNumber, either in the AfterUpdate
event of the RevisionNumber, or via a command button (after appropriate
error-checking, of course).

Dim strSQL As String

' Turn off warnings so user is not prompted to confirm query
DoCmd.SetWarnings False

strSQL = "INSERT INTO InspectionTestResults ( InspectionID, TestID )" & _
"SELECT Inspections.InspectionID, TestMaster.TestID " & _
"FROM Inspections INNER JOIN TestMaster ON
(Inspections.RevisionNumber = TestMaster.RevisionNumber) " & _
"AND (Inspections.PartNumber = TestMaster.PartNumber) " & _
"WHERE
(((Inspections.InspectionID)=[Forms]![Inspections]![InspectionID]));"

DoCmd.RunSQL strSQL
Me![qryTests].Requery
DoCmd.SetWarnings True

You will need to do appropriate error-checking to ensure a subsequent button
push does not add additional records to the detail table.

When complete, the subform should display the tests needed to be filled in.
If you base the subform on a query that includes the test description from
the Tests table, you can display the text rather than just its TestID. For
example,

Test Value
------------------ ------------
Viscosity
Specific Gravity

Hope that helps.

Sprinks

Sheila said:
I am creating a database (ms access 97) to enable employees to enter
inspection data for any given part. I have them type in the part number and
the revision of the part and the description, inspection code and supplier
auto fill. The part I need help with is this...Each part has certain criteria
that needs to be inspected. I would like to have that criteria auto enter on
the subform. The criteria could change from one revision to the other so I
would control that by having them enter the part number and revision. What I
can't figure out is how to show the criteria to measure and then have a field
for them to put their findings. Any and all help would be greatly appreciated.
Click to expand...
Click to expand...
 
Back
Top