Problem with dynamic filtering

I have a problem that doesn't make much sense and I'm hoping someone can help!

I have a data VIEW form with a subform.  Master form is Complaints, child form is Products.  When I put in a temporary filter (filter by selection button) on a field, it filters the records appropriately on the Master form.  However, the child form locks in to the first record of the filter, and stays there as you page through the different records.  When you unclick the Filter by Selection, the master form returns to full records, but the products subform still shows the one record.  

I am looking for any suggestions of things to check into, or any help at all.  This issue doesn't really make sense to me.  I can't address this issue in VBA, I don't think, because the field that the Filter will be applied in will vary each time between about 30 different possible fields.  

Any help or suggestions will be greatly appreciated!

Thanks in advance,
Who is Participating?
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.

You should be able to synchronize the subform with the main form by setting the master and child links in the subform control on the main form.  See this link:
P'S: the master link will usually reference a control on the main form , not the underlying fields. Examples  of controls are textboxes, comboboxes, listboxes, etc.
Sounds like you need to put a "me.YourSubform.requery " in somewhere.
You could try putting this code in the ON CURRENT event of the parent form.  No matter what happens, as soon as you access any record - the subform will be forced to requery itself.

Scott C
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

drschankAuthor Commented:
Thanks for help experts.  I am sorry just now be responding.  I have been trying to work through the suggestions all week and haven't been very productive!

I inserted the requery code, to no avail.  Same result I've been having.  I am looking more into the setup I have.  I set up the subform at first with the subform wizard, but now looking into the details I can't seem to find a Master/Child link setup anywhere.  The subform is linked to the main form by the field name ComplaintNumber, which is how the underlying tables are linked, but nowhere can I find where to set the Master/Child links on either the Master form or Subform.   It would seem this would relate to my problem, but I don't know what else to do.  I spent this week trying to find more information before I responded but this is where I'm at.

Thanks again for the help, I hope you can still work with me on this.

To see the master/child links, place the main form in design view, and click on the border between the main form and subform.

See this for a tutorial to help you.
drschankAuthor Commented:
Ok, got it.  They were set correctly as I expected them to be.  Still having the same issue.  Any other suggestions for me?  It just really doesn't make sense!

You need to post what you have for the master and child links. In order to have the dynamic filtering that you want, the master should reference the textbox (or other) control on the main form that corresponds to the primary key field for the subform (the child link).  This assumes you are using bound controls.  If the textbox currently does not exist on the main form, you need to add it (you ca hide it if desired).

Example >>>master link>>>>txtID
              >>>Child link>>>>>>ID >>>where ID is te primary key of the subform's underlying table

drschankAuthor Commented:
Ok, now we're on to something.  This is not how I have it set up.  This is my current link setup:

MasterLink: ControlComplaintNumber (textbox whose recordsource is ComplaintNumber,  which is the primary key for Complaint table)

ChildLink: ComplaintNumber (this is the link that binds the Complaint table and the Products table (Products table being the basis of the Products subform that I am working with) But it is NOT the primary key for the Products table, as this is a one-many relationship with the Complaint table.

The Primary key for the Products table is ProductLineKey, which is of course an autonumber field.

I see this is not consistent with the information you just gave, so at least we know where the problem is.  I don't see any way to create a control on the main form that relates to the Products primary key.  I'd be happy to send a copy of my db front & back end but it has some customer information in it already so I would prefer to send directly to you if that would help explain what I am working with.

Thanks again
Let's persist as long as we are making progress.....if progress is not being made then the file upload can be pursued.........anyway, unless you have an MS Access version 2000 compatible file, I won't be able to do anything with it.

Let's see if the hidden textboxes trick (1 hidden textbox on Main/1 hidden textbox on Subform)  can be implemented in your case.  Can you simulate a unique key on the subform by concatenating two  or more live fields on the subform?  The fields have to be in your record sources for the main and subform.
Example: subform>>>txtChildLink = [ComplaintNumber] & [Product]  
                Main form>>txtMasterLink =  [ComplaintNumber] & [Product]            
The idea comes from the following tip from
Improve Subform Performance

Base subforms on queries rather than tables. Include only required fields from the record source.
Index all the fields on the subform that are linked to the main form. Indexes speed up the matching of subform records.
Index any fields used for criteria such as where a subform is based on a parameter query.
If you are linking on multiple fields, add a calculated field to the main form that concatenates the fields. Then, create a calculated column in the subform's RecordSource property query with the same expression.

For example, to link to the subform on an Employee ID field and an Order ID field, add a text box to the main form with the following properties:

Name: EmployeeIDOrderID
ControlSource: =[EmployeeID] & [OrderID]

Next, add the following field to the query that the subform is based on:

EmployeeIDOrderID: [Employee ID] & [Order ID]

Then, link the main form and the subform on the concatenated field rather than on the two individual fields. The subform properties might look as follows:

LinkChildFields: EmployeeIDOrderID
LinkMasterFields: EmployeeIDOrderID

Because Access only has to compare one criteria to return the subform's recordset, the subform's performance should improve.
Set the subform's DefaultEditing property to Read-Only if the records in the subform are not going to be edited.
If your subform is a continuous form and contains combo boxes, explicitly justify the combo box in the subform's form Design view. This prevents Access from determining the proper justification of the combo box values for each record and thus speeds the display of subform records which have combo boxes.


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
PS: If, for example, you can't obtain the product line for your concatenated main form link until after the "filter by selection" button is clicked, you should be able to capture it in the filter button click event, and then use it to create the txtMasterLink.

drschankAuthor Commented:
Well, the answer became easier than expected.  I reworked the subform to base off of a query instead of the table directly, and it works correctly now.  I didn't even have to get into concatenating fields.   I am not 100% sure why it wouldn't work from the table directly, but it does now so I am grateful.

Thank you very much for all your help.  I appreciate your time, I am sorry it took me so long to respond back after so much help!


Glad I was able to help you resolve your problem.  Thanks for the points and grade.  Basing your subform record source on a query (Step #1 on aadconsulting's list) usually helps because it enables you to include fields from more than one table in the record source.  Often the field needed is the field to create the link to the master. So I assume that is what happened  in your case....... the field you needed for link was in a different table then the table you used.    
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.