Filter records shown on a sub-form based on a hyperlink field in a 2nd sub-form

I have a form with two sub-forms.  The main form is named frmOrders.  The 1st subform is named subfrmProjectPurchaseOrdersDS and is a datasheet view form and has a field named txtPOnumber that is a hyperlink field.  There is a 2nd sub-form named subfrmOrderParts. which is a datasheet view form also.   One of the fields on that form is named txtCustomerPOn.

When the user click the hyperlink on the 1st sub-form I need the 2nd sub-form to display only the records that have the same value as the field clicked on.

What would the onclick code look like?
Who is Participating?

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

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.

Hi Steve

You can probably do this with no code at all.

Create a textbox on your main form named txtCurrentPO, and set its ControlSource to:

Access will insert brackets in this expression but that is OK.

You can set the Visible property of this textbox to False if you wish to hide it.

Now, for subfrmOrderParts, set LinkMasterFields to txtCurrentPO, and set LinkChildFields to txtCustomerPOn.

Note that LinkChildFields should be the name of the field in your subform's recordsource, not the name of the textbox bound to that field.  If the field name is different, then use that instead of txtCustomerPOn.

-- Graham Mandeno [Access MVP since 1996]
Rey Obrero (Capricorn1)Commented:
try this

me.parent.subfrmOrderParts.form.filter  =  "[CustomerPOn]=" & me.txtPOnumber


me.parent.subfrmOrderParts.filter  =  "[CustomerPOn]=" & me.txtPOnumber


Forms_subfrmOrderParts.filter  =  "[CustomerPOn]=" & me.txtPOnumber

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
SteveL13Author Commented:
Graham and Rey... none of the suggestions worked.  I really do not like filtering.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

When you say it didn't work, can you elaborate?

If you make txtCurrentPO visible, do you see the PO number of the currently selected order in subfrmProjectPurchaseOrdersDS?

Are you sure you have set LinkMasterFields and LinkChildFields correctly?
Rey Obrero (Capricorn1)Commented:

with me.parent.subfrmOrderParts.form.recordsetclone
      .findfirst  "[CustomerPOn]=" & me.txtPOnumber
      if not .nomatch then
           msgbox "record not found"
     end if
end with

or this codes

with me.parent.subfrmOrderParts.recordsetclone
      .findfirst  "[CustomerPOn]=" & me.txtPOnumber
      if not .nomatch then
           msgbox "record not found"
     end if
end with
Jeffrey CoachmanMIS LiasonCommented:
What is not know is the relationship between the two subforms

Again, this is why you should always strive to post a sample database, and fully explain your objective.
Are the two subform at the same level below the main form?
Or is one subform a child of the other subform?

Without knowing all the details of this system, is difficult to get you a perfect solution based on a general explanation.

For example.
1. Whats the purpose of the hyperlink?,  What info does it contain?
2. < I really do not like filtering. >
Why not?, I cant see how you would achieve what you are asking for without some type of Filtering?

Hyperlinks are just as their names implies: "Fast Connections",
They typically bring you to a target location, however, they are not designed to trigger code (even though they can be programmed to do so.)
Then basic act of clicking on a hyperlink should bring you to that location (irl, email address, network location, ...etc)
It is counter-intuitive for clicking a hyperlink to open another form.
...Unless the hyperlink is a hyperlink to that other form, ...even then, "following" a form hyperlink cannot be made to apply any filters.

I would not use a hyperlink field for this purpose.  In fact, I don't use them at all since they are one of the data types that is "Access only" and can't be upsized.  To effect a hyperlink, simply use the FollowHyperlink method.  That will open any application with a defined association.  So .doc would open Word, .xlsx would open Excel, a URL would open IE, etc.

For this purpose, if the subforms are on the same main form, create a hidden field on the main form to hold the FK value.  In the "master" subform, format the control that you want to operate as a Hyperlink to always look like one even though it isn't one.  In the click event of the control, populate the hidden field and requery the other subform -
Me.Parent.txtHiddenFK = Me.PK

However if the other "subform" is a popup form, then use an argument in the OpenForm method
docmd.OpenForm "yourformname",,,"SomeFK = " & Me.PK
SteveL13Author Commented:
I ended up with this and it worked:

    Me.Parent.subfrmOrderParts.Form.Filter = "CustomerPOn = '" & Me.txtPOnumber & "'"
    Me.Parent.subfrmOrderParts.Form.FilterOn = True

Open in new window

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.