Solved

Problem with dynamic filtering

Posted on 2009-07-13
12
314 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:drschank
  • 7
  • 4
12 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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.
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
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
0
 

Author Comment

by:drschank
Comment Utility
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
0
 

Author Comment

by:drschank
Comment Utility
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
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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

0
 

Author Comment

by:drschank
Comment Utility
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
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
Comment Utility
Robert,
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   www.aadconsulting.com
____________________________________________________________
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.

0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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.

 
0
 

Author Closing Comment

by:drschank
Comment Utility
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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.    
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now