Filtering Forms

[Iggsy]

Hello, Greetings from Australia! I was hoping someone out there might be able to help me.

I'm currently trying to develop a simple access database. I have two tables.

They are set up like.

TABLE 1
LEVEL1........LEVEL1_DES
L001 ..........MILLS
L002 ..........BLRS
L003 ..........PRO

TABLE 2
LEVEL1 .........LEVEL2 ..........LEVEL2_DES
L001 ............ASH ..............Ash Plant
L001 ............FILL ..............Filtration Plant
L002 ............FAN ..............Fan # 1
L002 ............VAC ..............Vac Pump #1
etc



Table1-Level1 is linked to Table2-Level1. Level1 is the primary key in Table 1 and level 2 is the primary key in Table 2. Each Table has a query and a form based on the query.

The forms are of the "Multiple Items" variety.

What I want to do is use a button on the first form to open and filter the results on the second form. Pretty simple stuff, or so I thought.

I have used the wizzard to set up the control for the button and followed all the steps. The problem is the buttons will open the form, however it will not filter the results.

I have been using an already developed database as guide, and if I make my queries and forms use the wizzard in that database, everything works fine, so I'm not sure what the problem is with my database/process.

Does anyone have any suggestions as to what I can check to isolate the problem?

Thanks!

[orange]

Not sure I'm following exactly what it is you're trying to do. But typically if you have
(generically speaking) 2 lists that are related, you have a box to select a value from the first box, and use that to show a possible set of values in a second box.
This subject is Cascading Combos. There are free videos here. Check them out and see if they apply. If not post back and someone will have some advice.
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

[Iggsy]

Hello Orange,

Thanks for the reply.

The process I'm trying to achieve is similar to cascading combos, but not the same. I want the selection I make via a button on my first form to open a second form and filter the results. The thing that is making me pull out my hair is if i use the database I have been using as a guide, and make the forms and queries based on the existing tables, what I want to do works perfectly. Its only when I use my own Database and Tables that it doesn't. My table structure is nearly the same- I'm sure its something simple, but I have no idea what.

I have attached a quick sample of what I'm trying to do. Hopefully this will make it clearer. Open Form1 and press one of the buttons. It "should" (if I can use the button wizard corrrectly) open the second form containing the filtered results.

Thanks again for any help.

[orange]

I'm looking at your mdb.

If I open form1 and click a command button -- what filter should be applied.

Suppose I open form 1 and click the command where Area level1 code is INV-BLR.
What exactly should be in Form 2? Please be specific.

[Iggsy]

If you clicked the command button where Area level1 code is INV-BLR, in the second form I would only want to see the records that have the Area level1 code INV-BLR.

For instance .

Click command button aligned with "Area level1 - INV-BLR" in Form 1.

Form 2 should open and display:

INV-BLR ......6342-ASH.......Ash
INV-BLR.......6342-EFF........Effluent
INV-BLR.......6342-FLC........Fuel
Etc.


or if I clicked the command button aligned with " Area Level 1 - INV-PRO" in Form 1

Form 2 should open and display:

INV-PRO ......6342-CLR........Juice Clarification
INV-PRO.......6342-EVA........Juice Concentration
INV-PRO.......6342-FIL..........Mud Filtration
Etc.

The filter applied depends on which button is pressed. Hope this makes sense.

[orange]

I have adjusted your database in a separate mdb in your zip.

When you open Form 1 and click a command button ( I added a caption)
Form 2 will open with the appropriate filter. I set the record source of form2 to use the value from Form 1.

Open form 2 in design mode, check the Record Source property of Form2 under the Data Tab. The change is made in the underlying query. The criteria has been applied

SELECT [TBL-FncLoc-Level2].Area_Level1_Code
, [TBL-FncLoc-Level2].FncLoc_Level2_Code
, [TBL-FncLoc-Level2].FncLoc_Level2_Description
FROM [TBL-FncLoc-Level2]
WHERE ((([TBL-FncLoc-Level2].Area_Level1_Code)=[Forms]![FRM-Area-Level1]![Area_Level1_Code]));

Good luck

[Iggsy]

Thanks very much!

That made it look easy.