Filter Subform attempt

[Trevor]

I am trying to filter a subform by useing the build critria arg( origonaly in access 95), I am using Access '03 and I am getting an error on setting my subform filter criteria in line 2, the error is "form does not support this proporty" I have checked my form and textbox names and they are correct, the hardcoded IDNumber = 11 is a test that I will later change to a variable
SzCrit = BuildCriteria("IDNumber", dbText, "11")
Forms![Su-IL]!Form1.Data.Filter = SzCrit

The purpose is to prevent the user from having to re enter info in the subform if there is another record with the info already in the subform.
Thanks for helping

[Tommy]

Maybe?

Forms![Su-IL].Data.Filter = SzCrit

It looks like you are using the form twice, it is untested.

[OBP]

Assuming that Form1 is the name of the Subform on Form Su-IL and that form is open you could try
me.Form1.Filter = SzCrit
You may also have to turn Filtrering on as well.

[Tommy]

:mkay
Subform Tommy, remember Subform read the whole post :bug: not just part of the post :doh:
</IMG></IMG></IMG>

:whip

[Trevor]

Tommy, thanks for your replies. you first reply would filter the form named su-IL but but my subform is named form1

[Trevor]

OBP, isn't filter automaticly turned on when I issue command formname.Filter = szcrit?

[OBP]

Trevor, I honestly can't remember, but it should be.

[Trevor]

Ok here what is going on, I have

SzCrit = BuildCriteria("IDNumber", dbText, "11")
Me.filter = SZCrit ' on my subform named form1

but when the form opens it isn't filting its just listing all entries in the subforms table and in the order it appear in the table
I even set up a msgbox szcrit to see what the value is and it msgbox = IDnumber = "11"

[Trevor]

I have also tried on my subform onload event

Dim ftr as string
ftr = "SELECT * FROM [Su-ILT-Sub] WHERE [Su-ILT-Sub].IDNumber= '11'"
Me.Filter = ftr

and I get you can't assign a value to this object error on me.Filter = ftr, I have even tried me.Filteron = ftr with the same results

[Trevor]

ok 1 step closer, logicaly you may think filter is on by default just by issuing Me.Filter = x but no you must set Me.Filteron = true
and you cannot set a subformfilter to on from another form, for som reason it doesn't like that, now I'm working with how can I get to to fuction how I wont to function, will post my results or further questions, thanks Obp and Tommy for helping.

[Trevor]

I Am having trouble filtering with a variable


Private Sub Form_Load()
Dim IDN As String
Dim fter As String
'Me.FilterOn = True
If Forms![form2].Checkfltr = True Then

StrFilter = BuildCriteria(IDNumber, dbText, Forms!form2!IDNumber)
Me.Filter = StrFilter
MsgBox StrFilter
End If
End Sub

I have strfilter filtering on an IDNumber from form2 IDNumber(field)
And the checkbox is set (checkfilter) just to activate the filter
and I keep getting on my msgbox that should messg what the value it's filtering for is supose to be the value in IDNuber on form2 but when it displays mesgbox I get 11 = (whatever value is in IDNumber on form2
I have checked my filter proporties on Su-IL (the subform) and it is blank) so Im going crazy trying to track this problem down
there is an IDNumber "11" in a table entry, so I have an Idea where its getting that number from.

[Trevor]

Solved, finaly, code is on load of subform, the checkbox is on the form w/cmd button that tiggers the filter

If Forms![Form2].Checkfltr = True Then
Me.FilterOn = True
StrFilter = BuildCriteria(IDNumber.Name, dbText, Forms!Form2!IDNumber.Value)
Me.Filter = StrFilter

[OBP]

Trevor, well done in getting there. :)
Can I offer you an alternative method to explore?
You can use the Criteria Row of the Subform's Query to refer to the Mainform and filter the data before it gets to the subform. The only VBA required is to requery the subform when you make a change in the "trigger" on the mainform, which can be a field value, a combo, list or Frame selection.

[Trevor]

Thanks OBP,
I juht tried googling critriarow and I'm not finding anything that really explains, but I was looking to see if its just an alternate way of acheivveing the same goal as I did w/ buildcriteria or what adventange or disadvantgeses there are.

[OBP]

Trevor, there are a couple of minor advantages, first of all you do not have to worry about VBA Syntax for matching the Filter to the field, second, it is easy to have more than one filter.
You can easily filter the subform to, for instance, a Combo selection or a date or date Range.
The Form based Criteria row entry is also very useful for searching.