Re: How to Filter subforms contained in TABS

[Rich P]

Maybe you could try using a query as the recordsource for your subforms
and for the criteria field in the query you could use a Form parameter.
This way - instead of using the form filter property you could set a
value in a textbox on your mainform which would filter the query. This
way all of your subforms are synchronized.

fieldname: fldx
Criteria: Like Forms!Form1!Text0

Text0 would be a textbox on your main form (Form1 or whatever it is
called) if the main form is called mainform then

Forms!mainForm!TextBox1

Note: that I am using the 'Like' keyword in the Criteria. This is if
your criteria field is a text field. Then you can use wildcards like
j*. This would retrieve records where the criteria field starts with a
j.

Then add a button to the mainform to force updates when you change the
criteria. In the code section for the button add this code:

Private Sub Command1_Click()
Me.Requery
Me.Refresh
End Sub

This will work - I just tried it on my machine (Access2003), but you may
have to recreate the forms and the query a few times to get it straight.
Rich

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Linq Adams via AccessMonster.com]

I think you're over engineering this. If you're saying that all three
subforms are based on the same table, and you're using subforms because your
form was too crowded for a standard interface, you don't need to use subforms!
Use the tabbed pages, but simply place the fields from the table on the
tabbed pages, twenty to a page, not thru subforms. Then anything you do on
one page, like filtering on a field, will be reflected in all pages.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200809/1

[VinceRuso]

Hi Linq, yes, I tried this and it does work, HOWEVER, the issue I have
is that the sub form has to be a continuous form, so when I do it this
way, the tabs get repeated on each row of data, and so do all the
headers for the fields. It does work but it looks messy....
So I use the subforms mainly for the formatting, looks cleaner.

thanks
vince
On Sep 9, 1:53*pm, "Linq Adams via AccessMonster.com" <u28780@uwe>
wrote:

I think you're over engineering this. If you're saying that all three
subforms are based on thesametable, and you're using subforms because your
form was too crowded for a standard interface, you don't need to use subforms!
Use the tabbed pages, but simply place the fields from the table on the
tabbed pages, twenty to a page, not thru subforms. Then anything you do on
one page, like filtering on a field, will be reflected in all pages.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2008...

[VinceRuso]

On Sep 9, 11:28*am, Rich P <[e-mail]:

Maybe you could try using a query as the recordsource for your subforms
and for the criteria field in the query you could use a Form parameter.
This way - instead of using the formfilterproperty you could set a
value in a textbox on your mainform which wouldfilterthe query. *This
way all of your subforms are synchronized.

fieldname: fldx
Criteria: * *Like Forms!Form1!Text0

Text0 would be a textbox on your main form (Form1 or whatever it is
called) if the main form is called mainform then

Forms!mainForm!TextBox1

Note: *that I am using the 'Like' keyword in the Criteria. *This is if
your criteria field is a text field. *Then you can use wildcards like
j*. *This would retrieve records where the criteria field starts with a
j.

Then add a button to the mainform to force updates when you change the
criteria. *In the code section for the button add this code:

Private Sub Command1_Click()
* Me.Requery
* Me.Refresh
End Sub

This will work - I just tried it on my machine (Access2003), but you may
have to recreate the forms and the query a few times to get it straight.

Rich

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks Rich, but your method would work nice if it were only one field
that the user is filtering on. In my case ALL fields can potentially
be filtered on. That is why I use the right click method instead of
trying to create my own filter selection criteria with text boxes

[Rich P]

Hello again,

It sounds like what you need is a dynamic subform where you only display
the fields you need to see at a given time. Here is how you create a
dynamic subform:

In a single subform add 60 unbound textbox controls. Name them txt0,
txt1, txt2, ... txt59 (not 1 through 60 because ordinal counting starts
at 0 -- For i = 0 to 59)

In the main form you can have a listbox which displays the entire list
of fields in this table. The user selects the fields to be displayed.
In another listbox list all the fields. These will be the fields to be
used as criteria. You can then have 5 or 10 hidden textbox controls on
the main form (or how ever many textboxes for the max number of
criterias you would have in a Where clause). When the users selects
fields to be queried you unhide a textbox for each field selected to be
queried on. From here you build your sql string based on the fields
selected to be displayed and the fields selected as criteria. Then you
set the Subform's record source to this sql query string. Then you bind
the number of textboxes in the subform to the number/name of the fields
selected to be displayed.

Here is a sample of what the code looks like - In my sample I use 2
command buttons (just a simple example). In the first command button I
set the recordsource of the subform and the controlsource of 2 textboxes
to 2 fields from a table called tbl2. In the 2nd command button I set
the recordsource and controlsource of 2 more fields (actually the first
field is the same, but the 2nd field is different).

Private Sub cmd0_Click()
Me.frmMain2Sub.Form.RecordSource = "Select * from tbl2"
Me.frmMain2Sub!Txt0.ControlSource = "RecID"
Me.frmMain2Sub!txt1.ControlSource = "FName"
Me.Requery
Me.Refresh
End Sub

Private Sub cmd1_Click()
Me.frmMain2Sub.Form.RecordSource = "Select * from tbl2"
Me.frmMain2Sub!Txt0.ControlSource = "RecID"
Me.frmMain2Sub!txt1.ControlSource = "LName"
Me.Requery
Me.Refresh
End Sub

In your case - instead of hardcoding fieldnames - you would just select
the fields the user selected from the Listboxes - use a For Loop (thus
Ordinal numbering). Anyway, this is how you would build a dynamic
subform.

Rich

Rich

*** Sent via Developersdex http://www.developersdex.com ***