How to Retrieve Values on a Subform from a Parent Form and Vice-Versa in MS Access

[@ledahbernardi1070]

Thank you, for your class, just amazing. I´d like to watch the lesson, where you build this orders_form, can you inform the link? I need to count, my subform, the way I saw in your subform order items. Thank you a lot.

[@seanmackenziedataengineering]

I created those tables for a duplicates demonstration: kzbin.info/www/bejne/qnrNpJiij8eIY6M So there isn't really a video about those specifically! You can see how to design similar ones here though: kzbin.info/www/bejne/qqm0mapse9Bog68

[@jobyjohn1]

Thank you so much. You saved my life. 😊

[@seanmackenziedataengineering]

You're welcome!

[@justinreed1388]

Hey Mate, very helpful. THanks!

[@seanmackenziedataengineering]

My pleasure!

[@satyabanukil779]

Wonderful presentation Sir. Learnt a lot from the tutorial. Can I update any subform field based on a field on the parent form with "if" condition.

[@seanmackenziedataengineering]

Yes, you can, you can update as many subform fields as you want, using these methods. You can even use After Update on some field in the parent to check and update things on the subform.

[@faez8220]

Thank you so much! this helped me a lot.

[@seanmackenziedataengineering]

You're welcome!

[@akokohjoel9854]

thanks for your video. Pls from the video how can one update data from one subform to another subform. That's a value in subform A can move to subform B. What vba code will do that by clicking or double clicking ?

[@seanmackenziedataengineering]

Say you wanted to move a value from subformA to subformB. You can do it by going to the subform's text box properties > Events tab > Double-click event > ... button > Code builder > OK > in the event sub that is created, something like: Forms!frmB!subformB.Form("txtBoxOnSubFormB") = Me!txtBoxOnSubFormA or try: Forms!frmB!subformB.Form!txtBoxOnSubFormB = Me!txtBoxOnSubFormA Adjust values for each text box that you want to move values for.

[@stanTrX]

Thanks. I want to show some additional calculated data for display purposes only for my query fed form but probably due to the additional joins it is not unupdateable. What might be the solution? I want these to be seen on the same row. What i think maybe a side by side subrows which are perfectly aligned or maybe a vba event which will change the row source of the field for the fields which shall be updated.

[@seanmackenziedataengineering]

Lots of different ways to do it. You could output the query including all of the calculations into a temp table ttmpMyTable as the form opens, the form operates on ttmpMyTable as normal with updates for some fields. When the user updates those, catch it with an After Update event that simply updates the source table. kzbin.info/www/bejne/qaW0maV8jpmebqc

[@stanTrX]

@@seanmackenziedataengineering thanks Sean. I have used dlookup in my single query to pull other display data, this way i can edit what i really focus and also display additional. But i am interested about your solution, hope you make a video about it. What i dont understand is how to update data from that temp table to the original tables as in my case relations are complicated

[@richardmeborg4717]

Hi. I have built a database, whict I have pricing of a product, where i calculate aall the ingredience to become a full productr to sell. That price i get out theree, I would lioke to automatacally be shown in the actually product form without linking those tw form that has nothing to do with each other. Please can you help me?

[@seanmackenziedataengineering]

Open your product form in design view. Grab a text box from the Controls on the menu. Into the design of the text box (or in Properties > Data > Control Source) you can type something like: =DSum("IngredientPrice", "ProductIngredients", "ProductID = " & Me!ProductID) Each time you open the form, it will find the calculated price for your product and show it in that text box.

[@rohancorea750]

Sean I appreciate all your video tutorials. I have a request. Can you do a video on how and when to use Bang ! and Dot . It is not very clear to me. Thanks

[@seanmackenziedataengineering]

This is a good idea for a video, and there are misconceptions among junior Access devs. As a rule of thumb, avoid using . where you would use an Access *expression* with ! to reference an object. This is because using . may not work in all situations, for example, when front end files are moved to different computers, or when they are run using different versions of MS Access. I would also suggest to avoid simply using control names without Me! qualification for the same reason. I'll put that one on my list of videos to do!

[@rohancorea750]

@@seanmackenziedataengineering Thanks, Sean.

[@rajeshmajumdar4999]

Thank You Sean!

[@seanmackenziedataengineering]

Welcome!

[@navisalomi]

thanks for sharing. really useful tool 😎

[@seanmackenziedataengineering]

Thanks for watching!

[@chaudhrypoultry8757]

Hello 👋 👋 Hi dear sir I want to undo subform from the parent form plz help me.

[@seanmackenziedataengineering]

You can select the subform control on your parent form, then click delete on your keyboard, and it will be removed. Then you can try again. Sometimes it is tricky to select.

[@satyabanukil779]

Good Morning Sir. So far I had been working with single table as I was not conversant with relationship. Anyway this time created two tables having "PAIR", a numeric field. Made a relationship between the tables with PAIR as primary key in parent form. I want to fetch all data from parent form to rows in a linked tabular form where PAIR in form 1. For example if in the first page of parent form shows PAIR =1 then it will automatically show 1 in the tabular form. It is a separate form and not a sub form. I followed your tutorial but shows error. Then I tried with DLookUp but this cannot be applied when parent form PAIR changes. So when you get time, kindly present a tutorial on this problem. Thnx and regards

