Moving Names back and forth between two listboxes

[Valentino54]

Hi all, here I am with my first question...

The idea is to have two listboxes (lstboxA and lstboxB) on a form. Both listboxes have two columns, where column one is a text field and the second column is a check box. In lstboxA, the checkbox's value is True, in lstBoxB, instead, is False.

By doubleclickingon a name on the lstboxA i would like to uncheck the check box and refresh both listboxes.

My code tells me the column number and its values but I dont know how to modify the value of the checkbox.

By executing it I get Runtime Error 451 and so I'm stuck in it. Below the code I started with, only for lstboxA at moment, not complete yet.

Many thanks for help.

Private Sub lstGiaInvitato_DblClick(Cancel As Integer)

Dim intNumColonne As Integer
Dim intI As Integer
Dim frmInvito As Form

Set frmInvito = Forms!frmInvito
If frmInvito!lstGiaInvitato.ItemsSelected.Count > 0 Then

' Selezione
intNumColonne = frmInvito!lstGiaInvitato.ColumnCount
For intI = 0 To intNumColonne - 1
Debug.Print frmInvito!lstGiaInvitato.Column(intI)
Next intI

End If

frmInvito!lstGiaInvitato.Column(intI) = False

Set frmInvito = Nothing

End Sub

 

[theDBguy]

Hi. Just a thought, but maybe update the data in the source table and then refresh the listboxes.

 

[MajP]

Since I do this a lot I made a "User Control" that works on any form. You just need to instantiate it on a form. This is all the code needed.

Private Sub Form_Load()
  Set FormFromTo = Me.frmFromToList.Form
  Dim FromSql As String
  Dim ToSql As String
  FromSql = "SELECT ID, [Last Name] & ', ' & [First Name] AS FullName FROM Employees Where ID Not in (Select SelectedEmployeeID from TblSelectedEmployees) ORDER BY [Last Name]"
  ToSql = "SELECT ID, [Last Name] & ', ' & [First Name] AS FullName FROM Employees Where ID in (Select SelectedEmployeeID from TblSelectedEmployees) ORDER BY [Last Name]"
  FormFromTo.FTL_InitializeFromTo FromSql, ToSql, "Employees Not Selected", "Employees Selected"
End Sub

The ToSql is optional if not starting with any records in the To Listbox. This has a lot of bells and whistles for the buttons.

 

[Valentino54]

Hi MajP, many thanks for your suggestion, I appreciated it very much. But looking at all the code involved in your solution I tried to make it a bit simpler. I attach the solution I wrote. What do you think about? Is there any issue that might cause instability in the process?
I guess all depends what is the application for...
Many thanks again

 

[Valentino54]

Hi. Just a thought, but maybe update the data in the source table and then refresh the listboxes.

Hi, I followed your suggestion and it works... Thanks

 

[theDBguy]

Hi, I followed your suggestion and it works... Thanks

Hi. You're welcome. Glad to hear you got it sorted out. Good luck with your project.

 

[MajP]

But looking at all the code involved in your solution I tried to make it a bit simpler.

No need to look at the code, just need to use it. I build reusable "black box" solutions. I expect that user does not need to touch the code, since it is all encapsulated. It is like a watch, as long as you can wind it and set the time and date you really should not care how the clutch and balance wheel work.

So with my solution, any time you want to use it regardless of the source it is 7 lines of code in the main form. If you type in the sql statements you could get it down to 3 lines of code. I would say that this is less complicated than your code and it has far more features.

 Private Sub Form_Load()
  Set FormFromTo = Me.frmFromToList.Form
  Dim FromSql As String
  Dim ToSql As String
  FromSql = "SELECT ID, ..."
  ToSql = "SELECT ID, ..."
  FormFromTo.FTL_InitializeFromTo FromSql, ToSql, "Employees Not Selected", "Employees Selected"
End Sub

This provides all the button events as well.

But if building a one time solution, your solution should work well and be efficient. My code gives up efficiency for reusability.

Here is my code for your solution

Option Compare Database
Option Explicit
Private WithEvents ftl As Form_FromToList
'**************************************************************************************************************
'----------------------------------------Code To Use To From --------------------------------------------------
'**************************************************************************************************************
Private Sub Form_Load()
  Dim ToSQL As String
  Dim FromSQL As String
  Set ftl = Me.FromToList.Form
  FromSQL = "SELECT ID_Giocatore,[NomeCompleto] FROM qrySeniorGiaInvitati ORDER BY [NomeCompleto]"
  ToSQL = "SELECT ID_Giocatore, NomeCompleto FROM qrySeniorDaInvitare ORDER BY NomeCompleto"
  ftl.FTL_InitializeFromTo FromSQL, ToSQL, "Già invitato alla sezione", "Giocatori da invitare"
End Sub
**************************************************************************************************************
'----------------------------------Code to Update Tables   --------------------------------------------------
'**************************************************************************************************************
Private Sub ftl_ItemFromAdded(Item As Variant)
  Dim strSql As String
  strSql = "Update tblGiocatori SET Invitato_Senior = True WHERE ID_Giocatore = " & Item
  CurrentDb.Execute strSql
End Sub

Private Sub ftl_ItemToAdded(Item As Variant)
  Dim strSql As String
  strSql = "Update tblGiocatori SET Invitato_Senior = False WHERE ID_Giocatore = " & Item
  CurrentDb.Execute strSql
End Sub

 

[Valentino54]

Many thanks!! It's amazing!! It works great!

I understand that being a novice there is a lot to be learned!

There is just one more thing... I tried to change the label on the left list but it did not work. Is there any way to do it?

Sorry, I just fixed it! Slowly slowly I begin to understand the mechanisms...

Stay safe at home,
Valentino

 

[MajP]

You gave me a good idea to make this also able to work with two queries (included query, not included). I wrote it the way I did for max flexibility, but I can give the option to be query based. That would be more efficient and faster, but still provide the simplicity and features. I will provide an update later.

 

[Valentino54]

Hi, I just noted that in your code there might be a bug.

By clicking on a name on the left listbox I’m able to scroll up and down with the arrows. On the right side instead, by tapping the arrows the cursor jumps back to the left side.

It might also be useful to make multiselection with the control key...

Good evening,
Valentino

 

[MajP]

See if this is better. The multiselect code gets real confusing and it is hard to make it work well. See if the single click option would suffice. And check the behavior with the up and down arrows..

 

[dgreen]

Any thoughts on putting a find as you type on the left list box to help locate a record in a long list?

See if this is better. The multiselect code gets real confusing and it is hard to make it work well. See if the single click option would suffice. And check the behavior with the up and down arrows..

 

[Valentino54]

Dont worry about the multiselection... It was just a suggestion... The more you get, the more you want!

The arrows still do not work on the right listbox, but never mind, I can work with it anyway. It's just a nice to have.

Many thanks for help and for your time. I really appreciated it.

Stay safe,
Valentino

 

[Valentino54]

Any thoughts on putting a find as you type on the left list box to help locate a record in a long list?

It works already by tiping the name's initial...

 

[arnelgp]

there is also somewhat similar approach on this book:

Microsoft Access Solutions: Tips, Tricks and Secrets from Microsoft Access MVPs, Tip #9 Paired Listboxes

 

[MajP]

The arrows still do not work on the right listbox, but never mind, I can work with it anyway. It's just a nice to have.

I think the arrow work, but when you have items selected in both listboxes click on the list box you want (right side), that will unselect the other list. This was a trade off. When you move something to a list I select that item so you see where it moved. Now you have two items selected. I could turn this off so there is only one item selected, but then moving the next item is harder.