Open form and filter the subform

[Gaztry80]

Hi,
I am trying to open a form (Form B) from Form A by making a VBA code with "DoCmd.OpenForm" and apply the where clause on the current ID field.
This is working great when in the form that opens, the ID field is in the main form.
However, i want to go one step further and open a form with a subform, where the subform needs to only display the corresponding fields of the Id field from form A.

The code I have tried to make is the below by double click on the person Id field on Form A in the demofile:

DoCmd.OpenForm "FormB", _
WhereCondition:="Forms.FormB.Friends.Form.PersonId=" & Me.PersonId

I do not understand why the above code is not working.
Could somebody help me fix this problem ?

 

[June7]

The WhereCondition criteria can only apply to form that is named in the OpenForm method, not its subform.

DoCmd.OpenForm "FormB"
Forms.FormB.Friends.Form.Filter = "PersonIDRef =" & Me.PersonId
Forms.FormB.Friends.Form.FilterOn = True

Might find this of interest https://www.fmsinc.com/MicrosoftAcc...ink Child,based on a control on the main form.

 

[Uncle Gizmo]

The subform resides in a subform/subreport control and this this control has the ability to link your main form to your subform, usually on ID.

It creates a parent-child relationship between your main form and your subform and only presents records in the subform that relate to the current ID the main form.

It doesn't appear that you are taking advantage of this built-in functionality, and I wondered why?

 

[Gaztry80]

The subform resides in a subform/subreport control and this this control has the ability to link your main form to your subform, usually on ID.

It creates a parent-child relationship between your main form and your subform and only presents records in the subform that relate to the current ID the main form.

It doesn't appear that you are taking advantage of this built-in functionality, and I wondered why?

Hi Uncle Gizmo, I try to understand your point / question.
I thought this parent-child relationship was only possible within the same form.
Do you suggest that in FormB-main form I create a connection with FormA and then link formB-main with formB-sub?

 

[mike60smart]

Hi Uncle Gizmo, I try to understand your point / question.
I thought this parent-child relationship was only possible within the same form.
Do you suggest that in FormB-main form I create a connection with FormA and then link formB-main with formB-sub?

Hi
Uncle Gismo is telling you that your Main Form should be based on People and the Subform is based on Friends

To achieve this layout you must set Referential Integrity between the two tables by using the PeopleID as the Primary Key
in the People Table and then link to the Foreign Key - PeopleID in the Frields table

See the attached example

 

[Gaztry80]

Hi
Uncle Gismo is telling you that your Main Form should be based on People and the Subform is based on Friends

To achieve this layout you must set Referential Integrity between the two tables by using the PeopleID as the Primary Key
in the People Table and then link to the Foreign Key - PeopleID in the Frields table

See the attached example

Okay, I understand, but i know how to do that.
I want to create a "pop-up" form instead of putting it in the same form for saving space in my actual database.

 

[June7]

Okay, did you see post #2?

Why embed Friends in a parent form to begin with? Open it standalone and can use

DoCmd.OpenForm "Friends", , , "PersonIDRef =" & Me.PersonId"

 

[Gaztry80]

Okay, did you see post #2?

Why embed Friends in a parent form to begin with? Open it standalone and can use

DoCmd.OpenForm "Friends", , , "PersonIDRef =" & Me.PersonId"

Yes! This works and fixes the problem
However, I also want to understand which method is best according to "the book".
The reason why I put in Friend as a subform is that i want to put in a second subform in FormB with for example comments and is related to the Friends form in FormB. I am thinking about making a button which can make this comment box visible/invisible and then also to resize FormB in case this is applied. In the attached file you will find the new demofile with this comment forma and your and Uncle Gizmo / mike60smarts suggestions applied. Just for the information, I am not a pro in developing, i just want to prevent to work myself down the rabbit hole . What do you think about the method applied in the demo file? Resizing the form is something I need to look up on the internet, but let me try first !

 

[mike60smart]

Yes! This works and fixes the problem
However, I also want to understand which method is best according to "the book".
The reason why I put in Friend as a subform is that i want to put in a second subform in FormB with for example comments and is related to the Friends form in FormB. I am thinking about making a button which can make this comment box visible/invisible and then also to resize FormB in case this is applied. In the attached file you will find the new demofile with this comment forma and your and Uncle Gizmo / mike60smarts suggestions applied. Just for the information, I am not a pro in developing, i just want to prevent to work myself down the rabbit hole . What do you think about the method applied in the demo file? Resizing the form is something I need to look up on the internet, but let me try first !

Yes Your Popup Form works but this only allows you to view Friends which you must have entered directly into the table?

If you want to add Friends using a Popup Form then you will need to use what is known as "Open Args"

 

[Gaztry80]

Yes Your Popup Form works but this only allows you to view Friends which you must have entered directly into the table?

If you want to add Friends using a Popup Form then you will need to use what is known as "Open Args"

I will look into "Open Args", thanks! I now update the Friend table by using this code when I add a friend.

Private Sub Friend_AfterUpdate()
Me.Form.PersonId.Value = Me.Form.txtPersonId.Value
End Sub

Dont know if this is smart to do, but it works haha
If i understand "Open Args" correctly, the data will be passed instant by the DoCmd.OpenForm command, which will save a text box and reference.
Which makes it more efficient.

 

[MajP]

If you want to play with showing, hiding, and resizing see demo.

 

[Gaztry80]

If you want to play with showing, hiding, and resizing see demo.

Thanks for sharing your idea and code. It is funny since i was thinking to create some sort of conditional format to highlight the selected row. In order to prevent confusion about which Id gets the comment. Till now I have made showing/hiding as in the attached demofile.