Using Form to Track/Log Inspection Finds in Quality

[KComfort16]

Background: I have created 3 Forms for our QC Department to complete every Hour for Visual and Measurement Inspections on parts running in the presses. Form 1: Part Selection - Operator will being using a Combo Box to Select the Part and Click a Button to go to Step 2 (Form 2) Form 2: Visual Results - I Pass down the PartID from Form 1 and The Form Pulls the Inspection Criteria (based on the Unique PartID) from a table Titled Inspection - Visual; On the form there is room for 10 Individual Criteria (however NOT all 10 will be used for each Item) and there are 10 UNBOUND Result Boxes for the Operator to Fill out. Upon Clicking at Button and the Form CLOSING I want to Save the results from the PartID, Additional bound boxes (or fields) and the 10 UNBOUND Boxes (or Fields) to a Table Titled Inspection Results - Visual Form 3: Measurement Results - Is Identical with information being passed down from Form 1, but this form pulls its Criteria for a Table called Inspection - Measurements; each Measurement has a corresponding unbound field for the results to be entered.

What I have Tried - Trying to get the Visual Results to work first then I will apply that to the Measurement Results:

1) Linking both Tables to the Form - The Issue I had was - I was not able to get Form 2 (Visual Results) to Pull in the Inspection Criteria and I felt that was because I had 2 Tables Linked to the form - once I removed the Inspection Results - Visual Table from the Link form everything worked as I hoped.

2) I Tried Use the Following Code to have the Values Transferred to the Results Table:

Dim rs as Recordset
    Set rs = CurrentDb.OpenRecordset("Inspection Results - Visual")
        rs.AddNew
        rs!PartID = Me.PAID
        rs!MoldID = Me.MID
        rs!PressIS = Me.PRID
        rs!DTC = Me.DTC
        rs!Lot = Me.Lot
        rs!VR1 = Me.VRI
        rs!VR2 = Me.VR2
        rs!VR3 = Me.VR3
        rs!VR4 = Me.VR4
        rs!VR5 = Me.VR5
        rs!VR6 = Me.VR6
        rs!VR7 = Me.VR7
        rs!VR8 = Me.VR8
        rs!VR9 = Me.VR9
        rs!VR10 = Me.VR10
        rs.Update
        rs.Close

And I have Tried:

Dim StrSQL As String
StrSQL = "INSERT INTO [Inspection Results - Visual] ([PartID],[MoldID],[PressID],[DTC],[Lot],[VR1],[VR2],[VR3],[VR4],[VR5],[VR6],[VR7],[VR8],[VR9],[VR10])" & VALUES(" & Me!PAID & ", "Me!MID &", "Me!PRID &", "Me.DTC &", "Me.Lot &", "Me.VR1 &", "Me.VR2 &", "Me.VR3 &", "Me.VR4 &", "Me.VR5 &", "Me.VR6 &", "Me.VR7 &", "Me.VR8 &", "Me.VR9 &", "Me.VR10 &") DoCmd.RunSQL StrSQL

 

[plog]

This issue needs to be put aside for a bit and you need to focus on the bigger problem--a poor table structure. You need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), thats the process of properly setting up tables and fields. You have made on glaring error and I am pretty sure another big one as well.

Numerated fields (e.g. VR1, VR2, VR3. etc.): Data should be stored vertically (with more rows) and not horizontally (with more columns). This means all that VR data needs to go come out of [Inspection Results - Visual] and into a new table, with 1 value per row (for a total of 10 rows in that new tale.

Circular relationships: It's really odd that you have so many _ID fields (PartID, MoldID, PressID) in one table. I believe you are not relating your tables properly either.

I suggest you read up on normalization, fix the errors I've addressed and any others you find after your reading up on normalization. Then you should complete your Relationship Tool in Access, expand it so we can see all the fields of every table and then post a screenshot here. That way we can help you verify it.

 

[Ranman256]

and there's no need to code data entry. Forms can do this with zero code.

 

[Uncle Gizmo]

there are 10 UNBOUND Result Boxes

The fact that you put "unbound" in capital letters indicates to me that you've asked this question somewhere else and they keep telling you that you shouldn't use unbound controls. What makes you think you're going to get the different answer on a different forum?

 

[KComfort16]

The fact that you put "unbound" in capital letters indicates to me that you've asked this question somewhere else and they keep telling you that you shouldn't use unbound controls. What makes you think you're going to get the different answer on a different forum?

I have not gotten any responses to my question in any other Form - that is just how I typed it.
\

This issue needs to be put aside for a bit and you need to focus on the bigger problem--a poor table structure. You need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), thats the process of properly setting up tables and fields. You have made on glaring error and I am pretty sure another big one as well.

Numerated fields (e.g. VR1, VR2, VR3. etc.): Data should be stored vertically (with more rows) and not horizontally (with more columns). This means all that VR data needs to go come out of [Inspection Results - Visual] and into a new table, with 1 value per row (for a total of 10 rows in that new tale.

Circular relationships: It's really odd that you have so many _ID fields (PartID, MoldID, PressID) in one table. I believe you are not relating your tables properly either.

I suggest you read up on normalization, fix the errors I've addressed and any others you find after your reading up on normalization. Then you should complete your Relationship Tool in Access, expand it so we can see all the fields of every table and then post a screenshot here. That way we can help you verify it.

But I need one Row of Results per New Inspection - Putting the Result Vertically will make the output too messy.

 

[plog]

Then you don't need a database and we can't really help you.

Contrary to a lot of Excel users beliefs, Access isn't 'Excel With Forms'; it's a database and needs to be worked with as such. If you'd like to learn those ways and implement them we are happy to help. But if you want to hack together a spreadsheet system using Access you aren't going to get much direction here.

 

[KComfort16]

I Don't want to "Hack Together as Spreadsheet System"

What I am trying to do Can't be done in Excel as I am trying to repair a broken Access Database that we are currently work off of an it repeatedly crashes and causes loss of data - But Clearly This isn't the right Forum for me to be requesting help in seeing as ALL you guys have done is Belittled me and point me to Wikipedia (which I have READ multiple times as this seems to be the go to response to ALL questions I have read on the forum).

All My Tables in Access are created and setup with no duplicate information and Everything is linked by unique values, and laid out as instructed in the Normalization Article that you so nicely pointed out on Wikipedia - HOWEVER the table that I am looking to have created from the Forms is to Capture Unique testing results for the Quality Department, from the Hourly Checks that need to be completed. Each Entry will be assigned a unique Record ID and the Reason I why I would like the results in Horizontally is because I would like to have 1 Row for each hour and Item vs 10 rows per hour and item.

But again Clearly this Forum is ONLY for Experts who like to Belittle and Talk Down to Newbie Access Users.

 

[Uncle Gizmo]

But again Clearly this Forum is ONLY for Experts who like to Belittle and Talk Down to Newbie Access Users.

I can understand that my response may well have put your back up, however the nature of your question indicated that you were a spreadsheet thinking person. We get at least one person a week who want to reproduce their spreadsheet in MS Access. We say, hey, this isn't the way to do it, and some don't listen, and your bold type gave the impression that you were one of those people. However your latest response does contain some useful information, with a bit more information from you, and this will require you to answer some questions, I'm sure something can be done to help you... One of the best ways is if you create a simple database modelling the problem and the results you want. Also provide a selection of data in the format at that you don't like, and examples of the format you are aiming to achieve. The data is very important because people fall into the trap of thinking about the problem from the aspect of a form, when very often manipulating the data gives better results.