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

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?

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

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;

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

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

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......

typo misspelled Filter
SELECT DISTINCT Loan_Num As Filter, Loan_Num  FROM tblServTransTtl

Forgot to tell you to hide first column 0 width

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

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 *

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

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;

Yah . . . . I was thinking we will need to use "Like" as well.  I will try it.

Does not work.  Nothing in the combo box now accept (All)

Thanks - Hubbs

Take out default value and tell me what happens.  Thanks.

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

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?

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)

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;

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

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

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



 

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.

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

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.....

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

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

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.

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

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?  
                   

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

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.  

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

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.

PDB,

No worries.  In your own time.

Hubbs

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

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

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.

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

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.