Data entry One Record Multiple tables

[Zydeceltico]

Hi All -

Time for my newbie question of the day.

I have frmInspection used to do QC inspections of various products. Data from frmInspection is recorded in tblInspections.

On frmInspection are a series of combo boxes that record "Yes" or "No" responses regarding a variety of potential production issues.

On frmInspection, when the user selects yes from any given combo box a popup form (frmIssueDetails) opens where the user can add details about the production issue they are observing.

Currently I am recording those details back into tblInspections but I think this is not going to be sustainable due to bloat mostly and the fact that only a small percentage of inspections result in the need to add an issue detail. I don't want a mountain of empty data fields in tblInspections. It seems like better design to have a separate table to record issue details.

I would like to have another table (tblQCIssues) where the data from frmIssueDetails is stored.

My noob question is how do I link the original inspection recorded from frmInspection and stored in tblInspections to the details stored in tblQCIssues? I can relate the tables via PK and FK obviously but I don't really get how, in the event of a need to record IssueDetails, that Access "knows" that "OK - the user is doing an inspection and recording data into tblInspections - but - OH -OH - Wait! Now the user needs me to open frmIssueDetails and get some data AND store it in a different table AND relate this new record in tblQCIssues to a specific record in tblInspections.

(Actually - I do know how to call the popup form - it's the rest of it I don't comprehend).



Can someone please refer me to a really clear tutorial?

lol

THANKS!

Tim

 

[June7]

Use a subform. Otherwise you will need code (macro or VBA) to pass ID to the popup form. One way to do that in VBA is with OpenArgs. Can get rather complicated and tricky. Better to use subform and let Access do that heavy lifting.

 

[Uncle Gizmo]

When you say you have a series of combo boxes, then generally that means you have one field for each combobox. Assuming that's the case, and it's sort of inferred in your question, because you mention the wasted space and the like. The correct thing to do is create another table to store this information vertically instead of horizontally. And again as June alluded to the Logical thing to do is have a subform.

However the problem is when you create a new record the subform won't have any data in it, in other words there won't be anything like a list of combo boxes your users are used to. In other words you have a significant change in the user interface. I think it was Galaxiom who showed me a way of filling (and actually not actually filling the list in the subform) in other words if no records were entered the row would save empty. Trouble is I can't find the thread! And I'm not 100% sure it was "Galaxiom" ...

Anyway to make a long story even longer. I have a Blog which demonstrates how to fill a subform with a set of records, in other words you would be able to simulate what you had before with the comboboxes in a subform.

That this is a much better way of doing it than having individual combo boxes because if for some reason you have to add another field to the table behind your Form, then you'll have to physically add a new combobox(s). By putting things in a subform then adding a new "like a combobox" to the subform is just another record, you don't have to physically add a control.

My explanation of how to create a checklist should also be applicable in your case, filling the subform ready so that the user doesn't have to click on each row and add an item. It's a bit more user-friendly and will avoid the possibility of the user missing out entering something. My checklist blog is on my Nifty Access Website here:- How to add a Checklist to MS Access

 

[Uncle Gizmo]

I don't want a mountain of empty data fields in tblInspections. It seems like better design to have a separate table to record issue details.

I call this problem Excel in Access, it's a common problem and if not corrected can use can lead to some major problems in the future.

I describe it in detail and I also demonstrate how you can correct it easily. See my blog here "Excel in Access" ...

I also have a free tool which can convert the information in your combobox Fields taking them from the horizontal layout and putting them in a new table in a vertical layout.

The only caveat is you need to sign up to my newsletter to get the coupon code to download the free tool.