Filter on subform using unbound text box

H

Hiro

Hello Forum Members:

I'd like to have some help on the following codes on the Access 2000:
On the Form Header, I have an unbound text box called FindWord, in which I
type some words to search. The code below works on the main form.
{code}
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.FindWord) Then
strWhere = strWhere & "([English] Like ""*" & Me.FindWord & "*"")
AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Nothing is specified.", vbInformation, "Nothing to show."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
{/code}
{code}
Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me.FilterOn = False
End Sub
{/code}

However, what I want is that I would like to accomplish the same thing on a
SubForm inside the main Form. So I made some changes as follows:
* Me.Filter = strWhere was changed to:
Me.Child19.Form.Filter = "[English]='" & Me.FindWord & "'"
* Me.FilterOn = True was changed to:
Me.Child19.Form.FilterOn = True

This modified code is working, but
1) when I search a word such as "tomato", the result used to be like
"tomato" and "tomatoes", but now I get only "tomato".

2) when I hit the reset button on the result page, it used to go back to the
original screen, but now it just resets (clears) the word in a search box
(called FindWord unbound text box).

Would you be able to help me accomplish the above 1) and 2) on a SubForm?

With best regards,
Hiro
 
S

Steve Schapel

Hiro,

As far as I can tell...

1) I think you need the 'Like' operator rather than '=', therefore change:
Me.Child19.Form.Filter = "[English]='" & Me.FindWord & "'"
.... to:
Me.Child19.Form.Filter = "[English] Like '*" & Me.FindWord & "*'"

2) Make a similar change in the Reset code as you made in the other.
In particular, change:
For Each ctl In Me.Section(acHeader).Controls
.... to:
For Each ctl In Me.Child19.Form.Section(acHeader).Controls
 
H

Hiro

Dear Steve:

Thank you for your help so fast.
I checked your suggestions and the {code}Me.Child19.Form.Filter = "[English]
Like '*" & Me.FindWord & "*'"{/code} puts the desired function back.
And for {code} For Each ctl In Me.Child19.Form.Section(acHeader).Controls
{/code} is not working. It does not reset (clears) the words on the result
page nor go back to the original screen.

Would you please advise me what other adjustments I need to make?

With best regards,
Hiro

Steve Schapel said:
Hiro,

As far as I can tell...

1) I think you need the 'Like' operator rather than '=', therefore change:
Me.Child19.Form.Filter = "[English]='" & Me.FindWord & "'"
.... to:
Me.Child19.Form.Filter = "[English] Like '*" & Me.FindWord & "*'"

2) Make a similar change in the Reset code as you made in the other.
In particular, change:
For Each ctl In Me.Section(acHeader).Controls
.... to:
For Each ctl In Me.Child19.Form.Section(acHeader).Controls

--
Steve Schapel, Microsoft Access MVP
Hello Forum Members:

I'd like to have some help on the following codes on the Access 2000:
On the Form Header, I have an unbound text box called FindWord, in which I
type some words to search. The code below works on the main form.
{code}
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.FindWord) Then
strWhere = strWhere & "([English] Like ""*" & Me.FindWord & "*"")
AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Nothing is specified.", vbInformation, "Nothing to show."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
{/code}
{code}
Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me.FilterOn = False
End Sub
{/code}

However, what I want is that I would like to accomplish the same thing on a
SubForm inside the main Form. So I made some changes as follows:
* Me.Filter = strWhere was changed to:
Me.Child19.Form.Filter = "[English]='" & Me.FindWord & "'"
* Me.FilterOn = True was changed to:
Me.Child19.Form.FilterOn = True

This modified code is working, but
1) when I search a word such as "tomato", the result used to be like
"tomato" and "tomatoes", but now I get only "tomato".

2) when I hit the reset button on the result page, it used to go back to the
original screen, but now it just resets (clears) the word in a search box
(called FindWord unbound text box).

Would you be able to help me accomplish the above 1) and 2) on a SubForm?

With best regards,
Hiro
Click to expand...
Click to expand...
 
S

Steve Schapel

Hiro,

Where is this Reset button? On a form, I presume, but which form? Is
the button on the same form as the controls you want to clear the
entries from?
 
H

Hiro

Hello Steve:

Thank you for looking into this problem once again. I am still trying to
find a solution for this.
The reset button (cmdReset) is placed on the main form (form header) and is
also showing up on the same main form (after filtering), from which I want to
clear and go back to the original main form (before filtering).
But does this information help?

With best regards,
Hiro Shiratori


Steve Schapel said:
Hiro,

Where is this Reset button? On a form, I presume, but which form? Is
the button on the same form as the controls you want to clear the
entries from?

--
Steve Schapel, Microsoft Access MVP
Dear Steve:

Thank you for your help so fast.
I checked your suggestions and the {code}Me.Child19.Form.Filter = "[English]
Like '*" & Me.FindWord & "*'"{/code} puts the desired function back.
And for {code} For Each ctl In Me.Child19.Form.Section(acHeader).Controls
{/code} is not working. It does not reset (clears) the words on the result
page nor go back to the original screen.

Would you please advise me what other adjustments I need to make?
Click to expand...
Click to expand...
 
S

Steve Schapel

Hiro,

I must apologise, but I am really not clear what the situation here.

So you have a command button cmdReset which is on the Header section of
a form. And the goal is to clear he values from some controls. So what
is the name of the form? And where are the controls?
 
H

Hiro

Hello Steve:

Once again, thank you for your continued follow-up.
* Main Form is called DictionaryMain_Query.
On the form header of the main form, I have placed an unbound text box
"FindWord", a filter button "cmdFilter" and a reset button "cmdReset".
* Sub Form is called DictionaryMain_Query_SubForm.

So far, the filter button "cmdFilter" is working. Does the reset button
"cmdReset" allow me to clear the words inside the unbound text box and go
back to the original main form before filtering using a sub form approach?
Because if I juse just a main form, the reset button "cmdReset" just does
clear the words inside the unbound text box and go back to the original main
form before filtering.

With best regards,
Hiro Shiratori
 
S

Steve Schapel

Hiro,

Ok, I think I understand. I think your code needs to be like this:

Private Sub cmdReset_Click()
Me.FindWord = Null
With Me.DictionaryMain_Query_SubForm.Form
.Filter = ""
.FilterOn = False
End With
End Sub

Let us know if it is still not doing what you want.
 
H

Hiro

Hello Steve:

Wow, it is now working the way I wanted. Thank you so much!!
Even though my sub form name is DictionaryMain_Query_SubForm, for some
reason the sub form property under the main form is named as Child19. I don't
rememer changing it. Maybe when I was reading and following the tutorial
somewhere I must have mixed up.
Anyway, my form is working. Once again, I sincerely appreciate your time and
great support!!

With best regards,
Hiro Shiratori
 
S

Steve Schapel

Hiro,

I am very happy to hear that this is working for you now.

When you have a subform, there are two names involved. There is the
name of the form that is used as the subform, and there is the name of
the subform control on the main form. Sometimes these names are the
same as each other, sometimes not, depending on the way in which you
embedded the subform onto the main form. In your code, you need to use
the name of the subform control on the main form.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open in datasheet view 0
cmdReset on Access 2000 0
How to Search Multiple Values in a Filter 5
MultiList Select Box Search Form 7
Datasheet view alignment after filtering on subform 3
Creating Preview button with variable 6
Error on modified Allenbrowne SearchForm 6
Multiselect List Box 4

Top