Solved

DBList and multiple selection

Posted on 2000-05-09
16
293 Views
Last Modified: 2008-02-20
 I wrote an application where the user can select zip codes (both canadian and US), and I have a dblist listing all zipcodes for a given country, so the user can choose one or more of them. But I have had many demands to allow MULTIPLE SELECTION (like when u click on a line and the scroll down and click another one with SHIFT pressed so ALL lines between both lines clicked become highlited).
 After some research on the net (here and elsewhere), it doesnt seem possible to do this with a DBLIST, only LISTBOX. (I am using RDO so I cant use datalist). I know I could manually populate a LISTBOX but there are OVER 700 000 zip (postal codes) in canada! It doesnt seem practical/efficient to populate a LISTBOX with so many records.

Does anybody have a solution to this? Thanks in advance.

Jocelyn Hould
0
Comment
Question by:Simonac
  • 5
  • 5
  • 4
  • +1
16 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 2793760
http://www.apexsc.com/truedblistpro5/features.html

if its an option you may consider getting the True DBList 5.0, a list of its features are shown at the above link
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2793823
Yikes? RDO.. where does the Zip Code database reside.. server side or client?

If you can retrieve the zipcodes quickly.. I would suggest that based on the entry keyed into the City field do a SQL query to retrieve a Like type recordset of city/province/state-zipcodes. Upon entry of the city/province/state.. do a find on the recordset and populate a zipcode combobox with the results. Since the zipcode combobox will accept text the user can override it.. or use the dropdown to grab any Zip that is listed there.

If data access speed is going to be a problem, rather than intuitively load the zipcode combo.. make a simple command button that reads Find and only becomes visible when the Zip Code Text field gains focus. With hitting the Find ZipCode button, the user will expect a little delay and the SQL query results can be displayed in a multiselect listbox.

Naturally this is all predicated upon having previous address information being keyed. But the concept is the same.. load as little data in the combo/list as possible for a smooth running form.

Oh.. and one other thing.. consider converting over to ADO and using a disconnected recordset (I don't know if this is possible with RDO).. and then use a Filter to sift through only the records you want.
0
 
LVL 2

Author Comment

by:Simonac
ID: 2793905
azra: Tks for the info, but I was aware of tdblist.. problem is convinving management buying this only to have multiple selection...

wsh2:
(i)database is client side, one user only.
(ii) I dont have a city field, I could use STATE but even then, in ONTARIO there must be more than 300k zips.. Back to square one.
(iii) dont want combo box; user must be able to do a multiple selection with SHIFT
(iv) I can use a disconnected recordset in RDO (that the reason I switched from DAO in the first place), but I dont want to make another switch (to ADO) only for this functionnality (multiple selection). Problem is I cant use a recordset with a listbox, and I need multiple selection AND to have all zip in a list allowing multiple selection with shift.

hopes this clarify things a bit, and makes sense :)
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2793930
maybe have a separate listbox that is populated as the user clicks items in the dblist.  allow for, say, double clicking an item in the dblist will add it to the listbox.  i dont know if this will totally screw up your UI or if its just not a very effective solution but it is an idea.
0
 
LVL 2

Author Comment

by:Simonac
ID: 2793961
Well funny u would suggets that that's exactly what i've got right now! The problem my users are experiencing is that lets say u want to have all postal codes between J0E 2J0 and J9A 4K2 and that it represents 1000 lines, the user have to dblclick 1000 times! If they could use multiple selection, they would click J0E 2J0, scroll down to J9A 4K2, click it while pressing shift, then click the button I have on my form to take the selection and put it in the list box containing all selected zips, and Voila!
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2794027
I like Azra's thought of a seperate listbox.. it keeps all the selections in ready sight. Another approach is to dispense with the listbox and manually load your recordset into a Listview.. which does provide for multiselection.

