Solved

Filter Multiple Subforms

Posted on 2008-06-10
13
194 Views
Last Modified: 2013-11-28
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!!!
0
Comment
Question by:repco
  • 8
  • 5
13 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21754640
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
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 21754654
correction...

  Select Case strTemp
      Case "1": fnStatus = True
      Case "2": fnStatus = False
      Case "3": fnStatus ="<All>"
  End Select
0
 

Author Comment

by:repco
ID: 21755097
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?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:repco
ID: 21755106
also where would i put the optStatus? on the detail or footer of the subform? or on the main form itself?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21755230
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
0
 

Author Comment

by:repco
ID: 21755242
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.  
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21755473
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21755584
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

0
 

Author Comment

by:repco
ID: 21759607
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.

0
 

Author Comment

by:repco
ID: 21759691
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;
0
 

Author Comment

by:repco
ID: 21759817
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;
0
 

Author Comment

by:repco
ID: 21761955
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)
0
 

Author Comment

by:repco
ID: 21763118
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!!!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question