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