Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 15th, 2003, 01:26 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
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.

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
Reply With Quote
  #2 (permalink)  
Old July 16th, 2003, 12:21 PM
Registered User
 
Join Date: Jul 2003
Location: Idaho Falls, Idaho, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old July 16th, 2003, 08:25 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old July 16th, 2003, 11:39 PM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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......
Reply With Quote
  #5 (permalink)  
Old July 17th, 2003, 07:33 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old July 17th, 2003, 11:00 PM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.....
Reply With Quote
  #7 (permalink)  
Old July 18th, 2003, 07:41 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: Houston, Texas, USA.
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 2118 from combo box requery darrenb Access VBA 1 January 8th, 2008 12: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 06: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



All times are GMT -4. The time now is 04:09 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.