How to Retrieve Values on a Subform from a Parent Form and Vice-Versa in MS Access
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- Sometimes when you're developing forms in MS Access, you need to get particular value from a subform, or a value from the parent form. This can be tricky, but in this episode I'll show you several ways that you can reference values in your subforms or parent forms. You can use VBA that is running on the form's code, or you can use epxressions in text boxes. Let's go take a look at how to do our subform and parent form value lookups!
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
For developers looking for a new role, check out:
www.toptal.com...
Want my team to do a project for you? Let's get to it!
system.billzon...
• How to Retrieve Values...
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.
I created those tables for a duplicates demonstration: th-cam.com/video/tIjselZbU-A/w-d-xo.html So there isn't really a video about those specifically! You can see how to design similar ones here though: th-cam.com/video/txQhw6Fk5MM/w-d-xo.html
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 ?
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.
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.
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. th-cam.com/video/stQhrFY4k8E/w-d-xo.html
@@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
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?
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.
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
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!
@@seanmackenziedataengineering Thanks, Sean.
Thank you so much. You saved my life. 😊
You're welcome!
Hey Mate, very helpful. THanks!
My pleasure!
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.
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.
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
It is a separate form and not a subform, so you will use On Current th-cam.com/video/KdR1LVt3VJc/w-d-xo.html 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
@@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
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.
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!
You not only upload videos regularly but also answer everyone's questions. Thanks a lot.
@@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.
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.
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!
@@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
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.
Thank you so much! this helped me a lot.
You're welcome!
Thank You Sean!
Welcome!
Can you please consider making a video on how to link two subforms? :)
Good idea! Do you mean a subform inside another, or two on different forms?
thanks for sharing. really useful tool 😎
Thanks for watching!
Hello 👋 👋 Hi dear sir
I want to undo subform from the parent form plz help me.
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.
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
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.
Also put Dim frm As Form at the top of your procedure.
@@seanmackenziedataengineering Thank you,
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
@@AsanIT try instead forms!navform!customerForm.form("combox1")