Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Forms Programming / October 2005

Tip: Looking for answers? Try searching our database.

Try to code a Command Button "Clear List"

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
j1eggert - 13 Jul 2005 15:58 GMT
I have set up a Find Records form for search the records listed in the table.
I have setup text boxes for searching the information with a Find Records
command button. I have also set up a command button called "Clear List". What
I am wanting is to clear the records in the list box so I can do another
search for a different set of records. If anyone can help with the correct
syntax and procedures (example would be a great help) I would really
appreciate it.
Reply to this Message
Allen Browne - 13 Jul 2005 16:11 GMT
Save the function into a standard module.

Set the On Click property of your list box to:
   =ClearList([List1])
where "List1" represents the name of your list box.

Function ClearList(lst As ListBox)
   Dim varItem As Variant

   If lst.MultiSelect = 0 Then
       lst = Null
   Else
       For Each varItem In lst.ItemsSelected
           lst.Selected(varItem) = False
       Next
   End If
End Function

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have set up a Find Records form for search the records listed in the
>table.
[quoted text clipped - 5 lines]
> syntax and procedures (example would be a great help) I would really
> appreciate it.
Reply to this Message
j1eggert - 13 Jul 2005 16:33 GMT
I have one more code problem with the Find Records Command Button. Here is
what I have written so far:

Private Sub cmdFindRecords_Click()
On Error GoTo Err_cmdFindRecords_Click

   Screen.PreviousControl.SetFocus
   DoCmd.FindRecord(FindWhat, [Match As AcFindMatch = acEntire],
[MatchCase], [Search As AcSearchDirection = acSearchAll], [SearcAsFormatted],
[OnlyCurrent As AcFindfield = acCurrent], [FindFirst])=
   Set Table!tblPrograms.FindRecords = " & "
   
Exit_cmdFindRecords_Click:
   Exit Sub

Err_cmdFindRecords_Click:
   MsgBox Err.Description
   Resume Exit_cmdFindRecords_Click
   
End Sub

> Save the function into a standard module.
>
[quoted text clipped - 13 lines]
>     End If
> End Function

I am not sure what goes after = in the DoCmd.FindRecords statement. Thanks
for your help.

> >I have set up a Find Records form for search the records listed in the
> >table.
[quoted text clipped - 5 lines]
> > syntax and procedures (example would be a great help) I would really
> > appreciate it.
Reply to this Message
j1eggert - 13 Jul 2005 16:51 GMT
Allen:
I put the code in a module (Function to End Function) and then I put the
=ClearList([lstFindRecords]) On Click property, but it is still not working
when I push the Clear List command button. Do I need to do something in the
command button to trigger the module I wrote? Here is what I wrote in the
module:

Option Compare Database

Function ClearList(lst As ListBox)
   Dim varItem As Variant
   
   If lst.MultiSelect = 0 Then
       lst = Null
   Else
       For Each varItem In lst.ItemsSelected
           lst.Selected(varItem) = False
       Next
   End If
End Function

> Save the function into a standard module.
>
[quoted text clipped - 23 lines]
> > syntax and procedures (example would be a great help) I would really
> > appreciate it.
Reply to this Message
Allen Browne - 13 Jul 2005 17:43 GMT
That should work. Are you getting an error message?

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen:
> I put the code in a module (Function to End Function) and then I put the
[quoted text clipped - 50 lines]
>> > syntax and procedures (example would be a great help) I would really
>> > appreciate it.
Reply to this Message
j1eggert - 13 Jul 2005 17:51 GMT
Yes, when it runs it nests the function under "Private Sub
cmdClearList_Click()" which causes the error. I placed the End Sub under the
End Function, but it did not like that either.

> That should work. Are you getting an error message?
>
[quoted text clipped - 52 lines]
> >> > syntax and procedures (example would be a great help) I would really
> >> > appreciate it.
Reply to this Message
Allen Browne - 14 Jul 2005 01:12 GMT
The idea was to place the function in a standard module, so you can reuse it
for any list box on any form.

