Link to home
Start Free TrialLog in
Avatar of repco
repco

asked on

Filter Multiple Subforms

Hello,

I have 2 subforms in which they are table driven. reason why is because these 2 subforms control a check box, [Complete] ,that modifies the table 0 or -1. i have create 2 command buttons for each sub form, one is called a Show all, in which it shows all records that have a [complete] = 0 and show active in which it shows records where [complete] = -1. i have [complete] = 0 on the Filter box in the form properties.

Show All code i have is
me.filteron = true
Show Active code i have is
me.filteron = false

on form load for each subform i have the me.filteron = false since by default i do not want to show all records.

the forms work fine when i use them individually, but when i want to put the forms together cascade in a new form, the buttons don't seem to work. once in a while one of the subforms works but then the other subform doesn't work. its intermident. any ideas why?

thanks!!!
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

For your consideration:

In place of the check box and the button, have option group called optStatus

-Status-----------
  o Completed   | <-- optStatus
  o Pending       |
  o Boht             |
--------------------

In the recordsource of both subforms, under [Completed] field have:

IIF(fnStatus()="<All>", [Completed], fnStatus())

In a module have:

Function fnStatus() As Variant
On Error GoTo 10
   Dim strTemp as String

   intTemp NZ(Forms!MainForm!optStatus,"<All>"_

   'remove this msgbox after a test to make sure
   'it is able to read the value of optStatus
   MsgBox intTemp
 
   Select Case strTemp
      Case "1": fnStatus = True
      Case "1": fnStatus = False
      Case "1": fnStatus ="<All>"
  End Select

Exit Function
10:
   fnStatus="<All>"
End Function

And, in the after update event of optStatus have:

Me!Subform1.Requery  ' or Me!Subform1.Form.Requery
Me!Subform2.Requery  ' or Me!Subform2.Form.Requery

Mike
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of repco
repco

ASKER

hi thank you for your quick response. i have a few questions though...

when you say in the record source under [completed], that means i must create a query for this? i can't create a query since i need to modify the table with the check box as the activity was completed or not.

i basically have a check box where the user can check to see if that activity was completed.

how can i put this code into this type of scenario?
Avatar of repco

ASKER

also where would i put the optStatus? on the detail or footer of the subform? or on the main form itself?
Sorry for the delay:

re:> where would i put the optStatus?

On the main form where the subforms are also located.

re:> the record source under [completed], that means i must create a query for this?

The fact you are being to filter your subforms for completed/ not completed, it means you have a field called Status or completed (or something else that you are filtering). Using this method, I bet lots of exixting code you have will no longer of any need.

What is the SQL of records source for this subforms. If each have few, post few of them here. You no longer will need to have many of them.

Mike
Avatar of repco

ASKER

well i don't have any sql of record sources. all i'm doing is creating a form with an existing table linking to it and the completed checkbox drives the [complete] field to either 0 or -1. no queries or anything.  
In the design view of your subform either you have something like "Select...." or a table/query name.

If "Selec...." then copy and past it here.

If a table or query name, enter record source property on the property sheet and click on [...] whixh will appear as you enter into ths cell on the property sheet to invoke your record source query. Then add the fields you are displaying on your subform and exit the query save it WHEN you asked to do so. Now, you will see "Select ...." in the recordsource cell. copy and past it here.

If you click on the check box to insert record source SQL, what is your code for it?  If it is inserting just a table name, make a query of that table and then in the design view of this query, select View/SQL and then copy and paste it here.

Mike
correctio...

In the design view of your subform bring up property sheet and locate property called record source and in it either you have something like "Select...." or a table/query name

Avatar of repco

ASKER

ah i understand now. ok i went ahead and created a query under the record source. now i'm getting an error on the above code...

it highlights the  intTemp under the
 intTemp NZ(Forms!MainForm!optStatus,"<All>")
and states Compile error; sub or function not defined.

Avatar of repco

ASKER

also here's the sql code

SELECT IIf(fnstatus()="<All>",[complete],fnstatus()) AS Completed, NeedMethodTable.Complete, NeedMethodTable.[Record ID], NeedMethodTable.Invoice, NeedMethodTable.[Part Number], NeedMethodTable.Customer, NeedMethodTable.OrderDue, NeedMethodTable.QTY_REQ, NeedMethodTable.QOH, NeedMethodTable.Comment
FROM NeedMethodTable;
Avatar of repco

ASKER

sorry use this one instead for it not to create confusion on the complete completed part

SELECT IIf(fnstatus()="<All>",[complete],fnstatus()) AS Exp1, NeedMethodTable.[Record ID], NeedMethodTable.Invoice, NeedMethodTable.Complete, NeedMethodTable.[Part Number], NeedMethodTable.Customer, NeedMethodTable.OrderDue, NeedMethodTable.QTY_REQ, NeedMethodTable.QOH, NeedMethodTable.Comment
FROM NeedMethodTable;
Avatar of repco

ASKER

to be on the same page this is what i'm trying to do.
its basically like a To Do List.
there's a check box next to whatevr you need to complete.
i want these toggles to be able to show completed items, pending items, or all items.

right now as i'm workgin through this, i'm having problems getting the value of the option group so that the query can read to see if the option group is either true or false so it can show all the ones that have been completed (-1) or Pending (0)
Avatar of repco

ASKER

after working with it fora while i got it to work. i did not know how to name the option group to optStatus .works great!! thanks!!!