I tried the code on the attached database but the list box from where I am picking up values i.e ListNOFLE is a cascading list box and has a row source assigned from another listbox called ListCountry. I want to keep the structure of ListNOFLE intact and only add the end results after the cascade to ListYesItems.
This is the code :
Code:
Option Compare Database
Private Sub cmdAddAll_Click()
Call ChangeStatus(0, 2)
Me.ListNOFLE.SetFocus
Call ToggleButtons
End Sub
Private Sub cmdAddOne_Click()
Call ChangeStatus(CLng(Me.ListNOFLE), 1)
Call ToggleButtons
End Sub
Private Sub CmdApplyFilterLE_Click()
Dim strCountry As String
Dim strFilter As String
Dim varItem As Variant
Dim strYears As String
Dim strNOF As String
Dim strPhase As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "CostForecast_LargeEntity") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Country list box
For Each varItem In Me.ListCountry.ItemsSelected
strCountry = strCountry & ",'" & Me.ListCountry.ItemData(varItem) _
& "'"
Next varItem
If Len(strCountry) = 0 Then
strCountry = "Like '*'"
Else
strCountry = Right(strCountry, Len(strCountry) - 1)
strCountry = "IN(" & strCountry & ")"
End If
'Build criteria string from lstyears listbox
For Each varItem In Me.ListYearsLE.ItemsSelected
strYears = strYears & ",'" & Me.ListYearsLE.ItemData(varItem) _
& "'"
Next varItem
If Len(strYears) = 0 Then
strYears = "Like '*'"
Else
strYears = Right(strYears, Len(strYears) - 1)
strYears = "IN(" & strYears & ")"
End If
'Build criteria string from lstphases listbox
For Each varItem In Me.ListPhasesLE.ItemsSelected
strPhase = strPhase & ",'" & Me.ListPhasesLE.ItemData(varItem) _
& "'"
Next varItem
If Len(strPhase) = 0 Then
strPhase = "Like '*'"
Else
strPhase = Right(strPhase, Len(strPhase) - 1)
strPhase = "IN(" & strPhase & ")"
End If
'Build criteria string from lstNatureOfFees listbox
For Each varItem In Me.ListNOFLE.ItemsSelected
strNOF = strNOF & ",'" & Me.ListNOFLE.ItemData(varItem) _
& "'"
Next varItem
If Len(strNOF) = 0 Then
strNOF = "Like '*'"
Else
strNOF = Right(strNOF, Len(strNOF) - 1)
strNOF = "IN(" & strNOF & ")"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Country] " & strCountry & " AND [Years_Word] " & strYears & "AND [Nature_of_Fees] " & strNOF & _
"AND [Phase _of_Fees] " & strPhase
' Apply the filter and switch it on
With Reports![CostForecast_LargeEntity]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Private Sub cmdRemoveAll_Click()
Call ChangeStatus(0, 4)
Me.ListNOFLE.SetFocus
Call ToggleButtons
End Sub
Private Sub CMdRemoveFilterLE_Click()
On Error Resume Next
' Switch the filter off
Reports![CostForecast_LargeEntity].FilterOn = False
End Sub
Private Sub cmdRemoveOne_Click()
If IsNull(Me.ListYesItems) Then
MsgBox "You must select an item to move first.", vbExclamation + vbOKOnly, "No selection made"
Exit Sub
End If
Call ChangeStatus(CLng(Me.ListYesItems), 3)
Call ToggleButtons
End Sub
Private Sub Form_Load()
Me.ListYesItems.RowSource = Me.ListYesItems.Tag
Call ToggleButtons
End Sub
Private Sub ListCountry_AfterUpdate()
Dim varItem As Variant
Dim strSql As String
If Me.ListCountry.ItemsSelected.Count Then
strSql = "Select ID_Number, Nature_of_Fees from TotalCostsLargeEntity where [Country] in ('"
For Each varItem In Me.ListCountry.ItemsSelected
strSql = strSql & Me.ListCountry.ItemData(varItem) & "','"
Next varItem
strSql = Left(strSql, Len(strSql) - 2) & ") ORDER BY ID_Number"
Else
strSql = "Select ID_Number, Nature_of_Fees from TotalCostsLargeEntity ORDER BY ID_Number"
End If
Me.ListNOFLE.RowSource = strSql
End Sub
Sub ToggleButtons()
On Error Resume Next
If Me.ListYesItems.ListCount = 0 Then
Me.ListNOFLE.SetFocus
End If
If Me.ListNOFLE.ListCount = 0 Then
Me.ListYesItems.SetFocus
End If
Me.cmdAddOne.Enabled = False
Me.cmdAddAll.Enabled = False
Me.cmdRemoveOne.Enabled = False
Me.cmdRemoveAll.Enabled = False
If Me.ListYesItems.ListCount > 0 Then
Me.cmdRemoveOne.Enabled = True
Me.cmdRemoveAll.Enabled = True
End If
If Me.ListNOFLE.ListCount > 0 Then
Me.cmdAddOne.Enabled = True
Me.cmdAddAll.Enabled = True
End If
End Sub
I need to change the instructions on the Form load I guess. Please help!
I use these type of controls often. I have two listboxes and want to move things from one to another. I call this a From To List. Unfortunately, unlike other languages you cannot build your own user controls. In something like .net you could lump a few controls together and add custom properties, methods, and events and reuse it as a user control. In access the closest is to save it as a subform. So I built a form that has all of these controls and I reuse it all the time. I just have to pass in the sql for the left listbox (from list) and potentially the right listbox (the To list). All of the code for moving is in the subform, and there should be no need ever to modify that code. You can reformat the controls as needed. Then you can choose to do something whenever something moves from list to list, or you can wait till the end and get the list of things in the From list and To List. What you do with that information, has nothing to do with this code. Could be an update query, insert query, print command, etc.
I have two identical examples for Selecting Employees. One does something whenever an employee moves from list to list, the other only does something at the end. Again this is completely agnostic and has nothing to do with the subform controls. The subform simply allows you to move anything between two lists and tells you what got moved or what is in the list.
So here is an example doing something at the end. And that is basically all the code needed to reuse this.
Code:
Option Compare Database
Option Explicit
Public WithEvents FormFromTo As Form_FromToList
Private Sub cmdOk_Click()
UpdateTableData
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Load()
Set FormFromTo = Me.frmFromToList.Form
Dim FromSql As String
Dim ToSql As String
'Establish starting list. You may have nothing in the To list
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]"
'Initialize the Class
FormFromTo.FTL_InitializeFromTo FromSql, ToSql, "Employees Not Selected", "Employees Selected"
End Sub
Private Sub cmdApply_Click()
UpdateTableData
End Sub
Public Sub UpdateTableData()
Dim EmpIDs As Collection
Dim EmpID As Long
Dim I As Integer
Dim strSql As String
Set EmpIDs = FormFromTo.FromItems
'If it is in the From box it is not selected so update the table
For I = 1 To EmpIDs.Count
strSql = "Delete * from tblSelectedEmployees where SelectedemployeeID = " & EmpIDs(I)
CurrentDb.Execute strSql
Next I
'If it is in the to box it is selected so update the table
Set EmpIDs = FormFromTo.ToItems
For I = 1 To EmpIDs.Count
strSql = "Insert into tblSelectedEmployees (SelectedEmployeeID) Values (" & EmpIDs(I) & ")"
CurrentDb.Execute strSql
Next I
Me.subfrmSelectedEmployees.Requery
End Sub
The point is to write once and reuse whenever. If you do not want to use as a subform control, you could break it apart and reuse the code. However, that kind of defeats the point.
I use these type of controls often. I have two listboxes and want to move things from one to another. I call this a From To List. Unfortunately, unlike other languages you cannot build your own user controls. In something like .net you could lump a few controls together and add custom properties, methods, and events and reuse it as a user control. In access the closest is to save it as a subform. So I built a form that has all of these controls and I reuse it all the time. I just have to pass in the sql for the left listbox (from list) and potentially the right listbox (the To list). All of the code for moving is in the subform, and there should be no need ever to modify that code. You can reformat the controls as needed. Then you can choose to do something whenever something moves from list to list, or you can wait till the end and get the list of things in the From list and To List. What you do with that information, has nothing to do with this code. Could be an update query, insert query, print command, etc.
I have two identical examples for Selecting Employees. One does something whenever an employee moves from list to list, the other only does something at the end. Again this is completely agnostic and has nothing to do with the subform controls. The subform simply allows you to move anything between two lists and tells you what got moved or what is in the list.
So here is an example doing something at the end. And that is basically all the code needed to reuse this.
Code:
Option Compare Database
Option Explicit
Public WithEvents FormFromTo As Form_FromToList
Private Sub cmdOk_Click()
UpdateTableData
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Load()
Set FormFromTo = Me.frmFromToList.Form
Dim FromSql As String
Dim ToSql As String
'Establish starting list. You may have nothing in the To list
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]"
'Initialize the Class
FormFromTo.FTL_InitializeFromTo FromSql, ToSql, "Employees Not Selected", "Employees Selected"
End Sub
Private Sub cmdApply_Click()
UpdateTableData
End Sub
Public Sub UpdateTableData()
Dim EmpIDs As Collection
Dim EmpID As Long
Dim I As Integer
Dim strSql As String
Set EmpIDs = FormFromTo.FromItems
'If it is in the From box it is not selected so update the table
For I = 1 To EmpIDs.Count
strSql = "Delete * from tblSelectedEmployees where SelectedemployeeID = " & EmpIDs(I)
CurrentDb.Execute strSql
Next I
'If it is in the to box it is selected so update the table
Set EmpIDs = FormFromTo.ToItems
For I = 1 To EmpIDs.Count
strSql = "Insert into tblSelectedEmployees (SelectedEmployeeID) Values (" & EmpIDs(I) & ")"
CurrentDb.Execute strSql
Next I
Me.subfrmSelectedEmployees.Requery
End Sub
The point is to write once and reuse whenever. If you do not want to use as a subform control, you could break it apart and reuse the code. However, that kind of defeats the point.
Thanks for the help! One more question, how do I ensure that the entries in the From list box stay Distinct?
I have tried this code to make that happen... but its not working out.
Code:
Private Sub ListCountry_AfterUpdate()
Dim varItem As Variant
Dim strSQL As String
If Me.ListCountry.ItemsSelected.Count Then
strSQL = "Select ID_Number, DISTINCT Nature_of_Fees from TotalCostsLargeEntity where [Country] in ('"
For Each varItem In Me.ListCountry.ItemsSelected
strSQL = strSQL & Me.ListCountry.ItemData(varItem) & "','"
Next varItem
strSQL = Left(strSQL, Len(strSQL) - 2) & ") ORDER BY ID_Number"
Else
strSQL = "Select ID_Number, DISTINCT Nature_of_Fees from TotalCostsLargeEntity ORDER BY ID_Number"
End If
Me.ListNOFLE.RowSource = strSQL
End Sub
Never mind. I solved it! I used an idea given by a user on this forum on a previous post!
I created a table with NatureofFeesId foreign key, created an update query and updated the code in the post above to select DISTINCT.
I use these type of controls often. I have two listboxes and want to move things from one to another. I call this a From To List. Unfortunately, unlike other languages you cannot build your own user controls. In something like .net you could lump a few controls together and add custom properties, methods, and events and reuse it as a user control. In access the closest is to save it as a subform. So I built a form that has all of these controls and I reuse it all the time. I just have to pass in the sql for the left listbox (from list) and potentially the right listbox (the To list). All of the code for moving is in the subform, and there should be no need ever to modify that code. You can reformat the controls as needed. Then you can choose to do something whenever something moves from list to list, or you can wait till the end and get the list of things in the From list and To List. What you do with that information, has nothing to do with this code. Could be an update query, insert query, print command, etc.
I have two identical examples for Selecting Employees. One does something whenever an employee moves from list to list, the other only does something at the end. Again this is completely agnostic and has nothing to do with the subform controls. The subform simply allows you to move anything between two lists and tells you what got moved or what is in the list.
So here is an example doing something at the end. And that is basically all the code needed to reuse this.
Code:
Option Compare Database
Option Explicit
Public WithEvents FormFromTo As Form_FromToList
Private Sub cmdOk_Click()
UpdateTableData
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Load()
Set FormFromTo = Me.frmFromToList.Form
Dim FromSql As String
Dim ToSql As String
'Establish starting list. You may have nothing in the To list
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]"
'Initialize the Class
FormFromTo.FTL_InitializeFromTo FromSql, ToSql, "Employees Not Selected", "Employees Selected"
End Sub
Private Sub cmdApply_Click()
UpdateTableData
End Sub
Public Sub UpdateTableData()
Dim EmpIDs As Collection
Dim EmpID As Long
Dim I As Integer
Dim strSql As String
Set EmpIDs = FormFromTo.FromItems
'If it is in the From box it is not selected so update the table
For I = 1 To EmpIDs.Count
strSql = "Delete * from tblSelectedEmployees where SelectedemployeeID = " & EmpIDs(I)
CurrentDb.Execute strSql
Next I
'If it is in the to box it is selected so update the table
Set EmpIDs = FormFromTo.ToItems
For I = 1 To EmpIDs.Count
strSql = "Insert into tblSelectedEmployees (SelectedEmployeeID) Values (" & EmpIDs(I) & ")"
CurrentDb.Execute strSql
Next I
Me.subfrmSelectedEmployees.Requery
End Sub
The point is to write once and reuse whenever. If you do not want to use as a subform control, you could break it apart and reuse the code. However, that kind of defeats the point.
Thank you so much for sharing this knowledge, I'm new to Microsoft Access and VBA. Just attempted using your steps in my work and it keeps returning user-defined not defined, I have tried gone through all the codes yet the same thing, when I now copied my own form to your application, it worked perfectly. please what can be done to correct this