1. Click the Modules tab of the database window.
2. Click New. Access opens a new module.
3. Past the function in there.
4. Save the module with a name such as Module1.

Now set the On Click property to
   =ClearList([lstFindRecords])

Or, if you want to set the On Click property to:
   [Event Proceure]
then between the Private Sub... and End Sub lines, enter:
   Call ClearList([lstFindRecords])

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Yes, when it runs it nests the function under "Private Sub
> cmdClearList_Click()" which causes the error. I placed the End Sub under
[quoted text clipped - 60 lines]
>> >> > syntax and procedures (example would be a great help) I would really
>> >> > appreciate it.
Reply to this Message
j1eggert - 14 Jul 2005 14:15 GMT
Yes, I followed your previous instructions and created a module, pasted the
funciton in to it and in the On Click property in the List Box typed the
=ClearList([lstFindRecords]). But, when I click the Clear List command button
it does nothing. In addition, I notice that when I do add a record it
automatically puts it in the list box. The process is to put in text in the
text boxes and search the database for those particular items. Next, I will
set up a print button that will take lthe ist in the list box and print out a
report, then clear the list box to do additional searches. I hope this gives
you a little more information to make a better determination on what the
problem is. Do I need to set the Form's "On Current" field with the Macros
that has all the Query search parameters listed with the action "OpenQuery"
selected?

> The idea was to place the function in a standard module, so you can reuse it
> for any list box on any form.
[quoted text clipped - 76 lines]
> >> >> > syntax and procedures (example would be a great help) I would really
> >> >> > appreciate it.
Reply to this Message
Allen Browne - 14 Jul 2005 15:28 GMT
Your original question (as I read it) was how to code to clear the items in
the list box.

If you select one or more items in the list box, and then click the button,
do they become unselected? That's what the code is designed to do.

If it does not do that, add this line to the top of the procedure, i.e. on a
new line just after "Function..."
   Stop
Then when the code runs, if it is being called correctly, it will stop here,
and you can press F8 to step through the code and track down where the
problem is.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Yes, I followed your previous instructions and created a module, pasted
> the
[quoted text clipped - 102 lines]
>> >> >> > really
>> >> >> > appreciate it.
Reply to this Message
j1eggert - 14 Jul 2005 16:18 GMT
Allen:
I tried highlighting the rows in the list box and press the Clear List
button. It removes the highlight, but not the information contained in the
fields. In addition, everytime I try to highlight a row I get this error
message "The expression On Click you entered as the event property setting
produced the following error. The epxression you entered has a function name
that Microsoft Office Access can't find."
Furthermore, I tried entering Stop on a new line under "Function
ClearList(lst As ListBox)" and under the "End Function" and it did not like
either one. Perhaps I am missunderstanding where to put the Stop in the code.
Really do appreciate your help.

