Solved

Filter Multiple Subforms

Posted on 2008-06-10
13
185 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now