Using Subforms

Question

Community,

Thank you all for helping me with my first project in Access. I am trying to use the subform wizard to create child records with a combo box and list of items already selected. When I insert a subform, I want to use a query but it will only allow me to use existing forms.

I tried creating a form to use, but I can't get what I want. Any suggestions?

John

Answer 1

Thanks Scott for your reply,

I first tried using the subform wizard, but was advised in previous replys to create the subform first, then drag it into the main form, so I did. Everything is working, but PartID is not being populated in the table associated with the subform. Yes, PartID is in the query of the main form.

I tried making a table relationship between the PartTracker table and the TubeFails table in the subform query, but did not work for me. The SQL for the query is:

SELECT tblPartTracker.PartID, tblFailureAnalysis.PartID, tblPartTracker.RMA, tblPartTracker.ReceiptDate, tblPartTracker.Responsible, tblPartTracker.PartNum, tblPart.PartName, tblPartTracker.PartSN, tblPartTracker.Customer, tblPartTracker.MachineSN, tblPartTracker.Model, tblPartTracker.[CASE_SO#], tblPartTracker.FB, tblPartTracker.NCP, tblPartTracker.Hours, tblPartTracker.Location, tblPartTracker.DispositionComplete, tblFailureAnalysis.FADate, tblFailureAnalysis.FailDate, tblFailureAnalysis.ReturnDate, tblFailureAnalysis.FACompleteDate, tblFailureAnalysis.ReviewedCompleteDate, tblFailureAnalysis.QAClosedDate, tblFailureAnalysis.Status, tblFailureAnalysis.[SO#], tblFailureAnalysis.InsulatorSN, tblFailureAnalysis.FieldServiceDetermination, tblFailureAnalysis.AnalysisBy, tblFailureAnalysis.AnalysisReviewedBy, tblFailureAnalysis.QAResponsible, tblFailureAnalysis.QAClosedBy, tblFailureAnalysis.LossVacuum, tblFailureAnalysis.LeakingMiniFlange, tblFailureAnalysis.InsulatorArc, tblFailureAnalysis.OpenFilament, tblFailureAnalysis.InternalArc, tblFailureAnalysis.LeakingFlange, tblFailureAnalysis.Corrosion, tblFailureAnalysis.Implosion, tblFailureAnalysis.OverHeated, tblFailureAnalysis.PinHoleInsulator, tblFailureAnalysis.PinHoleAnode, tblFailureAnalysis.ArcIonPump, tblFailureAnalysis.OtherSymtom, tblFailureAnalysis.FANotes, tblFailureAnalysis.Insulator, tblFailureAnalysis.Anode, tblFailureAnalysis.Filament, tblFailureAnalysis.IonPump, tblFailureAnalysis.BulletTip, tblFailureAnalysis.TearDrop, tblFailureAnalysis.WaterJacket, tblFailureAnalysis.BulletBase, tblFailureAnalysis.OtherComp, tblFailureAnalysis.FieldServiceTech, tblFailureAnalysis.ServiceDate, tblFailureAnalysis.RootCause, tblPartTracker.Disposition, tblFailureAnalysis.QAFinalNotes, tblFailureAnalysis.ReviewersNotes, tblFailureAnalysis.DateRcvdVegas, tblFailureAnalysis.PinchOffCompromised, tblPartTracker.TrackerNotes, tblPartTracker.DefectFrom, tblPartTracker.TrackerAttachment, tblFailureAnalysis.TubeFAAttachment, tblFailureAnalysis.DateInService, tblFailureAnalysis.DateOutService, tblFailureAnalysis.[Tube Hours]

FROM (tblFailureAnalysis INNER JOIN tblPartTracker ON tblFailureAnalysis.PartID = tblPartTracker.PartID) INNER JOIN tblPart ON tblPartTracker.PartNum = tblPart.PartNum

WHERE (((tblFailureAnalysis.PartID)=[tblFailureAnalysis]![PartID]));

Like I said, I can create the necessary child records in my subform

but PartID is not being populated and I get this errror.

It creates the child record, but does not populate the PartID, which is in the main form.

This is what is populated in the subform after saying ok to the error message.

I can manually add the PartID, and that seems to work as now the main form and subform have made the relationship. As far as the control source, I just need to know how to populate the PartID in the subform and not get the error message.

Thanks

John

Answer 2

Is there any reason you have joined multiple tables in the query that is used as the record source for the main form?

As a general rule, that's not useful, and can, as in this case, introduce unnecessary complications.

The idea behind the Main form/Sub form design is that the TABLE on the one side of a relationship is bound to the Main Form while the TABLE on the many side of that relationship is bound to the Sub Form. You can use queries based on those single tables.

Adding multiple tables to a query like this simply complicates things exponentially.

If there are fields being added to the main form in both the One Side table and other, related tables, then you ought to consider breaking those out into their own subforms. You can have several sub forms in a Main Form and the management of the data is so much less fussy that it's almost invariably the smoother approach. Use a tab control for each of the sub forms, if that works better for real estate management.

The one thing I'd avoid, though, is complicating things with a multiple table query in a data entry form. They're fine in reporting or analysis forms, of course, where you don't have to struggle with adding, updating or deleting so many records from so many tables all at once.

Answer 3

What George said. But I think the real issue here is that you have PartID from 2 different tables in the query. This is unnecessary and maybe what's complicating the issue. You only need ONE PartID column since they will always be the same value.

But If FailureAnalysis is in a 1:1 relationship with Part Tracker, you can FailureAnalysis in a subform. You can even make that subform look like part of the main form, by removing the borders.

Answer 4

One point of clarification regarding the use of multiple tables in a query used as a form's RecordSource property. 

It is perfectly valid to include a table which is the referenced table in a relationship with the table into which a row is being inserted, edited, or deleted by means of the form.

However, it is not valid to include a table which is the referencing table in a relationship with the table into which a row is being inserted, edited, or deleted by means of the form.

A referenced table is a table which is on the one side of a one-to-many relationship type, a referencing table is that on the many side of the relationship type.

In your case, if it is tblFailureAnalysis into which a row is being inserted, edited, or deleted by means of the form, then the inclusion of tables tblPartTracker and TblPart is valid, provided that any controls bound to columns from the latter two tables are read-only (Enabled = False; Locked = True).  This will show the values from any columns in referenced rows in those tables for information purposes, but the user will be unable to edit those values.  If it is not necessary to show such values for information purposes, then the inclusion of the two referenced tables would be pointless of course.  As others have said only one of the key columns should be returned in the query, that in the table into which a row is being inserted, edited, or deleted by means of the form.

Only if the purpose of the form were to be to insert, edit, or delete rows from tblPartTracker, and/or tblPart would the query be invalid as the form's RecordSource as tblFailureAnalysis  is the referencing table in the relationship type with tblPartTracker.

It appears from the design view of the query that in none of the relationships is referential integrity enforced.  This is a serious omission, and may well have a bearing on the failure of the control bound to PartID to be populated.

Answer 5

Oh boy. I know this is not your current issue so I will yield back but this database design needs work. Post a new item if you want to discuss. The topic is "storing properties as column names".