Refreshing a text box displaying a column in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table containing a number of columns, including "SNameEx" and
"SearchTerms". "SearchTerms" contains a number of different words for
searching purposes. The searching itself is done by a query with a (rather
lengthy) search criteria applied to the "SearchTerms" column. The criteria
goes something like:


Like IIf([Forms]![Search List]![con]=True,"*Con*","") Or
Like IIf([Forms]![Search List]![ign]=True,"*Ign*","") Or
Like IIf([Forms]![Search List]![spel]=True,"*Spl*","") Or
Like IIf([Forms]![Search List]![tra]=True,"*Tra*","") Or
Like IIf([Forms]![Search List]![trig]=True,"*Trg*","") Or
etc. etc.


This criteria references a number of check boxes in a form. If a check box
is checked, then the criteria will search for the associated word in the
"SearchTerms" column of my table, and display all rows containing that word
in their "SearchTerms" column. Any number of check boxes can be selected at
once, so then the query would search for all rows containing one word, and
all rows containing another word, and display all of them. Although you
probably kew all that from looking at the code.

My question is this: In the same form as the check boxes is a listbox. Its
Row Source is the query I just mentioned, and it displays a different column
"SNameEx") in the query. However, the contents of the list box does not
change to reflect any changes in the check box selections. Is there a way to
do this automatically (preferred), or can I add a "Refresh" button into the
form that will update the list box (and query) for the latest check box
selections?


Also, is there any limit on how long the search criteria for a column in a
query can be? I intend to add at least 50 more check boxes for dozens of new
words to be added later, which means a much longer criteria.

On the face of it it sounds like adding all these extra critera will make
the search form immensely slow, but will it? The table contains no more than
600 rows. In which case would it be better to auto-update the list box or to
use a "Refresh" button?


Many thanks.
~Maruno
 
I don't know about limits on the query length, but a way to do it is to
requery the ListBox in the AfterUpdate event of each checkbox:

Me!YourListBox.Requery

Sprinks
 
After creating a macro to update the list box, that code and the auto-update
works.

However, there's a 255 character maximum for the Criteria box in the query.
I accidentally overfilled it and now the query closes as soon as it opens so
I can't correct it.

More to the point (since I can just make a new query), how do I overcome
this limit? Or at least, how do I get more than 9 Criteria rows in my query?
Because odds are I'll be needing several more, as I have about 80 check boxes
in total I want to use (independently too, so I can't use combo boxes that
only allow one selection), and only about 8 criteria fit into one row.


Thanks.
~Maruno
 
Maruno,

The way I would approach this is to build an SQL string, and assign it to
the RowSource property of the listbox. The most convenient way to do this is
to loop through the collection of form controls. I'm assuming since you
built a macro rather than using VBA, you haven't had much experience with it.
It's worth the effort to learn (it's not difficult once you understand how
it's organized)--it's much more flexible, and enables you to trap errors
rather than dumping the user into the debugger.

Click on one of your check boxes, show the Properties (View, Properties).
First we'll change the Tag Property on the Other tab. Enter the text
associated with this checkbox ( "con", "ign", etc.) with no quotes. Next
click the Events tab. Click on the AfterUpdate field, and select the
ellipsis to the right of the field, and Code Builder if prompted. Access will
create the shell of the event procedure for you. Enter the following between
the Sub and End Sub lines:

Call UpdateListBox

