Filtering a Subfrom from a Form

[Matt Weyland]

I would post this on a thread started 12/3/03, but I fear
it may get missed.

I have a form with a subform. The subform lists all
records The subform has a couple of list boxes and a
couple of text boxes. I want to be able to filter the
subform based on the above listed boxes.

My syntax for builing the SQL string is as follows:
Dim ctl As Control

For Each ctl In Controls
If ctl.ControlType = acTextBox Or ctl.ControlType
= acComboBox Then
If Not (IsNull(ctl.Value) Or Trim(ctl.Value)
= "") Then
strSQL = strSQL & ctl.Tag & " like " &
addQuotes("*" & UCase(ctl.Value) & "*") & " AND "
End If
End If
Next ctl
'testing
If IsNull(strSQL) Then Exit Sub
strSQL = "(" & Left(strSQL, Len(strSQL) - 4) & ")" 'this
line is oly to remove the last addition of " AND" to the
string.



my goal is to try and pass the string to the subform as a
flter attribute using a public function called getSQL
where the strSQL is returned.

This doesnt work. It just says the subform is filtered but
the record count is still as if it were not filtered.

The kicker is if in the immediate window I call the
function getSQL and copy that string to the filter line on
the subform then reopen it it works just fine and is
filtering the way it is supposed to. Rather frustrating.

Any help you could assist with this woudl be greatly
appreciated, or if you would prefer I post on the
newsgroups just let me konw.

THanks

MW

mweyland at mnqio dot sdps dot org

 

[William Taylor]

you can set multiple field pairs in the child / master link field
definition. Then when you change the values in the main forms controls, the
subform data will change to reflect those changes.
open the form in design view and select the subform control, then open it's
properties box
now use the link child field and link master field to create the pairs with
the ... build button or select the pairs yourself. the syntax is
link child field fieldone;fieldtwo;fieldthree;fieldfour (field names in
subform)
link master field fieldone;fieldtwo;fieldthree;fieldfour (field names in
main form)

 

[Matt Weyland]

William,

thanks for the info. I tried this and this doesnt work.
These are unbound listboxes on the form. I tried
referencing the masterfields to the names fo the controls
that store the value on the main form, but this too did
not work.

Any other ideas?

-----Original Message-----
you can set multiple field pairs in the child / master link field
definition. Then when you change the values in the main forms controls, the
subform data will change to reflect those changes.
open the form in design view and select the subform control, then open it's
properties box
now use the link child field and link master field to create the pairs with
the ... build button or select the pairs yourself. the syntax is
link child field

fieldone;fieldtwo;fieldthree;fieldfour (field names in

 

[William Taylor]

Yes. You can create a filter ( a string value) using the values from the
unbound controls on the main form and then set the subforms filter property
to this string. after that you must us filteron = true for the subform.
HTH