[@seanmackenziedataengineering]

It is a separate form and not a subform, so you will use On Current kzbin.info/www/bejne/gZW1Yn-MqZiJgMU on the parent form. On Current happens every time you navigate to a different record. One line on parent form On Current event: Forms!MyForm2!PAIR = Me!PAIR

[@satyabanukil779]

@@seanmackenziedataengineering Thank you Sir. I tried with your code. But perhaps, it has some limitation. First of all, I have to keep the second form open. Secondly, if I type 3 in the main form field, it updates the second form (which is a tabular form) only as far as the first row is concerned. What I want is if in the parent form first page, I type 6, 6 will appear in all the rows of the second form and not only the first row. Then If my open page of the first form shows 7 then all the rows of the second form will act accordingly. Regards

[@tummalatube]

Suppose My form can be used as standalone form as well as a subform. When it is used as a standalone form, Me.Parent is failing even while validating if parent exists. How to meet this scenario? Thanks. I am a subscriber to your channel and a regular follower.

[@seanmackenziedataengineering]

Thanks for subscribing! For that situation, you can use something like: If CurrentProject.AllForms(MYFORM).IsLoaded Then MyVariable = Forms!MYFORM!txtWhatever Else MyVariable = Null End If That will work if it is a parent or if it is open separately. Good luck!

[@tummalatube]

You not only upload videos regularly but also answer everyone's questions. Thanks a lot.

[@tummalatube]

@@seanmackenziedataengineering Thanks Sean, Can i discuss one more scenario. I have no idea if i can discuss here but relevant. I open a form on click of a button. The opened form is being used as a standalone form as well as subform under other form(s). From such a form, how can i pass a selected record reference (like primary key) to the form from which i opened this from on click of a button. If it is fit scenario to discuss in a separate video, we request you to do the same or pls answer here itself. Thanks in advance.

[@satyabanukil779]

Suppose I have single table database and having five forms under one table and each form has a field "Total" representing each person. Now I want to create another form in which I want the sum of all five individual form totals with where condition e.g. grand total against a particular individual. If should be possible but I don't know how to do that. Sir if you get time, kindly make a tutorial on it for which I shall be grateful. Regards.

[@seanmackenziedataengineering]

You sure can! You could try many ways to do this: - Create a summary query on the data with the sums you want. Then put a Dlookup on your form in text control, something like =DLookup("MyTotal", "MyTotalQuery") 'you can also use a condition if you need it - Create a text box with a big nasty expression in it like: =Forms!Form1!SubForm1!txtSubtotal+Forms!Form2!SubForm2!txtSubtotal+ .. etc You may also try: Forms!Form1!SubForm1.Form("txtSubtotal") as alternate syntax - Create a VBA Function to add them up and then just put =MyVBAFunction() in the text box. Give it a try!

[@satyabanukil779]

@@seanmackenziedataengineering Thnx for your kind advice Sir. As I was out of town couldn't get to look into. Definitely shall try it out and give you a feedback. However don't have any idea on the VBA function application. A video tutorial on this would be very helpful provided you get time. Regards

[@satyabanukil779]

Thnx Sir for your prompt and kind advice. I tried the way as advised. But it is showing an error message " ...cannot find the object Table2 (my form name from which the data to be brought to the other form). So I think I am making some mistake in putting the code correctly. In fact the first tabular form I have two fields (Pair No and Score, both numeric fields). In the first form in the first Row the data against Pair No is 1 and the score against 1 is 10. In the same form, in the second row Pair No is 2 and Score is 20. In the second tabular form I have a field SCR1 and SCR2. I want to update the second form SCR fields based on the score corresponding to the "Pair No' of the first form. For example, SCR 1 will be 10 (taking it from the first row of the first form corresponding to the Pair No). Regards.

[@artistryartistry7239]

Can you please consider making a video on how to link two subforms? :)

[@seanmackenziedataengineering]

Good idea! Do you mean a subform inside another, or two on different forms?

[@marcelodineck3524]

Hi I tried it but I still having error, I am try call the subform and seems is not referenced correctly Error 2450 or 2465. Could you suggest anything? I put the pictures here: drive.google.com/drive/folders/17C6avl18oceECq4cJ3nsRsWpYp5kLSBd?usp=sharing

[@seanmackenziedataengineering]

Make sure to open your form in the background before opening this report. So, the form cannot be open and closed before the report opens, just leave it open in the background and then open the report. Your code should execute. Spelling looks good.

[@seanmackenziedataengineering]

Also put Dim frm As Form at the top of your procedure.

[@marcelodineck3524]

@@seanmackenziedataengineering Thank you,

[@AsanIT]

Thanks from all great work. Please tell me possibly that I have done referenceing combo boxes from a form within a query it worked but when I put the form inside navigation form it did not work. Actually I am asking how to reference form controls of a form within a navigation form. Plenty of websites suggested many ways such as forms!navform!customerForm.form!combox1 it did not work. Regards

[@seanmackenziedataengineering]

@@AsanIT try instead forms!navform!customerForm.form("combox1")