changing form source while it's active! ??

[debhead]

running access 2k in frontend/backend config.

Yes - I really want to change the active form's source while it's active. Just trust me when I say you really don't want to know why..... (let's just say performance issues)

Here's my source(s) for the subform: (all joined fields are indexed)

Q_lookup_S:


tbl1->tbl2

Q_lookup: << actual source for my form
Q_lookup_S->tbl3->tbl4


Here's what the form looks like:
F_items_rcvd contains sub-form: F_lookup_S; linked via a single common ID field.

F_items_rcvd is driven by Q_items_rcvd
F_lookup_S is driven by Q_lookup

I have an unbound field in F_items_rcvd, in which an item code is entered (ctl_entry). (after-update does a recordset clone/search, and set's this form's record to the matching one.)
I would like to alter the WHERE clause of Q_lookup_S to use only this code.

All of this I have already done.
My problem is - I can't get it to work right. The subform even after a requery/refresh, does not display the matching record. It's recordset reports "no records".

My question is - WHERE SHOULD I be inserting the code to make this change, and what must I do to ensure the query, as well as the subform is properly sync'd ?

Should I use on-current in parent? in sub-form? before-update in ctl_entry?
do I need my WHERE clause change in one event, and some kind of requery in another? which for each?

TIA - Bob

[debhead]

Well - figured it out on my own.

apparently microsoft never fully understood the meaning of ".requery".

access is bugged (no surprise there), in that .requery does NOT work in this application.

Instead - if I:
temp = Me!F_subForm_S.Form.RecordSource
Me!F_subForm_S.Form.RecordSource = ""
Me!F_subForm_S.Form.RecordSource = temp

This FORCES softmicro access to REALLY perform a ".requery".

so much for thinking even the most basic of method's should work as expected.