List Box Requery/Refresh

misterqj · July 15th, 2003, 01:26 PM

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.

Code:

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
        .AddNew
            !ID = Forms!Members!MemberID
            !County = CountyList.ItemData(CurItem)
        .Update
    End With
Next CurItem

rstCounty.Close
myConn.Close
Set myConn = Nothing
'Requery and refresh to update display
MemberCounties.Requery
CountyList.Requery

blueflame · July 16th, 2003, 12:21 PM

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.

misterqj · July 16th, 2003, 08:25 PM

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.

Dataman · July 16th, 2003, 11:39 PM

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
me.Counties.Requery
me.MemeberCounties.Requery

'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,
Mike
Will code food......

misterqj · July 17th, 2003, 07:33 PM

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.
Quint

Dataman · July 17th, 2003, 11:00 PM

Quint,

  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,
Mike
Will code for food.....

BethMoffitt · July 18th, 2003, 07:41 AM

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.

Regards,

Beth