Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 15th, 2003, 01:26 PM
Authorized User
Join Date: Jul 2003
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default List Box Requery/Refresh

We are all familiar with the window where you have 2 list boxes: one with a list of options, and one with the options you have selected. Add and Remove buttons are used to select items from the source list. When you select an option from the source list, it shows up in your selected list, and is removed from the source list.

I am trying to implement something similar in Access 2000. Both list boxes are based on a query, and when the form opens, the contents of the list boxes is correct. However, when I add an item from the source list to my selected list, I can't get the list boxes to update appropriately. I have checked the underlying table, and it is updated correctly, it is just the GUI that won't update. I issue a requery and refresh on the list boxes after modifying the table and no dice. Interestingly, if I manually refresh a few seconds later, the list boxes show the correct info again.

It looks like something is taking a while in the background, and I don't know what it is. I am posting code below.

Thanks in advance for any assistance.

Dim CountyList, MemberCounties As Control
Dim CurItem As Variant
Dim myConn As ADODB.Connection
Dim rstCounty As ADODB.Recordset

Set myConn = New ADODB.Connection
Set rstCounty = New ADODB.Recordset
myConn.Open CurrentProject.Connection
rstCounty.ActiveConnection = myConn
rstCounty.LockType = adLockOptimistic
rstCounty.CursorType = adOpenDynamic
rstCounty.Open "Counties", , , , Options:=adCmdTable

'Return Control object variable pointing to list box.
Set CountyList = Forms!ChooseCounties!Counties
Set MemberCounties = Forms!ChooseCounties!MemberCounties
'Lopp thru the list of counties and get the selected items.
For Each CurItem In CountyList.ItemsSelected
    'If the item is selected, add it to the Counties table
    'using the ID from the current record in the Members form
    With rstCounty
            !ID = Forms!Members!MemberID
            !County = CountyList.ItemData(CurItem)
    End With
Next CurItem

Set myConn = Nothing
'Requery and refresh to update display
Old July 16th, 2003, 12:21 PM
Registered User
Join Date: Jul 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

If your Forms!ChooseCounties Recourd Source is a Table rather than a Query it is considered a non-updateable recourd source, and therefore a refresh of the form will not show the data on the form since the form's recordset will not get refreshed even with a Form.Refresh or a MemberCounties.Refresh. If you change the Record Source of your form to a QBE or Query, and make sure it is an updateable query - then issuing a Forms!ChooseCounties.Refresh, or DoCmd.Refresh, or Me.
Refresh should do it. A Forms!ChooseCounties!MemberCounties.Requery will not work unless you refresh the form's data source, since it is based on the data of the form.
Old July 16th, 2003, 08:25 PM
Authorized User
Join Date: Jul 2003
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts

The form itself doesn't have any record source. I am just using it as a container for 2 list boxes. Each of the list boxes uses an SQL statement as the record source. Interestingly, if I just wait a few seconds and force a refresh (Alt+F9) the changes show correctly.

The code I posted is part of the Add button you can see at the page I link to below.

Go to www.scaled-solutions.com/access_ex to see a screen shot of my form and the SQL statements for each list box.

Thanks for taking the time to respond.
Old July 16th, 2003, 11:39 PM
Authorized User
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

I came into this topic a little late, but I would change the insertion of a new record to an SQL statement.

Dim strSQL as String

strSQL = "INSERT INTO Counties (ID, County) Values(" & Forms!Members!MemberID & ", '" & me.countyList.itemdata(me.countylist.listindex) & "')"

Use the above SQL statement instead of cycling through each item in the list box.

You could replace all of your code with this

Dim strString as string

strSQL = "INSERT INTO Counties (ID, County) Values(" & Forms!Members!MemberID & ", '" & me.countyList.itemdata(me.Counties.listindex) & "')"

Currentdb.Execute strSQL

'The code above should improve your performance significantly and also get rid of any update issues.

If I missed the ball on this one, I appologize. I do have some code that will ensure that the highlight line stays highlighed even after the requeries are executed. This makes for a very quick user interface.

Hope this helps,
Will code food......
Old July 17th, 2003, 07:33 PM
Authorized User
Join Date: Jul 2003
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts

Dataman - you rule!

I thought I would never be able to build a control like that. I have been teaching myself VB/Access 2000 using books from Wrox and O'Reilly, and everyone seems to go about things the long, hard way. I am used to Perl where TIMTOWTDI, and usually shorter ways as well.

It looks using straight SQL is a good way to speed up Access applications and shorten the code.

Thanks very much for your insight.
Old July 17th, 2003, 11:00 PM
Authorized User
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts


  Glad I could help....I like this forum. You should read up on the currentdb object. It can save you a bunch of code and time. If you are connecting to SQL server you can still use the SQL, you just have to use ADO to connect first.

  I have an application that uses Access XP as the front end and SQL Server 2000 (130 tables) as the backend as well as some 30 access tables. I've been able to bind forms directly to stored procedures from SQL Server...very cool stuff....its relatively small...the application only has about 60,000 lines of code (VBA) and 20,000 lines of T-SQL.

  One process that used to take 240 minutes to run in Access now takes only 6 mintues to run in SLQ Server....the power is yours!!!!

Take care,
Will code for food.....
Old July 18th, 2003, 07:41 AM
Friend of Wrox
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts

I came in late on this thread, but if I read it right, I would have to disagree with the statement that "If you are connecting to SQL server you can still use the SQL, you just have to use ADO to connect first."

We have used SQL Servers as the backend for a very very long time (back in Access 2.0 and now using Access XP) and I have *never* used ADO, strictly DAO. However, we do use ODBC connections to the database via a DSN.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 2118 from combo box requery darrenb Access VBA 1 January 8th, 2008 01:31 AM
multi-column list box values moved to 2nd list box sbmvr Access VBA 1 May 14th, 2007 01:58 PM
help please, object list cannot refresh kawak_zx7 General .NET 4 December 10th, 2004 07:36 AM
Populate List Box by Combo Box Selection mmcdonal Access 2 June 15th, 2004 12:08 PM
Search using drop down list box and a text box tcasp Classic ASP Basics 1 July 31st, 2003 02:58 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.