AccessDiscussion 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 .
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
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.
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.
'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.
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.
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!!!!
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.
List Box Requery/Refresh
Linear Mode
