Using filter by form on a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I use Access 2003. The problem occusrs in mdb and adp as well.
I have an unbound main form and a bound subform in it. (I mean the main form
contains only unbound controls, but the subform has a recordsource and has
bound controls.)
When I press the filter by form button on the toolbar, the filter by form
screen appears in the subform. The controls on the main form are disabled by
Access.
Everithing is OK up to this point.
But when I set any criteria and push the filter button on the toolbar, the
subform remains in filter mode only the main form controls change back to
enabled.
The filter buttons on the toolbar are disabled, so I cannot do anything but
close the form.

I thank you very much for your help
Gábor
 
Hi, Gabor.
The filter buttons on the toolbar are disabled, so I cannot do anything but
close the form.
Click to expand...

It's a bug. In Access 2003 databases, one cannot use an unbound main form
with a subform and try to filter the subform. This feature is crippled in
Access 2003, but not in earlier versions, so maybe Microsoft will fix it in
the next service pack -- or perhaps the next version. However, there's a
kludge available. It's pretty silly and it wastes disk space, but it works.

1. Create a new table named tblFakeFK with the following fields:

ID, AutoNumber, primary key
TableName, Text

2. Save the table and then open it in Datasheet View and type a single
record in the TableName field:

MyTable

... where MyTable is the data source for your subform.

3. Add a new field to your table (the data source of your subform), name
this field FakeFK, and make it a Long data type.

4. Create a new query and paste the following into the SQL View pane:

UPDATE MyTable
SET FakeFK = 1;

Replace MyTable with the name of your table and then run the query. The
number should match the number in the AutoNumber field of the tblFakeFK
table, so if yours isn't 1, then replace 1 with the actual number in your
table.

5. Create a relationship between your table that you've just altered and
the tblFakeFK table, and set the FakeFK in your table to relate to the ID
field in the tblFakeFK table. Enforce referential integrity.

6. Alter your subform.

a. Add a new text box control for the new FakeFK field and set it to be
invisible.
b. Save the subform and close it.

7. Alter your main form.

a. Open your main form in Design View.
b. Set the Record Source to tblFakeFK.
c. Add a new text box control for the new ID field and set it to be
invisible.
d. On the subform control, set the LinkChildFields Property to FakeFK.
e. On the subform control, set the LinkMasterFields Property to ID.
f. Save your form and close it.

Filtering your subform from the main form will now work. If you have any
other unbound main form / bound subform combinations and want to use the
"Filter By Form" feature on the subform, then you can add a new record in the
tblFakeFK for each new table that you add the FakeFK to, and then use an
update query to set the FakeFK field in this new table to the same value in
the of ID field in the tblFakeFK table for that record.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thank You '69 Camaro!

It works, but I had to modify my code a little .
First I set the subform's underliing recordsource from code (the form load
event of the main form) and it was an SQL statement.
But when I applied the filter, a message appeared saying there is no table
or query with name: <my subform name>.

So I changed the code and, set the recodsource of my subform to a table, and
use additional link fields (linkchildfields and linkmasterfields).

In one word, it works now, thank you




„'69 Camaro†ezt írta:
 
Hi, Gabor.
First I set the subform's underliing recordsource from code (the form load
event of the main form) and it was an SQL statement.
But when I applied the filter, a message appeared saying there is no table
or query with name: <my subform name>.
Click to expand...

You're welcome. Thanks for the feedback. I hadn't attempted to use this
kludge with a subform's record source being set when it opened. You are
correct in that the LinkMasterFields and LinkChildFields Properties must be
assigned after the RecordSource Property is assigned in code, even if these
properties have already been assigned in the subform control's Property
dialog window, because any previous LinkMasterFields and LinkChildFields
Properties will become invalid as soon as the new RecordSource Property is
assigned.

However, I didn't have a problem with a query or SQL statement assigned as
the record source. Perhaps you had a typo when you first tested it? The
following code worked for me in the main form's OnLoad( ) event, where
subfrmCtrl is the name of the subform control, tblStuff is the name of the
table used as a record source, and qryStuff is the name of the query based
upon tblStuff:

Private Sub Form_Load()

On Error GoTo ErrHandler

Me!subfrmCtrl.Form.RecordSource = "qryStuff" ' Works.
'Me!subfrmCtrl.Form.RecordSource = "tblStuff" ' Works.
'Me!subfrmCtrl.Form.RecordSource = "SELECT * FROM tblStuff ORDER BY
SomeDate;" ' Works.
Me!subfrmCtrl.LinkMasterFields = "ID"
Me!subfrmCtrl.LinkChildFields = "FakeFK"

Exit Sub

ErrHandler:

MsgBox "Error in Form_Load( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
Back
Top