Requery data in a form

[Guest]

Hi,
I've scoured this discussion group for hours and am frustrated that I cannot
'see' the answer to my question.
I have created a query that displays records where it finds a matching text
string within a text field qryCheckName. I have created a form that displays
the query data frmNameUnique. In the header to the form is an unbound text
field where the user enters the text string they wish to search for. I have
added a Command button and used innumerable of combinations of field names,
form names and syntax in VBA and Macros but cannot get the form to refresh
when the text string is changed. Switching between form Open and Design
causes the data to refresh as intended.
(I have also tried using a sub form. The Form header holds the unbound text
field and the subform the result of the query. The sub form refreshes
correctly on switching between form open and design but again I cannot get a
Command button to refresh the subform.)
Can someone please put me out of my misery!

 

[Guest]

Hi,

My guess is that your problem originates in how you link the value in your
unbound text box to the underlying query of your sub form.

The event that triggers the requery should be the On Click event of your
Command Button - so far, so good.

You don't mention how your query searches for the comparison. If it's very
complex you may have to reconstruct the entire SQL string. If it's simpler
then you can try passing the text box value as a parameter. It's so long
since I did that that I have forgotten how it's done but there is a simpler
way!

Remove any WHERE clause from your sub form's query, qryCheckName. In the On
Load event for the sub form code something like:

Private Sub Form_Load()
Me.Filter = "<astringresultsetfieldname> = '123'"
Me.FilterOn = True
End Sub

The idea is that you specify something that is always false for the filter
property such that it selects no rows for the result set, your sub form will
be empty.

Now for the button's On Click event code something like:

Private Sub btnWhatever_Click()
Me.<mycontrolnamethat holdsthe subform>.Form.Filter = "<mynamefield>
Like '*" & me.<myunboundtextbox> & "*'"
End Sub

This should trigger a requery and perform the comparison. The asterisks are
included such that the supplied string is matched in any position.

Regards,

Rod

 

[Guest]

With your assistance I'm moving in the right direction but I'm not all the
way there. I've got your 'on load' code working fine and understand the
concept for the code. However, I haven't mastered the on click code for the
Refresh button. What I don't understand is what you mean by
<mycontrolnamethat holdsthe subform>. What is the 'control name' and where do
I find it? Is it the name of the form or subform... I don't understand.
I created the parent form in design view and created a text field to provide
the unbound field for entering the search string. I then used the wizard to
create the subform based on qryCheckName. Prior to the editing you prompted,
I had a condition in qryCheckName that filtered the relevant text field with
the following condition Like "*" & [tblCheckUnique]![Surname] & "*".
Following your strategy I don't see where there is a 'link' between the entry
in the unbound field and the sub form.
Further assistance would be appreciated.

Regards,

Tesa

 

[Guest]

Ahhh! No wonder you're confused; I read your original post too quickly and
assumed a main form / sub form combination. Now I hope I've got it right.

The technique is the same except we now want to amend the filter property of
the main/only form. So the code behind the On_Click event of the refresh
button is now

Me.Filter = "<mynamefield> Like '*" & me.<myunboundtextbox> & "*'"

I tend to use angle bracket when you need to substitute your own specific
names. <mynamefield> is the name of the field in the result set of your SQL,
Query or Table - almost assuredly in your case the name of the field on the
table. <myunboundtextbox> is the name of your text box control in the Header
section of your form.

