confused! (1 Viewer)

[cokeblue8]

i have a form with two subforms. say, frmMaster (parent), frmDetail and frmProblem. frmDetail and frmProblem are connected to each other by DetailID and both are connected to frmMaster by SheetID. I made it so that when the user selects a new record in frmDetail, frmProblem has its DetailID automatically updated to match. However, if the user made a mistake and forgot to enter a problem corresponding to a previous record for frmDetail, they will have to manually update the DetailID box. (I do NOT want to make this field visible to avoid potential screw-ups!) Is there any way around this??

 

[RuralGuy]

If frmProblem has the LinkChild/MasterFields filled in with the DetailID field and pointing back to the frmDetail SubForm, then the DetailID in the frmProblem SubForm should be completed by Access without any help from you. Then the LinkChild/MasterFields of the frmDetail will just include the SheetID field. I am assuming that the DetailID is an AutoNumber PK in that SubForm's RecordSource.

 

[cokeblue8]

DetailID and SheetID are both autonumbers and PKs. The SheetID is automatically filled in on frmProblem because there can only be one sheetID per record on frmMaster.

However, there can be more than one DetailID (there would be 10 detail forms filled out for 10 operating lines for example).

The DetailID is automatically generated (by code that I have written) for frmProblem when I select a new record on frmDetail. BUT if I have to fill in a problem corresponding to a previous detail form (say, for line 9 when the most current detail form I filled out is for line 10), the DetailIDs don't match. (the DetailID will be for line 10 when I want it to be for line 9)

 

[RuralGuy]

How about posting what you have for the LinkChild/MasterFields for both of the SubForms. I'm almost positive you can get Access to do all of the work for you without any code.

 

[cokeblue8]

Here's a simplified version of the database.

When you select the line in the frmLineDet (line detail), the DetailID appears in each of the problem forms (the ones along the bottom). However, when you select a new line in the PROBLEM forms, the DetID doesn't match the DetID for the line in the frmLineDet.

Thanks in advance!

 

[boblarson]

The problem lies in that ALL of your subforms are linked by SheetID and nowhere are any linked by DetailID. I think your design may be (I didn't look that close) that you need to have one, or more, of the subforms as subforms on the subform.

 

[cokeblue8]

now that you mention it, it DOES make a whole lotta sense to make the forms subforms of the subform. Thanks!

 

[RuralGuy]

The easiest way to get one SubForm to syncronize with another SubForm using the LinkChild/MasterFields properties is to use an invisible textbox on the MainForm that has its ControlSource set to the Linking key of say SubForm1 and then point the LinkMasterField property of SubForm2 to that invisible control. Once you get it set up it works like a dream.

 

[cokeblue8]

The easiest way to get one SubForm to syncronize with another SubForm using the LinkChild/MasterFields properties is to use an invisible textbox on the MainForm that has its ControlSource set to the Linking key of say SubForm1 and then point the LinkMasterField property of SubForm2 to that invisible control. Once you get it set up it works like a dream.

Does that mean I don't have to make the frmProblems subforms of the frmDetail subform, and can just do what you told me above? (Which, btw, can you please explain further? I'm a beginner at Access)

 

[RuralGuy]

Does that mean I don't have to make the frmProblems subforms of the frmDetail subform, and can just do what you told me above? (Which, btw, can you please explain further? I'm a beginner at Access)

Correct! The two SubForms can be side by side on your form. Create an unbound text box on your main form and set its visible property no NO. Set the ControlSource of this textbox to something like
=SubForm1.FORM.DetailID and leave the control visible while you are debugging the process. If we name this invisible control say SubDetailID then you can set the LinkMasterFields property of SubForm2 Forms.MainFormName.SubDetailID and point the LinkChildFields property to the DetailID field on that form. Everything should stay syncronized and the DetailID field on SubForm2 should populate automatically. Post back if you want additional assistance. It can be a little tricky the first time you do this.