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

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

Hiro wrote:
> 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
> 

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" wrote:

> 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
> 
> Hiro wrote:
> > 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
> > 
> 

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

Hiro wrote:
> 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?

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" wrote:

> 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
> 
> Hiro wrote:
> > 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?
> 

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?

-- 
Steve Schapel, Microsoft Access MVP

Hiro wrote:
> 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?
> 

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

"Steve Schapel" wrote:

> 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?
> 
> -- 
> Steve Schapel, Microsoft Access MVP
> 
> Hiro wrote:
> > 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?
> > 
> 

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.

-- 
Steve Schapel, Microsoft Access MVP

Hiro wrote:
> 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.

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

"Steve Schapel" wrote:

> 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.
> 
> -- 
> Steve Schapel, Microsoft Access MVP
> 
> Hiro wrote:
> > 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.
> 

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.

-- 
Steve Schapel, Microsoft Access MVP

Hiro wrote:
> 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!!
>