If you want to enhance this feature then teach your users about the syntax
of the Like function, particularly the wild cards (*,?,#,...) and the
alternates ([ABX],...). Then remove the asterisks from the On_Click
statement and get the users to enter them if needs be.

Regards,

Rod

With your assistance I'm moving in the right direction but I'm not all the
way there. I've got your 'on load' code working fine and understand the
concept for the code. However, I haven't mastered the on click code for the
Refresh button. What I don't understand is what you mean by
<mycontrolnamethat holdsthe subform>. What is the 'control name' and where do
I find it? Is it the name of the form or subform... I don't understand.
I created the parent form in design view and created a text field to provide
the unbound field for entering the search string. I then used the wizard to
create the subform based on qryCheckName. Prior to the editing you prompted,
I had a condition in qryCheckName that filtered the relevant text field with
the following condition Like "*" & [tblCheckUnique]![Surname] & "*".
Following your strategy I don't see where there is a 'link' between the entry
in the unbound field and the sub form.
Further assistance would be appreciated.

Regards,

Tesa

Hi,

My guess is that your problem originates in how you link the value in your
unbound text box to the underlying query of your sub form.

The event that triggers the requery should be the On Click event of your
Command Button - so far, so good.

You don't mention how your query searches for the comparison. If it's very
complex you may have to reconstruct the entire SQL string. If it's simpler
then you can try passing the text box value as a parameter. It's so long
since I did that that I have forgotten how it's done but there is a simpler
way!

Remove any WHERE clause from your sub form's query, qryCheckName. In the On
Load event for the sub form code something like:

Private Sub Form_Load()
Me.Filter = "<astringresultsetfieldname> = '123'"
Me.FilterOn = True
End Sub

The idea is that you specify something that is always false for the filter
property such that it selects no rows for the result set, your sub form will
be empty.

Now for the button's On Click event code something like:

Private Sub btnWhatever_Click()
Me.<mycontrolnamethat holdsthe subform>.Form.Filter = "<mynamefield>
Like '*" & me.<myunboundtextbox> & "*'"
End Sub

This should trigger a requery and perform the comparison. The asterisks are
included such that the supplied string is matched in any position.

Regards,

Rod

 

[Guest]

Rod, thanks for persisting. I have now achieved my goal!
I could note get your code Me.Filter = "<mynamefield> Like '*" &
me.<myunboundtextbox> & "*'" to work in a Form. I could get it to work if I
was not trying to wildcard the string.
However, with a little trial and error (syntax) I now have a form with
subform achieving exactly what I need. The on click code is:
Me.<subformname>.Form.Filter = "<mynamefield in the subform> Like '*" &
Me.<myunboundtextbox> & "*'"
Strangely, I don't want the users wildcarding their search. The requirement
is for them to be able to enter a name string and then find if there are any
names in a free text field that matches the string they enter.
Thanks for your help, much appreciated.

Regards,

Tesa

Ahhh! No wonder you're confused; I read your original post too quickly and
assumed a main form / sub form combination. Now I hope I've got it right.

The technique is the same except we now want to amend the filter property of
the main/only form. So the code behind the On_Click event of the refresh
button is now

Me.Filter = "<mynamefield> Like '*" & me.<myunboundtextbox> & "*'"

I tend to use angle bracket when you need to substitute your own specific
names. <mynamefield> is the name of the field in the result set of your SQL,
Query or Table - almost assuredly in your case the name of the field on the
table. <myunboundtextbox> is the name of your text box control in the Header
section of your form.

If you want to enhance this feature then teach your users about the syntax
of the Like function, particularly the wild cards (*,?,#,...) and the
alternates ([ABX],...). Then remove the asterisks from the On_Click
statement and get the users to enter them if needs be.

Regards,

Rod

With your assistance I'm moving in the right direction but I'm not all the
way there. I've got your 'on load' code working fine and understand the
concept for the code. However, I haven't mastered the on click code for the
Refresh button. What I don't understand is what you mean by
<mycontrolnamethat holdsthe subform>. What is the 'control name' and where do
I find it? Is it the name of the form or subform... I don't understand.
I created the parent form in design view and created a text field to provide
the unbound field for entering the search string. I then used the wizard to
create the subform based on qryCheckName. Prior to the editing you prompted,
I had a condition in qryCheckName that filtered the relevant text field with
the following condition Like "*" & [tblCheckUnique]![Surname] & "*".
Following your strategy I don't see where there is a 'link' between the entry
in the unbound field and the sub form.
Further assistance would be appreciated.

Regards,

Tesa

Hi,

My guess is that your problem originates in how you link the value in your
unbound text box to the underlying query of your sub form.

The event that triggers the requery should be the On Click event of your
Command Button - so far, so good.

You don't mention how your query searches for the comparison. If it's very
complex you may have to reconstruct the entire SQL string. If it's simpler
then you can try passing the text box value as a parameter. It's so long
since I did that that I have forgotten how it's done but there is a simpler
way!

Remove any WHERE clause from your sub form's query, qryCheckName. In the On
Load event for the sub form code something like:

Private Sub Form_Load()
Me.Filter = "<astringresultsetfieldname> = '123'"
Me.FilterOn = True
End Sub

The idea is that you specify something that is always false for the filter
property such that it selects no rows for the result set, your sub form will
be empty.

Now for the button's On Click event code something like:

Private Sub btnWhatever_Click()
Me.<mycontrolnamethat holdsthe subform>.Form.Filter = "<mynamefield>
Like '*" & me.<myunboundtextbox> & "*'"
End Sub

This should trigger a requery and perform the comparison. The asterisks are
included such that the supplied string is matched in any position.

Regards,

Rod
:

Hi,
I've scoured this discussion group for hours and am frustrated that I cannot
'see' the answer to my question.
I have created a query that displays records where it finds a matching text
string within a text field qryCheckName. I have created a form that displays
the query data frmNameUnique. In the header to the form is an unbound text
field where the user enters the text string they wish to search for. I have
added a Command button and used innumerable of combinations of field names,
form names and syntax in VBA and Macros but cannot get the form to refresh
when the text string is changed. Switching between form Open and Design
causes the data to refresh as intended.
(I have also tried using a sub form. The Form header holds the unbound text
field and the subform the result of the query. The sub form refreshes
correctly on switching between form open and design but again I cannot get a
Command button to refresh the subform.)
Can someone please put me out of my misery!