Refresh subform based on record change in main form Access 2013

I have a subform within a main form that queries records tied with a unique record on the main form.  I have the following sub in my code:
Private Sub Form_Load()

Me.RecordSource = Me.RecordSource
Me.Child231.Form.Filter = "[RecordID]= '" & Me.CompName & "'"
Me.Child231.Form.FilterOn = True

End Sub

Open in new window


This updates the subform so it only displays records pertaining to the record selected on the main form.  However, when I click next record on the main form, the subform continues to display records pertaining to the first record viewed in the main form.  I'd like for the subform to refresh itself so it displays recorded pertaining to the record selected in the main form.  

I attempted to try to requery the subform in Private Sub CompName_AfterUpdate() but that did not seem to do anything.

Thanks in advanced!
Muhammed UmairAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need the same code in the OnCurrent event.  

But why are you not simply using the master/child links of the subform control?  Access would do it for you then.

Jim.
Muhammed UmairAuthor Commented:
I tried that, choosing CompName as the Link Master Fields and Link Child Fields, however, now it shows me the unfiltered query in the subform.  Before, it would at least filter by the first record in the main form.

Also, I have to type in CompName manually in Link Master fields and Link Child fields, if I click on the three dots I get the following error: Can't build a link between unbound forms.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There's no reason for it not to work.

If the master/child links are set correctly, as soon as the main form record changes, the subform would requery.

Jim.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PatHartmanCommented:
Delete the subform from the main form.
When you add it back, Access will suggest Master/Child fields based on the relationships you defined in the relationships window.  You did relate the tables didn't you?

This mainform controlling subform is built in Access behavior.  You don't need to write code to make it happen.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Muhammed UmairAuthor Commented:
Just to confirm, If I want to my subform to update based on the CompName field, I would enter CompName for both Link Master fields and Link Child Fields, correct?
Muhammed UmairAuthor Commented:
I guess the way I initially added the subform did not let me link it to the any fields on the main form.  Once I deleted the sub form and ran thru the wizard, I was able to link the two forms together and saw that the subform now updates based on the record selected in the main form.  Thanks.
PatHartmanCommented:
The names of the two relating columns do not have to be the same but they will always be the PK of the master record and a FK in the child record.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
One other point; the master link can be a field name or a control name.  The later comes in handy in a number of situations, one of which is if you have two sub forms on one main form.

Say Customers (main), orders (1st subform), line items (2nd subform).

Using a hidden control on the main form, you can set the 2nd subform controls master link to that control.  

Then from the 1st subform, update that control in the OnCurrent event.

What you end up with is; main record becomes current, 1st subform gets a current record, and 2nd form is automatically synced.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.