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!!!
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!!!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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?
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?
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
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
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
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
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
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!optStatu
and states Compile error; sub or function not defined.
it highlights the intTemp under the
intTemp NZ(Forms!MainForm!optStatu
and states Compile error; sub or function not defined.
ASKER
also here's the sql code
SELECT IIf(fnstatus()="<All>",[co
FROM NeedMethodTable;
SELECT IIf(fnstatus()="<All>",[co
FROM NeedMethodTable;
ASKER
sorry use this one instead for it not to create confusion on the complete completed part
SELECT IIf(fnstatus()="<All>",[co
FROM NeedMethodTable;
SELECT IIf(fnstatus()="<All>",[co
FROM NeedMethodTable;
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)
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)
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!!!
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!optStatu
'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