Solved

Access 2003 - Filtering a subform with a second sync'd subform

Posted on 2009-05-06
41
398 Views
Last Modified: 2012-05-06
Hi Experts,

I recently created an unbound form with two subforms ( a master sub, and a detail sub).  I added an unbound textbox to the main form and set it to equal a field in the master subform so it could be used to sync the detail form.  That is working fine.  What I want to do now is give the user the opportunity to filter the master sub based on some criteria.  I initially used a combo box and linked it the same way I did the two sub forms(using Master and Child link properties of the master form), but then the Master form is ALWAYS limited to what is in the combo box.  I would like it to be filtered when the user wants it to be filtered, and also be able to see the form totally unfiltered.  I have no problem using VBA if that is necessary.  What is the best way to accomplish this?
0
Comment
Question by:Hubbsjp21
  • 21
  • 20
41 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24317600
Hi Hubbs,
If I understand you correctly, I would suggest that you keep the combobox on the main form to filter the master sub, but have the first row of the combobox contain "All" (corresponding to unfiltered), as a selection item. What do you think?
0
 

Author Comment

by:Hubbsjp21
ID: 24317872
PDB,

Wouldn't the value "All" in the linked field (Combo box with value "all" as master, linked field in subform child) only find the records with the value "all" in the subform?  Or were you thinking of something else?

Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24318319
If the "All"  is set up correctly (see below) it should work.  it should pass the wildcard default value to the child link. If it doesn't work, I would look into why and fix it, or give you another way to approach it.
1. Set the default value of your combobox to >>>>>>   "*"
2. Then, To add (All) as the first item in the droplist of an unbound combobox, replace the RowSource property of the combobox with the following SQL string, replacng the illustrative object names with their names in your application:

SELECT YourField FROM YourTable
UNION SELECT "(All)" FROM YourTable;

simple filled-in example
SELECT City FROM tblRegions
UNION SELECT "(All)" FROM tblRegions;
0
 

Author Comment

by:Hubbsjp21
ID: 24318380
Okay - I with play with that.  I am also trying a search box I have used before in another application.

I'll let you know if I get stuck.

Thanks,

Hubbs
0
 

Author Comment

by:Hubbsjp21
ID: 24327025
Hey PDB,

I had a chance to work with your suggestion.  The "(All)" portion is not working in that when "All" is selected from the combo box, the form doesn't select any records.  Below is the SQL statement in the combo box.  The combo box in itself seems to be fine.  If I select an actual value, the subform populates accordingly.

SELECT DISTINCT tblServTransTtl.Loan_Num  FROM tblServTransTtl
UNION SELECT "(All)"  FROM tblServTransTtl
ORDER BY tblServTransTtl.Loan_Num;

Also, when the form opens, the default value "*" (w/o quotes in the combo box) does not select any records either.

Thanks - Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24327538
Don't worry...we will get it to work.  
1. Change your select statement to:
SELECT DISTINCT Loan_Num As Fllter, Loan_Num  FROM tblServTransTtl
UNION SELECT '*' As Filter, "(All)" As  Loan_Num FROM tblServTransTtl
ORDER BY tblServTransTtl.Loan_Num;

2. use single quote for default '*'

Tell me what happens......
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24327566
typo misspelled Filter
SELECT DISTINCT Loan_Num As Filter, Loan_Num  FROM tblServTransTtl
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24327581
Forgot to tell you to hide first column 0 width
0
 

Author Comment

by:Hubbsjp21
ID: 24327692
Yah . . no worries.

I made the changes, and no change to the behavior, accept that the first line in the combo box is * instead of All.

Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24327790
Thanks for bearing with me...I have done it before, but I am still refreshing my memory.....
You need to make the first column(column(0)) the hidden column ....it is the column with the *
0
 

Author Comment

by:Hubbsjp21
ID: 24327842
Right.  I didn't see that comment until after I made the initial changes.  I have done that now, and still no changes.

FYI - I am not in a hurry on this because my other method works great.  I would however like to finish this out for the sake of having more arsenal, and because I want to award the points.

Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24330090
Hubbs,
OK ....just got back.  Thanks for your patience.  Let me know how this works. If there is no progress, I will need time to experiment with a live combo box instead of doing it from memory. Now I am curious and want to figure this out for myself how I made it work before!

