Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Filter Multiple Subforms

Posted on 2008-06-10
13
Medium Priority
?
199 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 34

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 34

Accepted Solution

by:
Mike Eghtebas earned 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 34

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 34

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 34

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

877 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