How Link Subform Using Associated Record on Second Subform 1

[nyamrembo]

Hi, I have an Access main form with a subform linked to it. The subform is in Tabular format and the relationship between the main form and the subform is one-to-many. I also have another popup subform that I want to link to the second subform using the ID field on the first subform.
Forms are as follows:
FormA = main form
FormB = Subform - linked to FormA using FormA Auto Primary key and FormB foreign key (from FormA table)
FormC = subform - I want to link to FormB.
FormB may have more than one record and for each record in FormB, there is an associated record in FormC. So what I would like is to have the user click on the ID field on FormB and FormC pops up linked to the record ID selected on FormB. I hope it makes sense. Please let me know if you are confused or have additional questions. You help is greatly appreciated.
All my primary and foreign key fields are auto. Tables can assume the names of tblFormA.ID, tblFormB.ID, tblFormC.ID.

Nyamrembo:-D

 

[MajP]

Do you want to actually link form C to B or just open form C to the selected record in B?

If you want just want to open it, look at the 4th parameter of the Docmd.openform method
Something like "[foriegnKeyName] = " & me.tableBid

Is form C opened as dialog?

If you open it to a specific record, normally you open the form as dialog and return only one record. Then you close it to go back to formA.

If you want to synch, normally you open C not as dialog and return all records. Then you can shift focus back to formA, move to a different record, and synch the movement of the forms.

Technique one is easy. Option 2 is less common, and requires more code. These are two different effects.

 

[nyamrembo]

Thank for your help. to answer your questions,
I want to open FormC to the selected record in B and I want to open form C in dialog. Need help with the code and where to put it.

Thanks again,

(-:

 

[MajP]

Well you said you want them to click on the ID field textbox. So put the code in the on click event of that textbox.

Did you look in the help file under Docmd.openform? It explains and gives examples. The help file is very useful and you should get familiar with it.

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

docmd.openform "frmC",,,"[theNameOfTheForeignKeyField] = " & me.IDfieldNameInTableB,,acdialog

if the ID field is alphabetic add single quotes
"[theNameOfTheForeignKeyField] = '" & me.IDfieldNameInTableB & "'

 

[nyamrembo]

It worked! Thank you!

 

[nyamrembo]

Hello,
I have FormC opening to a specific selected record on FormB when it's an existing record. That works fine. But when I enter a new record on(FormB) and click on the Auto generated ID field to link up the new record to (FormC), the (FormC) opens up but the linked ID from (FormB) is not automatically populating in FormC. I have to refresh the main FormA then go back and select the new record in (FormB to enter the associated record in FormC. This also creates orphan records. Is there a way to avoid this refresh process of Main FormA. I would like the user to be able to add the associated record FormC without having to stop to refresh.

 

[PHV]

Me.Dirty = False
DoCmd.OpenForm ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886