1. change default value property of combo box to:
             YourComboboxName.ItemData(0)
2. Change your select statement to:
SELECT "*" As Filter, "(All)" As Loan_Num FROM tblServTransTtl
UNION SELECT DISTINCT Loan_Num As Filter, Loan_Num  FROM tblServTransTtl  WHERE Loan_Num  Like Forms!YourForm!YourCombobox
ORDER BY tblServTransTtl.Loan_Num;
0
 

Author Comment

by:Hubbsjp21
ID: 24335912
Yah . . . . I was thinking we will need to use "Like" as well.  I will try it.
0
 

Author Comment

by:Hubbsjp21
ID: 24336486
Does not work.  Nothing in the combo box now accept (All)

Thanks - Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24336583
Take out default value and tell me what happens.  Thanks.
0
 

Author Comment

by:Hubbsjp21
ID: 24337981
Sorry - no go.  Same result.  In addition, I had to take out the ORDER BY line because you switched the order of what was in the Select, and Union Select lines.

Thanks - Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24339087
Hubbs,
Ok,  I will put it back the order it was. This weekend I will take a live combobox and get it working and then get back to you by Monday at the latest. Once again, I appreciate your patience.  I have a combobox like yours working in response to a question I answered on one of the public forums, but I don't remember which one.  I would appreciate it if you could provide the following:
1. I s loan_num numeric or alphanumeric? can you list 3 rows from the combobox
2. what is the primary key of tblServTransTtl
3. Is loan_num a primary key in any of your tables?
0
 

Author Comment

by:Hubbsjp21
ID: 24339506
PDB,

Thanks for your continuing on with this;

1. I s loan_num numeric or alphanumeric?  

 NUMERIC

can you list 3 rows from the combobox
 
 18515486
 18515544
 18516047

2. what is the primary key of tblServTransTtl

 LOAN_NUM, TRAN_DATE

3. Is loan_num a primary key in any of your tables?

 YES

Thanks,

Hubbs (now gone for the weekend)

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24345404
Hubbs,
I finally found my original source from  2004 that I used to implement the query trick that we are trying to implement here.                  http://www.databasejournal.com/features/msaccess/article.php/10895_3444891_2/Fascinating-Query-Tricks.htm

