asked on

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?

Graham Mandeno

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:
=subfrmProjectPurchaseOrdersDS.Form!txtPOnumber

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]

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Graham and Rey... none of the suggestions worked. I really do not like filtering.

Graham Mandeno

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)

try

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

or this codes

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

Jeffrey Coachman

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, ....it 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.

JeffCoachman

PatHartman

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
Me.Parent.sfrmOther.Requery

However if the other "subform" is a popup form, then use an argument in the OpenForm method
docmd.OpenForm "yourformname",,,"SomeFK = " & Me.PK

SteveL13

ASKER

I ended up with this and it worked:

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

Open in new window