Moving items between listboxes

[MajP]

I added a demo of a Large From to List with about 10K available. But no issue doing any size. As set up I would think no problem handling a several 100k you would need to slightly modify the code once you get into the millions so that you never go over the 60k limit. It is a combination of a FAYT and a From to list. So you can filter as you like on the left side and select your records. Then you can search as needed on something else. In this example the left list starts blank then returns all words with "A" then "A" then "AAR".

If you list gets into the millions then you will have to be more restrictive. You will have to force the user to provide a minimum amount of letters to search. Here it starts after just one letter.

There is really almost no new code to make this work. I had to hard wire the search and the update and delete queries.
This is pretty much the whole code that needs to be touched, everything else is in the class module. You initialize the class and code the delete and update queries.

Public WithEvents FormFromTo As Form_FromToListLarge
Private Sub Form_Load()
  Set FormFromTo = Me.subform_FromToList.Form
  Dim FromSQL As String
  Dim ToSQL As String
  FromSQL = "SELECT tbldata.PersonID, Full_Name FROM tblData where True = false"
  ToSQL = "SELECT tbldata.PersonID, Full_Name FROM tblData inner JOIN tblSelectedData ON tblData.PersonID = tblSelectedData.PersonID ORDER BY tblData.Full_Name"
  FormFromTo.FTL_InitializeFromTo FromSQL, ToSQL, "People Not Selected", "People Selected", True
End Sub

Private Sub FormFromTo_ItemFromAdded(Item As Variant)
  Dim strSql As String
  strSql = "Delete * from tblSelectedData where PersonID = " & Item
  CurrentDb.Execute strSql
  FormFromTo.ListFrom.Requery
  FormFromTo.ListTo.Requery
End Sub

Private Sub FormFromTo_ItemToAdded(Item As Variant)
  Dim strSql As String
  strSql = "Insert into tblSelectedData (PersonID) VALUES (" & Item & ")"
  CurrentDb.Execute strSql
  FormFromTo.ListTo.Requery
  FormFromTo.ListFrom.Requery
End Sub

 

[Deleted member 73419]

WHAT are your listboxes used for? Why do they start out totally unfiltered?

The list boxes are used to to select items. They are unfiltered as the user has no idea what they want to select until they've found what they're looking for.
If they were filtered initially, there is every chance what the user is looking for would not be in the list...

 

[Pat Hartman]

Even in the example I posted where the left list starts out being filtered by job, there is additional filtering criteria such a type of drawing or parts of the drawing name. No one wants to look through a list thousands of rows long. The user is looking for something. WHAT is he looking for? Clearly, the user knows what he wants or he wouldn't be able to pick it out of a list.

You attempted to answer the question but you provided no useful information. So, I guess you have a "solution". Your "phone" book is the billions of people in the world rather than those with last names starting with h or living in Connecticut.

 

[MajP]

The list boxes are used to to select items. They are unfiltered as the user has no idea what they want to select until they've found what they're looking for.
If they were filtered initially, there is every chance what the user is looking for would not be in the list.

That does not make any logical sense. Assume you have a very large list, having all the records visible to start is of no value. You cannot scroll 70k, 10k, 5k or probably even 1K. The user might not know exactly what they are looking for, but for sure they at least have to have an idea of what it could look like.

In my case I know it is "Aaron" something with an "L". But I do not know if Aaron is spelled Aaron, Ahren, Aaron. I think it is "Le" something. So I am not scrolling 10k records to see if I can find something. I start with no records returned. I will then type "Aar" and that should get the list to something manageable. I can then try "Ahr" if I do not find a solution. If I still cannot find it I am searching "Le" and seeing if that works.

In my case I return records after a single letter is applied. If it was a really big list I would require the user to provide at least a couple of letters. My phonebook starts returning records when you enter the "H".

If this was a huge list of names, of course the search would be even more restrictive. I do an "In" string search but would have to change to a "start with" search. Also the search would not occur on each keystroke. You would type a minimum string and then hit the search button.