If you read the article, we were pretty much on track, except that we should have referenced Column(0) As Filter instead of Loan_Num.  (we do not have to use MSysObjects table because we are using Filter as an alias for Loan_Num in our search table.  When we added  Loan_Num as Filter to show the value "All", we created a 2 column combobox that looks as follows
Col Index                   Column(0)                                 Column(1)
Column Name                Filter                                      Loan_Num  
Bound-Nonbound     Bound (Hidden)                     NonBound (Show)
Column Width               Width 0                                   Width 1"


1. change default value property of combo box back to:
            YourComboboxName.ItemData(0)  
2. Change your select statement to:
SELECT DISTINCT Loan_Num As Filter, Loan_Num  FROM tblServTransTtl  WHERE Filter Like Forms!YourForm!YourCombobox
UNION SELECT "*" As Filter, "(All)" As Loan_Num FROM tblServTransTtl
ORDER BY tblServTransTtl.Loan_Num;
0
 

Author Comment

by:Hubbsjp21
ID: 24357933
Hey PDB,

Hope you had a good weekend.

I think I have found the disconnect after reading the article on the "trick".  I have been trying to do this using the Master and Child link field properties of the sub form.  Here is the excerpt from the initial question:

   "What I want to do now is give the user the opportunity to filter the master sub based on some criteria.   I initially used a combo box and linked it the same way I did the two sub forms(using Master and Child link properties of the master form), but then the Master form is ALWAYS limited to what is in the combo box.  I would like it to be filtered when the user wants it to be filtered, and also be able to see the form totally unfiltered."

 With the way I have been doing it, the * hasn't worked becuase of the link fields.  Within the link, the link fields are trying to match all records in the subform with the value in the combo box .  If I choose one of  the Loan Num values, it works fine.  But if I choose "*" (All), it doesn't work because there are no records with the value *.  I am pretty sure LIKE can't be used in the link property (I could be wrong).  After having read the article, it appears he is using SQL to rewrite the underlying query of the subform, using the combo box value in a LIKE criteria. (I already know how to do that.)   From the article:

  "My demo report uses the selection by referencing it directly from the query that serves as its record source."

If you look at the article, there is no WHERE clause in the combo box SQL, whereas the SQL you have been giving me has a WHERE clause.  I could be wrong, but that is the way it appears to me.

I was hoping to find a way to use the link fields and be able grab all records, and I thought that was what we trying accomplish.  If I am way off here, please set me straight.  If I have not comunicated correctly about what I am trying to accomplish, I profusely apologize.

Thanks - Hubbs
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24358386
Hi Hubbs,
<<<<If you look at the article, there is no WHERE clause in the combo box SQL, whereas the SQL you have been giving me has a WHERE clause.  I could be wrong, but that is the way it appears to me.>>>>

You are not correct. See the excerpt below from the article.
                    WHERE Employees.EmplNo Like ([Forms]![frmMainDemo]![cboEmployee02])
So, the result here is that if a long integer EmplNo is passed, the WHERE clause evaluates to this:
                    WHERE Employees.EmplNo LIKE 1234
But if "--All Employees--" was selected then all records are returned with this criteria:
      WHERE Employees.EmplNo LIKE *
It's that simple.  One SQL statement.  One reference to the combo box.

The link fields are fine. The SQL where clause is what makes it dynamic so that you can use it for all or a specific loan-num.

If it is still not working, then there are still other possible causes as discussed below:
In access, when a form control is used to pass a parameter, the sql must identify it as a parameter within the where clause.....the syntax differs between a numeric and text string parameter.
Try both of the syntaxes below and let me know if there is any difference. I have been assuming that you know that you are to replace the names of the form and combobox obiects I used for illustrative purposes with their actual names in your application.  If this doesn't resolve the problem, please post your actual rowSource SQL statement so that I can look at it.

If the where clause is returning a text string, the where clause syntax should be as follows:
         "Select.................................WHERE Filter Like '" & Forms!YourForm!YourCombobox & "'"

If the where clause is returning a numeric string, the where clause syntax should be as follows:
         "Select.................................WHERE Filter Like " & Forms!YourForm!YourCombobox
0
 

Author Comment

by:Hubbsjp21
ID: 24358903
Hi PDB,

If I am incorrect, I have absolutely no problem with that, but before I try to decifer this any further, let me make sure I get on your page.

1)  Yes, I understand your code in referencing form names etc., so we are okay there.
2)  You have been telling me to change my SELECT statement along the way.  I assumed you were always referring to the select statement of the combo box . . . . am I right about that?
3)  The article refers to two different SQL statements from what I can tell.

   the first SQL statement was for the combo box:

  SELECT EmplNo, Employee FROM Employees
  UNION SELECT "*", "-- All Employees --"
  FROM MsysObjects ORDER BY Employee;

  I thought you had adjusted our version to this (most recent):

  SELECT DISTINCT Loan_Num As Filter, Loan_Num  FROM tblServTransTtl
  WHERE Filter Like Forms!YourForm!YourCombobox
  UNION SELECT "*" As Filter, "(All)" As Loan_Num FROM tblServTransTtl
  ORDER BY tblServTransTtl.Loan_Num;

  the second SQL statement in the article was for the report (subform) itself.  He says in the article
  before he gives  the SQL statement, "My demo report uses the selection by referencing it directly from
  the query that  serves as its record source."  The way I read that, is the "selection" is the choice made
  from the combo box.  It is referenced in the WHERE clause of the SQL statement he then gives
  next . . .

  SELECT Employees.Employee, Sales.ord_date, Sales.qty, Titles.title,
         Titles.price, [Price]*[Qty] AS Ext, Employees.EmplNo
  FROM (Employees INNER JOIN Sales ON Employees.EmplNo = Sales.EmplNo)
        INNER JOIN Titles ON Sales.title_id = Titles.title_id
  WHERE Employees.EmplNo Like ([Forms]![frmMainDemo]![cboEmployee02])
  ORDER BY Employees.Employee, Sales.ord_date;

  To me, that is why either * or a value would work, because it is used in the LIKE criteria.

4)  I am with you regarding the syntax difference between text and numeric.

5)  To this point, we have not discussed the rowsource SQL.  It was being filtered by the MASTER and CHILD link properties of the subform.  You can't do both, can you?  A WHERE clause in the underlying rowsource SQL statement, and the link fields????

