Open form and filter the subform (1 Viewer)

Gaztry80

Member
Local time
Today, 15:48
Joined
Aug 13, 2022
Messages
52
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:

Code:
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 :)?
 

Attachments

  • demofile.accdb
    1.3 MB · Views: 94
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:48
Joined
Jul 9, 2003
Messages
16,245
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

Member
Local time
Today, 15:48
Joined
Aug 13, 2022
Messages
52
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

Registered User.
Local time
Today, 14:48
Joined
Aug 6, 2017
Messages
1,899
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
 

Attachments

  • demofile.zip
    26.9 KB · Views: 77

Gaztry80

Member
Local time
Today, 15:48
Joined
Aug 13, 2022
Messages
52
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

AWF VIP
Local time
Today, 06:48
Joined
Mar 9, 2014
Messages
5,425
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

Member
Local time
Today, 15:48
Joined
Aug 13, 2022
Messages
52
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 :p. 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 :)!
 

Attachments

  • demofile.accdb
    1.4 MB · Views: 80

mike60smart

Registered User.
Local time
Today, 14:48
Joined
Aug 6, 2017
Messages
1,899
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 :p. 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

Member
Local time
Today, 15:48
Joined
Aug 13, 2022
Messages
52
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.

Code:
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.
 

Attachments

  • demofile.accdb
    1.4 MB · Views: 73

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:48
Joined
May 21, 2018
Messages
8,463
If you want to play with showing, hiding, and resizing see demo.
 

Attachments

  • MajP_demofile.accdb
    1.4 MB · Views: 85

Gaztry80

Member
Local time
Today, 15:48
Joined
Aug 13, 2022
Messages
52
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.
 

Attachments

  • demofile.accdb
    1.5 MB · Views: 83

Users who are viewing this thread

Top Bottom