I was assuming that you were using an unbound control, such as a textbox,
into which you would enter the string you wanted to filter on. However, it
seems that's not the case.
The difference between FieldName and ControlName is that FieldName is the
name of a field in the recordsource of the form; it will be the name of a
field in the table, or perhaps an alias if your form's recordsource is a
query, rather than a table. ControlName is the name of the control on the
form whose value you are wanting to use in the filter.
If you built your form by dragging fields into the form, the textbox
controls for those fields will, unless you have explicitly changed their
names, have the same name as the table/query fields to which they are bound.
If you're not doing anything fancy, it's not a problem; but if you want to
do anything that involves referring to fields and/or controls, it very often
is a problem. As here.
I assume that you have a textbox control named "status", and a field in your
table/query also named "status". If that's the case, I strongly suggest
that you rename the control to txtStatus. And then, if you change the
second line in the code I posted to:
strFilter = Me.Filter & " AND Status = '" & Me.txtStatus & "'"
(expanded for clarity with the single/double quote characters, that's
strFilter = Me.Filter & " AND Status = ' " & Me.txtStatus & " ' ")
When you do that, your button will filter the status field based on the
contents of that field for the current record.
If you want to filter for the specific string you mentioned in your reply,
you can use:
strFilter = Me.Filter & " AND Status = 'strt'"
HTH,
Rob
gg said:
Rob Parker said:
I don't understand what you mean by "messy in this case". And there
isn't a
way to do it without retaining the current filter, but it's easy to use
that - you simply refer to it via Me.Filter. So for your additional
filter
button, all you need is some code along these lines:
Dim strFilter As String
strFilter = Me.Filter & " AND FieldName = '" & Me.ControlName & "'"
Me.Filter = strFilter
Me.FilterOn = True
HTH,
Rob
I filter my form based on the contents of several combo boxes tied
together
with OR's. I would like to add a button to the form that allows the
operator to filter the filtered data based on the contents of still
another
control. The problem
is that this second filter (I used form.filter=etc). displays the whole
data
base (filtered), not just the remnants of the first filter. Is there a
way
around this without including the first filter in the code ,messy in
this
case..
I am using Access 2000. Thanks for any help.
Still a problem. to keep things simpler, I want my button to show only
those records wherein a control name status ="strt"
me.filter="[status]='strt'" works, but displays the whole data base
rather
than the filtered results of the previous filter action. Could you show
me
the code that combines this with me.filter to do this?
I have tried every variation that I can think of with the the code you
gave
me, but get error messages. Also, what is difference between a fieldname
and
controlname?