Below the End Sub line, cut and paste the following procedure (it's tested),
changing the "YourTable" and "YourField" to the name of the table and field
where you're getting your data.

Private Sub UpdateListBox()
Dim ctl As Control
Dim strSQL As String

' Initialize search string
strSQL = "SELECT YourField FROM YourTable WHERE "

' Loop through all form controls
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
strSQL = strSQL & "YourTable.YourField Like '*" & ctl.Tag & _
"*' OR "
End If
End If
Next ctl

' Trim off last " OR "
strSQL = Left(strSQL, Len(strSQL) - 4)

' Assign string to RowSource property and requery
With Me![MyListBox]
.RowSource = strSQL
.Requery
End With

End Sub

Then create an AfterUpdate event procedure for each checkbox, with the
single line

Call UpdateListBox

between the Sub and End Sub lines.

Hope that helps.
Sprinks
 
You're right; I haven't really had any experience with this kind of thing
before.

I understand how your code works. I've followed your instructions, but it's
still not working. At the moment I only have 6 check boxes, all of which have
a default value of True. When I open the form, the list is filled with every
row in the table, but as soon as I change any of the check boxes all the
entries disappear and do not come back regardless of how I click the check
boxes again.

Perhaps this is the cause? Your code that adds terms to the string goes like:

strSQL = strSQL & "tblDatabase.Search Like '*" & ctl.Tag & _
"*' OR "

whereas the code I've been using looks like:

Or Like IIf([Forms]![Search List]![ign]=True,"*ign*","")

I'm using Office 2000, so perhaps your code is for a different version? I
didn't get any error messages, though; just a blank list box when I try to
change any check box selections.


~Maruno


Sprinks said:
Maruno,

The way I would approach this is to build an SQL string, and assign it to
the RowSource property of the listbox. The most convenient way to do this is
to loop through the collection of form controls. I'm assuming since you
built a macro rather than using VBA, you haven't had much experience with it.
It's worth the effort to learn (it's not difficult once you understand how
it's organized)--it's much more flexible, and enables you to trap errors
rather than dumping the user into the debugger.

Click on one of your check boxes, show the Properties (View, Properties).
First we'll change the Tag Property on the Other tab. Enter the text
associated with this checkbox ( "con", "ign", etc.) with no quotes. Next
click the Events tab. Click on the AfterUpdate field, and select the
ellipsis to the right of the field, and Code Builder if prompted. Access will
create the shell of the event procedure for you. Enter the following between
the Sub and End Sub lines:

Call UpdateListBox

Below the End Sub line, cut and paste the following procedure (it's tested),
changing the "YourTable" and "YourField" to the name of the table and field
where you're getting your data.

Private Sub UpdateListBox()
Dim ctl As Control
Dim strSQL As String

' Initialize search string
strSQL = "SELECT YourField FROM YourTable WHERE "

' Loop through all form controls
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
strSQL = strSQL & "YourTable.YourField Like '*" & ctl.Tag & _
"*' OR "
End If
End If
Next ctl

' Trim off last " OR "
strSQL = Left(strSQL, Len(strSQL) - 4)

' Assign string to RowSource property and requery
With Me![MyListBox]
.RowSource = strSQL
.Requery
End With

End Sub

Then create an AfterUpdate event procedure for each checkbox, with the
single line

Call UpdateListBox

between the Sub and End Sub lines.

Hope that helps.
Sprinks
Click to expand...
 
Maruno,

The procedure I gave you abandons the use of your

Or Like IIf([Forms]![Search List]!...etc., and the query it was associated
with.

It doesn't use your old query at all; it builds a new query statement on the
fly
using the Tag property of each checkbox. For the checkbox labelled "ign",
put "ign" in the Tag property, with no quotation marks, and similarly for
your other checkbox controls.

The fact that your listbox is going blank after you check or uncheck means
that there is something wrong with the SQL statement being assigned to the
RowSource.

If you can't get it to work, cut and paste post your entire procedure, and
we'll get it to work.

Sprinks

Maruno said:
You're right; I haven't really had any experience with this kind of thing
before.

I understand how your code works. I've followed your instructions, but it's
still not working. At the moment I only have 6 check boxes, all of which have
a default value of True. When I open the form, the list is filled with every
row in the table, but as soon as I change any of the check boxes all the
entries disappear and do not come back regardless of how I click the check
boxes again.

Perhaps this is the cause? Your code that adds terms to the string goes like:

strSQL = strSQL & "tblDatabase.Search Like '*" & ctl.Tag & _
"*' OR "

whereas the code I've been using looks like:

Or Like IIf([Forms]![Search List]![ign]=True,"*ign*","")

I'm using Office 2000, so perhaps your code is for a different version? I
didn't get any error messages, though; just a blank list box when I try to
change any check box selections.


~Maruno


Sprinks said:
Maruno,

The way I would approach this is to build an SQL string, and assign it to
the RowSource property of the listbox. The most convenient way to do this is
to loop through the collection of form controls. I'm assuming since you
built a macro rather than using VBA, you haven't had much experience with it.
It's worth the effort to learn (it's not difficult once you understand how
it's organized)--it's much more flexible, and enables you to trap errors
rather than dumping the user into the debugger.

Click on one of your check boxes, show the Properties (View, Properties).
First we'll change the Tag Property on the Other tab. Enter the text
associated with this checkbox ( "con", "ign", etc.) with no quotes. Next
click the Events tab. Click on the AfterUpdate field, and select the
ellipsis to the right of the field, and Code Builder if prompted. Access will
create the shell of the event procedure for you. Enter the following between
the Sub and End Sub lines:

Call UpdateListBox

Below the End Sub line, cut and paste the following procedure (it's tested),
changing the "YourTable" and "YourField" to the name of the table and field
where you're getting your data.

Private Sub UpdateListBox()
Dim ctl As Control
Dim strSQL As String

' Initialize search string
strSQL = "SELECT YourField FROM YourTable WHERE "

' Loop through all form controls
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
strSQL = strSQL & "YourTable.YourField Like '*" & ctl.Tag & _
"*' OR "
End If
End If
Next ctl

' Trim off last " OR "
strSQL = Left(strSQL, Len(strSQL) - 4)

' Assign string to RowSource property and requery
With Me![MyListBox]
.RowSource = strSQL
.Requery
End With

End Sub

Then create an AfterUpdate event procedure for each checkbox, with the
single line

Call UpdateListBox

between the Sub and End Sub lines.

Hope that helps.
Sprinks
Click to expand...
Click to expand...
 
So the query I used to populate the list box is now completely useless? I
made the "Row Source" and "Row Source Type" boxes for the list box blank, if
that's the case.

I can't find any problems with the code. I assume I've replaced all the
terms I should have done. Then again, I don't know too much about it. I've
attached it below.


~Maruno



Private Sub UpdateListBox()
Dim ctl As Control
Dim strSQL As String

' Initialize search string
strSQL = "SELECT Search FROM tblDatabase WHERE "

' Loop through all form controls
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
strSQL = strSQL & "tblDatabase.Search Like '*" & ctl.Tag & _
"*' OR "
End If
End If
Next ctl

' Trim off last " OR "
strSQL = Left(strSQL, Len(strSQL) - 4)

' Assign string to RowSource property and requery
With Me![lstsearched]
.RowSource = strSQL
.Requery
End With

End Sub
 
Back
Top