How to link two identical subforms together?

[ittechguy]

I have a main form which has two identical subforms. The main form displays all of the rooms in tblRoom and it allows me to add new rooms or make changes to existing rooms.



The first subform (I'll call the Subform1) is in datasheet view and it displays all the cabinets within a room. Currently, RoomPK from the main form is linked to RoomFK in the subform.

The second subform (Subform2) is in regular single form view and I want to use it to add new cabinets to tblCabinet as well as make changes to existing cabinets. I'm trying to make it so that if I click on a row from Subform1, subform2 will requery to display those records and allow me to make changes to them.

I'm not sure whether I should link the subforms together and requery Subform2 from OnCurrent of Subform1, or if I should link both subforms separately to the main form and then filter Subform2 by the CabinetPK from Subform1.

[ItsMe]

You might be able to write a single procedure that will bookmark the current record of the main form and then requery the Main Form. The procedure could be placed in the Main Form's module and could be made Public. Using afterupdate events of various controls of the two subforms to call the Public Procedure would probably work.

[RuralGuy]

You should be able to do what you want simply by using the LinkChild/MasterFields of the two SubFormControls. No Requery would be necessary as Access will do all of the work for you.

[ItsMe]

...No Requery would be necessary as Access will do all of the work for you.

Yeah, I was kinda wondering why there is an issue, anyway.

[RuralGuy]

Many do not understand the power of the SubFormControl and it's properties.

[ittechguy]

You should be able to do what you want simply by using the LinkChild/MasterFields of the two SubFormControls. No Requery would be necessary as Access will do all of the work for you.

I've been trying that, I must be doing something wrong.

Subform1 is currently linked to the main form using RoomPK and RoomFK. Subform 2, I have CabinetPK from Subform 2 linked to CabinetPK from Subform1. In link master fields for Subform2, I have this: [Subform1].[Form]![txtCabinetPK]. There is no records in Subform2, so I think its not linked properly.

I've created a text box and set its ControlSource to =[Subform1].[Form]![txtCabinetPK] and I that text box changes its value every time I click on a different row in Subform1. So I tried linking Subform2 to that txt box on the main form, but the subform still shows nothing.

[ittechguy]

This is why I thought I needed to requery the subform.



That is record 1 of the main form. You can see I've selected a cabinet in Subform1. The text box on the lower left corner displays 213 which is the CabinetPK for that record selected. Subform2 does not display any records. However, if I go to record 2 on the mainform, I see this:



Which displays the record that was selected on the previous page. Also if I select a record from Subform1, click on Subform 2 and hit F5 it does display a record in Subform2 as it should. So I tried placing this code in Form_Current of Subform 1: Forms!frmMainAddRooms.Form![Subform2].Requery, but its not working.

[RuralGuy]

How were you referencing the Text Box from Subform2? It may get confused unless you go through the Forms collection.

[ittechguy]

This is in "Link master Fields" of Subform1: [Forms]![frmMainAddRoom]![Subform1].Form!txtCabinetPK. Child is set to CabinetPK.

[RuralGuy]

That does not look right. It looks more like the Link Master Fields of Subform2.

[RuralGuy]

BTW, you need to make sure you are using the correct name for your Subform Controls. They can fool you sometime.

[ittechguy]

I'm sorry, I had mixed them up. Yes, thats the link master fields of Subform2. So I guess, thats how I reference the text box from Subform 1. I'm not referencing a text box on Subform 2. Should I be?

[RuralGuy]

In your post #7 are the pictures showing *both* Subform1 and Subform2? Where is the Main form. Sorry, just struggling to wrap my mind around your form.

[ittechguy]

Sorry for the confusion. I'll try to add more detail.



The main form is called frmMainAddRoom. Its recordsource is set to tblRooms. Form header is used to add new records or to make changes to existing records. There is a subform in the middle which is unbound and acts kinda like a split form. The main form is filtered by clicking on a cell in the subform. Also the subform and the mainform is filtered using the search form. I can change whether the form is in Data Entry mode or not.

The problem is I cannot add tblCabinet to the recordsource of this main form and be able to edit cabinets because doing so would create duplicate entries for Rooms in the main form.

My solution was to place a subform on the main form and use that subform for editing cabinets. That turned into two identical subforms because a subform cannot be in datasheet and single form view at the same time. So Subform1 is in datasheet and it displays cabinets which are in the room viewed on the main form. Subform2 is in single form view and it is used to either add a new cabinet to the room displayed in the main form, or to make changes cabinets displayed in subform 1.

Which, the more I think about it, linking Subform2 to Subform1 creates problems when it comes time for me to use Subform1 to add new cabinets to that room.

In the past I've used IF statements to fire if the form is in Data Entry view or not, but I don't think I can enter records into a bound form. Maybe there is VBA code to unlink the subforms and then unbind them (.ControlSource = "") and then insert values into tblCustomer using DAO recordset.

[ittechguy]

I got it working!

First off OnCurrent of Subform1 wasn't firing for some reason. I went into design view, hit event tab and there was nothing there. So I clicked on code builder and my code was there. Sometimes it just doesn't make the connection. Then I found out that the form name was frmMainAddRoom, not frmMainAddRooms. Now it requeries automatically.