If you want to scrap this at this point, that is fine with me.  Otherwise, tell me where I am confused about what we are doing.  I just feel bad that this has taken so long.

Hubbs



 



0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24359197
Hubbs,
Don't worry...... I won't give up until I resolve this!  Now consider that the whole point of the article is the author's query "trick" that enables him to use a single query to return the correct results for a "specific value" selection and for an "All" selection made from the combobox

Therefore, both sql statements that were discussed by the author of the article refer to the combobox.  He is using the  first one to show what the typical non-dynamic row source sql looks like and is using the second one to illustrate the dynamic sql statement that is the "key" to his trick of using a single sql statment to generate the correct results for both...a specific employe and all employees.

This part of the sql statement >>>>>>>Forms!YourForm!YourCombobox  is what I am referring to as illustrative object names.....you did not change it....you need to replace YourForm with your actual main form name, and replace YourCombobox with the actual name of your combobox.  If you haven't been changing it .....then your sql statement could never work.

You can do both the link fields and the sql where.. the whole idea is that the master and subform will display the correct results (based on the links) for the value selected in the combobox.



0
 

Author Comment

by:Hubbsjp21
ID: 24359616
Hi PDB,

I have been changing them.  Maybe the best thing for me to do at this point is upload the database for you to see everything.  I think that would be best for you to know what is happening, then you can school me on what was wrong.  My next communication will have what you need.  Again, sorry for the confusion on my part.

Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24359679
Hubbs,
If you can upload your file in Access Version 2000 format, that would be great!  Now, I am really curious as to why the filtering isn't working for  you.....
0
 

Author Comment

by:Hubbsjp21
ID: 24360672
Hi PDB,

I wasn't sure how to save the file in a previous version; I am using 2003.  If you want to tell me how to do that, I would be happy to.  In the meantime, I have uploaded the 2003 version.

Hubbs
Copy-of-dbAssetMngt.mdb
0
 

Author Comment

by:Hubbsjp21
ID: 24360687
Forgot something - I had to make copies of the tables because they were linked tables in the original.  I forgot to give them Primary keys.  The two Trans tables are keyed on Loan_Num and Tran_Date.

My Bad.

Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24365831
Hubbs,
Just wanted to let you know I downloaded your file and can read it fine because it uses year 2000 format.  Will work on it as time permits and get back to you by Friday at the latest.
0
 

Author Comment

by:Hubbsjp21
ID: 24366149
Okay - Cool.  Looking forward to your discoveries.

BTW - In the same article we have been discussing, he shows a trick for giving a "Totals" line in a query.  As simple as it is, I never thought of doing that - very cool trick.

Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24368515
Hi Hubbs,
Before working with your db, I took a preliminary look at  your table structure to set up the primary keys and relationships between the tables.  The tables as they are in the db you sent me seem to contradict your description of them in your various posts.  For example, in tblLoansHeld, it looks like the Primary Key is Serv_Loan_Num (DataType>>>>Number) and not Loan_Num (DataType Text).

In tblServTrans, Loan_Num + {TranDate} is not unique and can't be used as a primary key.

The same thing applies to tblServTransTtl.   {Loan_Num} + {TranDate} is not unique and can't be used as a primary key.

What gives?  
                   
0
 

Author Comment

by:Hubbsjp21
ID: 24375064
Hi PDB,

you are right in that tblServTrans Loan_Num + {TranDate}  is not unique.  That table is not keyed.  I apologize for not looking closer before I sent the post.  That should not affect what we are trying to do though, because this table is just linked as one to many with the tblServTransTtl table.

tblServTransTtl.   {Loan_Num} + {TranDate} should be unique.  This table is created by doing a totals query with sum on the money fields, and a group by on the first 6 fields and the Tran_Date field using the tblServTrans table.  The first 6 fields are always the same for each unique Loan_Num field.  So by keying on Tran_date and Loan_Num, I get a unique value (in my database anyway).

I actually didn't give a primary key for Loans Held, but you're right, It is  Serv_Loan_Num .  (my bad)

Sorry for that.  That is why I don't like paring down a DB for the sake of example.  I often lose track of what I have done.  My sincere apologies.  Again, no hurry on this.  Though I know this is eating at you as it is me. :)

