asked on

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,
Robert

puppydogbuddy

Robert,
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:
http://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp

puppydogbuddy

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.

clarkscott

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

drschank

ASKER

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.

Robert

puppydogbuddy

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.
http://www.jegsworks.com/Lessons/databases/formsreports/step-subforms.htm

drschank

ASKER

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!

Thanks

puppydogbuddy

Robert,
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

drschank

ASKER

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

ASKER CERTIFIED SOLUTION

puppydogbuddy

membership

This solution is only available to members.

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

Start Free Trial

puppydogbuddy

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.

drschank

ASKER

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!

Thanks,

Robert

puppydogbuddy

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.