Filtering a form & it's subform from a separate form

  • Thread starter Thread starter Jayyde
  • Start date Start date
J

Jayyde

Was that title complicated enough? ;) Basic scenario: I have a form
with assorted combo boxes, text boxes, and check boxes for the user to
select search criteria. I have a main form that opens off the main
filter that this creates, (a product master form if you will) then a
sub form on that with the specific products within that master. Kind
of like Blue shirts -> v-neck, crew-cut, etc (not exactly what I'm
doing, but the idea is similar). Now, I have the filter for the main
form working fine, now I'm trying to set up a filter for the subform.
Right now I can create the filter string and it's saved into a global
module, so I can pull it up in the main form just fine (into
strPFilter). I cannot for the life of me get it to actually filter
that sub form though. This is what I'm trying, which is obviously not
working:

frmProductChild.Application.DoCmd.ApplyFilter , strPFilter

Any help you Wizs out there can give me would be much appreciated =)

-Jayyde
 
you have to refer to the subform in the context of the main form. running
the code from a *third* form, try the following syntax, as

With Forms!MainForm!SubformControlName.Form
.Filter = "a string like a WHERE clause without the key word"
.FilterOn = True
End With

note that you need to use the name of the subform *control* within the main
form, which may be different than the name of the subform object in the
database window.

hth
 
I got the filter working, although I did it a different way than that.
If I switched it to your way will the main form automatically do an
inner join with the sub-form's records. That's the next thing that's
giving me problems.

I'm trying to do something like:

Dim strSQL As String
strSQL = "SELECT * FROM dbo_tblProductMaster " _
& "INNER JOIN dbo_tblProduct " _
& "ON
dbo_tblProductMaster.iProductMasterId=dbo_tblProduct.iProductMasterId "
_
& gblProcs.GetProductFilterSQL()

Me.RecordSource = strSQL
Me.FilterOn = True

In the main form's open(). The sub form's already filtered at this
point. I'm just trying to narrow down the main to only show the
records in it that have matching children. For some reason when I run
it with this code in, it loses the info from some of the main's fields
(like the PK and assorted random others so I get a bunch--but not
all--of #NAMEs) and none of the sub form's records come thru. With
this code out, the sub form filters fine, but all 21k records in the
main are returned =\.
 
in a mainform/subform setup, you should *not* need to manipulate the
RecordSource of either form in order to get related records only, in the
subform. first, make sure the two tables in question have their relationship
set up correctly in the Relationships window. the relationship must show as
one-to-many or one-to-one (whichever is correct), *not* indeterminate.
second, in the mainform design view, click on the subform to select it. in
the Properties box, make sure that the LinkMasterFields property shows the
primary key field(s) of the mainform's table; make sure the LinkChildFields
property shows the related *foreign* key field(s) in the subform's table.

now the subform will always display only those records which are related to
the current mainform record. you can filter the main form records as needed.
you can also filter the subform records as needed, which will return a
subset of the related records.

hth
 
Here's your next curveball then ;) ...these are linked tables (linked
to our SQL 2k server). So I can put a relationship there, but it
doens't auto-pull the type (one -> many). I think this may what's
complicating everything, but that's not something I can change. I'm
basically having to force everything. Also, the problem isn't getting
the subform to only show records that relate to the parent, it's
getting specific parent records to not show if they have no relating
records in the subform.
 
I can put a relationship there, but it
doens't auto-pull the type (one -> many).
Click to expand...

i've never used Access to "front" a SQL Server database, but AFAIK, the
above shouldn't matter. if the data is related correctly and explicitly, the
form/subform should work fine. the reason i cautioned against the
"indeterminate" relationship is that it means that Access can't figure out
what the correct relationship is between two native tables. when that's the
case, it can't enforce referential integrity; which is necessary for the
links to return the correct data consistently. if you're handling
relationships appropriately in the backend db, you should be fine.
Also, the problem isn't getting
the subform to only show records that relate to the parent, it's
getting specific parent records to not show if they have no relating
records in the subform.
Click to expand...

okay. your first post said you were having problems filtering the subform,
so that's what i assumed the question was. in filtering records for the main
form as you describe above, you need to forget about the forms entirely for
a moment. your real task is to filter the parent *table* to return only
those records which have a related record in the child *table*. to do that,
write a query that links the parent and child tables with an INNER JOIN on
the primary key/foreign key fields.

hth
 
Then we're kinda back to what I posted earlier this morning, which was
the INNER JOIN =\. I went back and checked, btw, and it is pulling the
one -> many relationship between the 2 tables OK, I just can't check
any of the checkboxes on the relationship screen (enforce referential
integrity and cascading). It's also set to option 1 in the join type
(the inner join) so I'm either missing something really stupid and/or
just plain out of ideas...

Thanks for all the help so far btw too =).
 
well, it's not clear to me just how many options you want to give the user.
the available choices seem to be:

- pull all parent table records, with related child table records.
- pull all parent table records that match user-defined criteria, with
related child table records.
- pull only those parent table records that have related records in child
table, with related child table records.
- pull only those parent table records that have related records in child
table AND match user-defined criteria, with related child table records.

you can offer all four options to the user, or any combination of the four
that you want. in each case, you don't have to do anything to get the
related child table records, as long as the mainform/subform are linked
correctly by the LinkMasterFields and LinkChildFields properties, as noted
in my previous post.

i'd suggest the following:
to control whether the main form displays 1) all parent records, or 2) only
parent records that have related child records: change the form's
RecordSource property and, if necessary, Requery the form.
to control whether the main form displays 3) all the records returned by the
RecordSource, or 4) only the subset of records that match the user-defined
criteria: set the Filter property of the form, and make sure you "turn it
on", as

Me.Filter = "a string with criteria like a WHERE clause without the key
word"
Me.FilterOn = True

hth
 
Thanks for all the help tina xD. I figured it out earlier this morning
actually. As usual it had more to do with my stupidity than with my
code. What I was doing was fine it was just getting confused on the
binding when it tried to bind something that was in both tables...like
the PK/FK for the parent so I had to specify which field to return on
that inner join.

Thanks again though!
 
Back
Top