Requery 2nd subform on unbound main form (1 Viewer)

[DavRob]

My scenario is to create a form where I can search for a customer then edit their associated Contacts

I have a main form that has an unbound text box "txtSearch" that searches the Customers Table, using the search as you type method, the results are populated in subform -1 "frmContacts_CustSub"

subform - 2 "frmContactsEditSub"displays the Contacts associated to the customer displayed in subform -1 "frmContacts_CustSub"

I have used the AfterUpdate event of field "CustName" in subform -1 Me.Parent![frmContactsEdit].Form.Requery

The main form "frmContactsEdit" is an unbound form

I have researched the internet, but can't find a solution, many answers online suggest utilizing the Link Fields, I have tried multiple combinations but this stops results from being displayed.

Any help would be greatly appreciated

DavRob

 

[XPS35]

First of all I think the after update event will not work. You do not change the customer name, so the event won't fire.
A requery of subform-2 will also only work If the recordsource of the form refers to the selected customer. Without knowing that recourdsource, I doubt this will be the case. I can't think of a way how to do that when you designed the form.

A solution could be to create a on dubble click event on the customer name. In the event change the recordsource of sub-2. Something like

Me.Parent![frmContactsEdit].Form.RecordSource = "SELECT * FROM Contacts WHERE CustId = " & me.CustID

 

[arnelgp]

many answers online suggest utilizing the Link Fields,

you can do that, provided you have Primary Key on your Customer Table and an associated Foreign Key on your Contacts table.
see this demo:

 

[DavRob]

arnelgp
Thank you for the example it is exactly what I am trying to achieve.

Could you please explain why the txtCriteria box is required, I have seen the dummy text box in other examples of search as you type but it seems to work ok without it.

XPS35
Thank you for your reply

DavRob

 

[arnelgp]

i am Requerying the customerF subform in the Change Event of txtSearch textbox.
on the Change Event, there is No Value yet in txtCriteria, what is intact is .Text property (what you have typed).
so if i use txtCriteria, on the Query1 (customerF uses this query), i will get Nothing (no matter what i typed).

but again on this Event i assign the .Text property to another textbox, txtCriteria (and therefore becomes it's Value).
that is why i uses txtCriteria and not txtSearch on the query.

you can use txtSearch, not on Change event, but on AfterUpdate. Or if you have a button (therefore will leave focus from the textbox
when you click the button and update it's Value).

 

[DavRob]

arnelgp
Thanks for the feedback, I thought that there was a valid reason

DavRob

 

[Pat Hartman]

If the second form is related to the first form, use the current event of the first form to requery the second form. Use a where clause in the RecordSource of the second form:

Where someID = Forms!mainform!subform1.Form!someID

Let Access build the field reference in the QBE so you get it right.

 

[DavRob]

Thanks Pat

Always handy to have alternate methods

DavRob

 

[arnelgp]

Always handy to have alternate methods

that was first suggested earlier in post #2.
the problem with this is if you need to add New contact for the customer, you will need to manually add (by vba or sql) the Foreign key.
using Master/Child Link, the Foreign key will be saved automatically and no other code is needed.

 

[Pat Hartman]

that was first suggested earlier in post #2.

No, it was discounted rather than recommended.
And yes, when a form is not a subform as this one is not since it is not a child of the form holding the parent data, the FK needs to be set via code in the form's BeforeInsert event. Using the BeforeInsert event is best since it doesn't run until the user dirties the form. So, in that event, it would be:

Me.theFKfield = Me.Parent.subform1controlname.Form!thePKfield

 

[DavRob]

Thank you for the reply Pat.
I'll give it a try

DavRob