> Your original question (as I read it) was how to code to clear the items in
> the list box.
[quoted text clipped - 115 lines]
> >> >> >> > really
> >> >> >> > appreciate it.
Reply to this Message
Allen Browne - 14 Jul 2005 16:30 GMT
So the code does unhighlight the items in the list (that's its intent), but
you actually want to remove the items from the list?

The answer to that question will depend where the list gets its items from,
and how you want them removed.

If the list gets its items from a table, you could delete all the items from
the table (e.g. execute a DELETE query), and then Requery the list box.

If you don't want to delete the items from the table - just not show them -
then change the RowSource of the list box to something like this:
   SELECT * FROM Table1 WHERE (False);

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen:
> I tried highlighting the rows in the list box and press the Clear List
[quoted text clipped - 146 lines]
>> >> >> >> > really
>> >> >> >> > appreciate it.
Reply to this Message
j1eggert - 14 Jul 2005 16:54 GMT
Thanks, I think this is going to work fine. After entering the code ti
immediately cleared the columns in the list box. I would like to test it out
further; however, I need to set the Find Records button working so it will
pull the information from the table to the list box based on the criteria
entered in the text boxes. What do you recommend to accomplish the Find
Record function?

> So the code does unhighlight the items in the list (that's its intent), but
> you actually want to remove the items from the list?
[quoted text clipped - 159 lines]
> >> >> >> >> > really
> >> >> >> >> > appreciate it.
Reply to this Message
Allen Browne - 14 Jul 2005 17:05 GMT
If it is a multi-select list box, you will need to loop through the
ItemsSelected property to build up the string to use for the Filter of your
form. You could adapt the code from this article so the string ends up as
the Filter of the form instead of the WhereCondition of the OpenReport:
   Use a multi-select list box to filter a report
at:
   http://allenbrowne.com/ser-50.html

If it is a single-select list box, and there is only one matching item in
your form, adapt this example:
   Using a Combo Box to Find Records
at:
   http://allenbrowne.com/ser-03.html

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks, I think this is going to work fine. After entering the code ti
> immediately cleared the columns in the list box. I would like to test it
[quoted text clipped - 3 lines]
> entered in the text boxes. What do you recommend to accomplish the Find
> Record function?
Reply to this Message
j1eggert - 14 Jul 2005 22:14 GMT
Allen:
I followed the instructions and one of the code statements toward the end
came up with an error and highlighted the Err_Handler: MsgBox. Furthermore, I
don't know how this helps my situation since I tried the form and it does not
work.

> If it is a multi-select list box, you will need to loop through the
> ItemsSelected property to build up the string to use for the Filter of your
[quoted text clipped - 17 lines]
> > entered in the text boxes. What do you recommend to accomplish the Find
> > Record function?
Reply to this Message
Allen Browne - 15 Jul 2005 02:08 GMT
I think I have given as much time as I can to this thread.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen:
> I followed the instructions and one of the code statements toward the end
[quoted text clipped - 3 lines]
> not
> work.
Reply to this Message
j1eggert - 15 Jul 2005 14:00 GMT
Allen:
Thank you for your help and I understand you cannot allow someone to
dominate your time. Have a great day.

> I think I have given as much time as I can to this thread.
>
[quoted text clipped - 5 lines]
> > not
> > work.
Reply to this Message
Ang - 27 Oct 2005 15:55 GMT
How to I change this to make it work for a combo box?

I pasted the code into a module and tried to use it to clear a combo box.

Since it was a data mismatch error I changed it to a list box.  The fucntion
worked.  

I'd like to use this for a form that has multiple combo boxes.  I tried
changing the first line to

Function ClearList(lst As ComboBox)

but then when I compiled I recieved the error message:

"compile error: Method or data member not found"
for .Multiselect in the opening if line.

I tried deleting the multiselect portion but then have the same error for
ItemsSelected.
Reply to this Message
Brendan Reynolds - 27 Oct 2005 16:25 GMT
Only one item at a time can ever be selected in a combo box, so there isn't
any list to clear.

To clear a combo box selection, just set the value of the combo box to Null
...

Me.NameOfComboBox = Null

Signature

Brendan Reynolds

> How to I change this to make it work for a combo box?
>
[quoted text clipped - 16 lines]
> I tried deleting the multiselect portion but then have the same error for
> ItemsSelected.
Reply to this Message
Ang - 27 Oct 2005 16:40 GMT
Thanks.

> Only one item at a time can ever be selected in a combo box, so there isn't
> any list to clear.
[quoted text clipped - 24 lines]
> > I tried deleting the multiselect portion but then have the same error for
> > ItemsSelected.
Reply to this Message
j1eggert - 13 Jul 2005 18:01 GMT
Allen:
The error occurs when I press the Clear List button because I set the button
to the Expression Procedure. If I am not suppose to do that please let me
know.

> That should work. Are you getting an error message?
>
[quoted text clipped - 52 lines]
> >> > syntax and procedures (example would be a great help) I would really
> >> > appreciate it.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.