Solved

Problem with dynamic filtering

Posted on 2009-07-13
12
334 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
ID: 24842856
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
ID: 24842934
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
ID: 24857269
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:drschank
ID: 24879874
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
ID: 24880183
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
ID: 24880614
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24881572
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
ID: 24881744
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
ID: 24882514
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
ID: 24882923
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
ID: 31602918
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
ID: 24919368
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

816 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

13 Experts available now in Live!

Get 1:1 Help Now