I have attached the same file with the primary key set on tblLoans_Held, and tblServTransTtl.

Again - sorry.  I don't usually make this many mistakes

Hubbs


Copy-of-dbAssetMngt.mdb
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24375402
Hi Hubbs,
No problem.  I just wanted confirmation from you that I was reading your db correctly.  Just out of curiousity, did you notice any difference using serv_loan_num instead of loan_num in the row source sql ?  I will try to start on the program sometime this afternoon or late evening.  
0
 

Author Comment

by:Hubbsjp21
ID: 24375686
The values are the same, but in some cases it is a text field instead of a number field.  It is an issue because our loan servicer uses numeric fields for their loan number, and we use text for our loan number (we are the loan originator).  The Loan_Num field is the servicer's loan number, so it should be numeric.  Over the years, I have had to create different versions of the same field using different names.  Serv_Loan_Num should be the same value as Loan_Num.  A single loan can have as many as 5 loan numbers depending on how many times the loan has been sold or changed servicers.

Thanks - Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24412660
Hi Hubbs,
I converted the form from being "property sheet" based to being "code" based via event procs.  I have the "all" option from the combobox working, but as all the loans are displayed, the ldb(lock file) gets corrupted...I am investigating (I think it may have somthing to with the join) and will need at least one more day.
0
 

Author Comment

by:Hubbsjp21
ID: 24413068
PDB,

No worries.  In your own time.

Hubbs
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 24473586
Hubbs,
Sorry this has taken so long, but as previously discussed, each generally accepted method I used caused the Access ldb (lock) file to freeze and lock me out of the mdb file&&requiring me to reboot the system so that I could erase the lock file and enable me to proceed with coding changes until I tried to run the application&.after which it would lock me out from making changes, starting the cycle all over again. After much investigation and research, I figured out that I needed to clear the records on the existing screen before populating the screen with new ones.  However, I think the following also could cause locking and related problems as well:
1. the fact that I am using Access 2000 to work on a db file created under Access 2003, or
2. the affect of the name autocorrect bug  http://allenbrowne.com/bug-03.html
I did not realize that you had name autocorrect turned on until recently.

I coded 3 different methods of layering "All" (as part of a combobox list) to display all records on the form, without adversely affecting the already existing functionality, which displayed all records for a specific loan agreement #.   Of the 3 methods listed below, the one that seemed to work the best was the Reset the subform record source method (#1).
1. Dynamically reset the subform record source.
2. Dynamically reset the master/child links.
3. Passing a filter string to the subform. The passed string is built based on the selection from the combobox.

 your file is attached, updated to demo the record source method. If you have any questions or problems, let me know.  

For the record, here are the steps I followed:
1.      Went to the relationship window and set up two <<1 to many>> relationships between the master and detail tables with referential integrity enforced.
2.      created a saved query (qrySbfrmMaster) that simplified construction of the sql statements.>>>>>>>>>>Select * From qrySbfrmMaster Where&&&.)
3.      coded a form open event to set up the combobox with "All" included in the selection list.
4.      coded a combobox Click event to display the item selected.    

V5dbAssetMgmt.zip
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24528359
Hubbs,
What happened?  Have not gotten any response from you regarding the updated file I posted. Unless you have any further questions or problems, please close out the question.  Thanks.

PDB
0
 

Author Comment

by:Hubbsjp21
ID: 24528426
I got slammed at work, and home (teenagers), and have not had time to even look at it.  I apologize.  I am not being aloof, I promise.  I hope to get to it this week.  Sorry it is taking so long.

Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24529085
No worries!  Just wanted to confirm that you were aware that there was an update waiting for you. Every now and then, things get lost in the EE-mail.
0
 

Author Closing Comment

by:Hubbsjp21
ID: 31578482
PDB - I apologize for how long it has taken me to get back to you on this.  You are to be commended for staying with this as long as you did.  I tested what you gave me, and it does what I wanted it to do.  If I choose to make tweeks to it, and can't get it to work, I will ask a new question.

Thanks a milliion - Hubbs
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24591733
Hubbs,
Glad it's working to your satisfaction.  The locking problem was a real challenge to solve and turned out to be an unexpected, but good learning experience for me.  Thanks for the points and grade.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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