Hi, Gabor.
The filter buttons on the toolbar are disabled, so I cannot do anything but
close the form.
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.