Regardless of either of these methods, loading 300,000 Abstract Ziop code entries into ANY control is going to be an ergonomic nightmare. Your thumb is going to be a microdot and as sensitive as a Latin Lover that has just been jilted. Somehow, you have to find a way to make this more user friendly.. perhaps you could leave a little bit more information on how the user knows which zip codes to select in using your application. You may even want to consider using a TreeView in a Province / First Letter of City / City Name / Zipcode format.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2794044
Scrolling a 1000 lines is no fun.. <wink>.. a From/To positioning option would be nice.. <smile>. To make it work with a single listbox.. set up a binary switch where the first click goes into the From box.. and the next click goes into the To box.. and then click a command button to process. Oh.. and also add a text box for manual search/positioning into the Listbox.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2794061
yep that would be my suggestion
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Author Comment

by:Simonac
ID: 2794213
Well after reading your comments I had an idea; tried it tested it and it works! I catch the shift and do the selection programatically:
-------------------------------------
Dim strText As String

Private Sub DBList1_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)

    If Shift = 1 And strText <> "" And strText <> DBList1.Text Then
        If MsgBox("Are u sure u want to move to selected zips all zips between " & _
            strText & " and " & DBList1.Text, vbYesNo) = vbYes Then
                ' add all valid entries to listbox
                ' blablabla
        End If
    Else
        strText = DBList1.Text
    End If

End Sub
---------------------------

As for the Find the DBList already offers that functionality (match entry = extended), but a distinct textbox may be useful since it will make the search faster (since the dblist will position itself after EACH keystroke, thus 6 positionning for a 6 digits postal code).

So ill wait to see if someone come up with a better solution and if not ill give u some points since u helped me in a time of dire need... :)

If u see any flaw in my solution and/or improvements dont hesitate!
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2794288
I still prefer displaying a control with From / To on it.. so that the User has something to refer back to in case they lose their place.. but to each their own.. <smile>.
0
 
LVL 2

Author Comment

by:Simonac
ID: 2794306
Well the advantage I see to my solution is that it is very similar to the usual comportment of a windows app, almost every user knows they can use SHIFT to multiple select, some might get confused with your approach.. and since im the support for this thing, id like to keep confusion to a minimum :)

but what do u mean by "losing their place" ? if that means knowing what was the other line selected so they know what will be the interval selected u'll notice my solution provides that via the msgbox.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2794548
Yes.. but your msgbox.. only appears when the second listbox item is clicked. Let me give you an example of what I am saying.. I select the JOE 123 Zip.. and then start scrolling down the list box to pick the second zip. In the process.. I forget which Zip I had started from.. now I have to go back to find out where I started.. and then scroll back down again. To alleviate this.. a little popup control with the last Zip code selected could be of benefit. The helper display box only appears when the zipcode listbox control has focus.. as soon as the zipcode listbox control loses focus.. the helper display box disappears. Azras suggfestion of a Selected Item Listbox would provide a very nice Helper display to keep the operator appraised of where they are at any time. For multiselects.. just post a From: xxxxxxx To: yyyyyyy line for multiselection.. rather than each individual code.. OR.. use the helper listbox for multiselection item removal.
0
 
LVL 28

Accepted Solution

by:
AzraSound earned 25 total points
ID: 2794549
i sort of agree with displaying a from/to option...perhaps give two option buttons

1) select range
2) select single

with select range they specify the range by selecting the two zip codes that will appear in the from/to captions...allow for a simple mechanism for them to change it if needed...if its a single selection then you can implement the select an item, move it to the listbox method
0
 
LVL 2

Author Comment

by:Simonac
ID: 2799967
OK here's what i'll do: i'll put a text box for the selected item, so the user knows what was the last item he clicked, eliminating confusion as to "what was my first click anyway?", and apply my code.

For your help i'll give u both 25 points for helping me. Look for a Q with your name in the title.
0
 
LVL 3

Expert Comment

by:darinw
ID: 2801090
Community Support has reduced points from 75 to 25
0
 
LVL 3

Expert Comment

by:darinw
ID: 2801091
Hello everyone,

I am reducing the points to 25 on this question to allow for a partial award and split.

Simonac - you can now accept one Expert's comment as an answer to award the first Expert. Remember, the Accept Comment as Answer button is in the header of the comment.

For the second Expert, you need to create a new question in this topic area. The new question title should be 'For ExpertName -- 10341383' with the appropriate Expert name and points.

For your convenience, you can use this link to create your new question:
http://www1.experts-exchange.com/bin/NewQForm?ta=31

darinw
Customer Service

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now