Update a listbox based on two comboboxes values in another form (1 Viewer)

[richardw]

Hello all,

I'm trying to open a form (frm_viewActuals) that contains a listbox (lbo_actuals) base on a two comboboxes (cbo_teamName & cbo_period) values in another form called (frm_filterActuals).

What i'd like to do is to update the listbox in frm_viewActuals after clicking "Apply" button in the frm_filterActuals form.

Does anyone have a solution for this ?

Thanks in advance

 

[CJ_London]

you would need some code in your 'apply' button click event along the following lines

lbo_actuals.rowsource=" SELECT * FROM myTable WHERE onefield=" & forms!frm_filterActuals!somefield & " AND anotherfield=" & frm_filterActuals!someotherfield

 

[arnelgp]

what values in your listbox need to be updated. do you need to add new items. from where to we get the values?

 

[richardw]

Hi CJ

I tired this code but i didn't work

Private Sub cmdApply_Click()
 
    DoCmd.OpenForm stDocName, , , lbo_actuals.RowSource = " SELECT * FROM tbl_temp WHERE teamName=" & Forms!frm_filterActuals!cboteamFilter & " AND actualMonth=" & frm_filterActuals!cboFilterPeriod
     
End Sub

 

[richardw]

Hi arnelgp,

For the listbox, it's already linked to a table. And when opened, it shows the values not filtered.

For the combobox, the values are from a query and i want to show the listbox in the other form based on the combobox selection.

Many thanks

 

[arnelgp]

Private Sub cmdApply_Click()
 
dim var as Variant
dim strTeam As String
dim strMonth As String
Dim strFilter As String
Dim strSQL As String
For Each var In Frm_FilterActuals!CboTeamFilter.ItemsSelected
    strTeam = strTeam & Frm_FilterActuals!CboTeamFilter.Column([COLOR=Blue]0[/COLOR], var) & ","
Next var
For Each var In Frm_FilterActuals!cboFilterPeriod.ItemsSelected
    strMonth = strMonth & Frm_FilterActuals!cboFilterPeriod.Column([COLOR=Blue]0[/COLOR], var) & ","
Next var
If strTeam <> "" Then 
    StrTeam = "[TeamName] IN (" & Left(strFilter, Len(strFilter)-1) & ")"
    strFilter = " WHERE " & strTeam
End If
If strMonth <> "" Then
    strMonth = "[ActualMonth] IN (" & Left(strMonth, Len(strMonth)-1) & ")"
    If strFilter <> "" Then
        strFilter = strFilter & " AND " & strMonth
    Else
        strFilter = "WHERE " & strMonth
    End If
End If
strSQL = "SELECT * FROM tbl_temp" & iif(strFilter <> "", strFilter, "") & ";"
DoCmd.OpenForm stDocName,,,,,,strSQL

End Sub

on your form's Open event (the form for strDocName):

Private Sub Form_Open(Cancel As Integer)
    Dim strSource As String
    strSource = Me.OpenArgs & ""
    If strSource <> "" Then Me.lbo_actuals.RowSource = strSource
End Sub

 

[CJ_London]

I tired this code but i didn't work

sorry, misread your post
looks like other have come up with alternative solutions

 

[richardw]

Thank you CJ any way.

Thank you @Arnelgp for your help bu i'm getting this error :

Run-Time error 2494 - The action or method requires a form Name argument.

just for this part of code :

DoCmd.OpenForm stDocName, , , , , , strSQL

 

[richardw]

This is the excat source of the listbox

SELECT [tbl_temp].[idTblTemp], [tbl_temp].[idInitiative], [tbl_temp].[initiativeName], [tbl_temp].[initiativeType], [tbl_temp].[teamName], [tbl_temp].[budgetYear], [tbl_temp].[initialValInt], [tbl_temp].[initialValExt], [tbl_temp].[initialValOth], [tbl_temp].[monthActual], [tbl_temp].[typeActual], [tbl_temp].[totalFTE], [tbl_temp].[actualFTE], [tbl_temp].[remainFTE], [tbl_temp].[averageFTE], [tbl_temp].[startMonth], [tbl_temp].[endMonth] FROM tbl_temp ORDER BY [initiativeName];

 

[arnelgp]

stDocName is a string variable for the name of your form.

Private Sub cmdApply_Click()
const stDocName As String = "yourFormNameHere"
'
' rest of code

 

[richardw]

Yes it's what I did .. Thank you
It opens the other form with the listbox but it doesn't filter data
here is the code as you suggested :

Private Sub cmdApply_Click()
 
Dim var As Variant
Dim strTeam As String
Dim strMonth As String
Dim strFilter As String
Dim strSQL As String
Const stDocName As String = "frm_viewActuals1"
 
For Each var In Forms.filterfrm_Actuals!cboFilterTeam.ItemsSelected
    strTeam = strTeam & filterfrm_Actuals!cboFilterTeam.Column(0, var) & ","
Next var
 For Each var In Forms.filterfrm_Actuals!cboFilterPeriod.ItemsSelected
    strMonth = strMonth & filterfrm_Actuals!cboFilterPeriod.Column(0, var) & ","
Next var
 If strTeam <> "" Then
    strTeam = "[teamName] IN (" & Left(strFilter, Len(strFilter) - 1) & ")"
    strFilter = " WHERE " & strTeam
End If
 If strMonth <> "" Then
    strMonth = "[monthActual] IN (" & Left(strMonth, Len(strMonth) - 1) & ")"
    
    If strFilter <> "" Then
        strFilter = strFilter & " AND " & strMonth
        
    Else
        strFilter = "WHERE " & strMonth
        
    End If
End If
 strSQL = "SELECT idTblTemp, idInitiative, initiativeName, initiativeType, teamName, budgetYear, initialValInt, initialValExt, initialValOth, monthActual, typeActual, totalFTE, actualFTE, remainFTE, averageFTE, startMonth, endMonth FROM tbl_temp" & IIf(strFilter <> "", strFilter, "") & ";"
DoCmd.OpenForm stDocName, , , , , , strSQL
 End Sub

I put also this code in frm_viewActuals1

Private Sub Form_Open(Cancel As Integer)
 
    Dim strSource As String
    strSource = Me.OpenArgs & ""
 
    If strSource <> "" Then Me.lboActuals.RowSource = strSource
 
End Sub

 

[arnelgp]

please post rowsource of your 2 combobox.

 

[richardw]

-- For cboFilterTeam (The source is a Query)

SELECT [rsel_teamElementary].[teamName] FROM rsel_teamElementary ORDER BY [teamName];

-- For cboFilterPeriod (Typed manually)

1;2;3;4;5;6;7;8;9;10;11;12

 

[arnelgp]

your field, MonthActual, is it numeric?

 

[richardw]

Yes! It's numeric and it represents months from 1 to 12

 

[arnelgp]

Private Sub cmdApply_Click()
 
Dim var As Variant
Dim strTeam As String
Dim strMonth As String
Dim strFilter As String

Const stDocName As String = "frm_viewActuals1"
 
For Each var In Forms.filterfrm_Actuals!cboFilterTeam.ItemsSelected
    strTeam = strTeam & Chr(34) & filterfrm_Actuals!cboFilterTeam.Column(0, var) & Chr(34) & ","
Next var
 For Each var In Forms.filterfrm_Actuals!cboFilterPeriod.ItemsSelected
    strMonth = strMonth & filterfrm_Actuals!cboFilterPeriod.Column(0, var) & ","
Next var
 If strTeam <> "" Then
    strTeam = "[teamName] IN (" & Left(strFilter, Len(strFilter) - 1) & ")"
    strFilter = " WHERE " & strTeam
End If
 If strMonth <> "" Then
    strMonth = "[monthActual] IN (" & Left(strMonth, Len(strMonth) - 1) & ")"
    
    If strFilter <> "" Then
        strFilter = strFilter & " AND " & strMonth
        
    Else
        strFilter = "WHERE " & strMonth
        
    End If
End If
DoCmd.OpenForm stDocName, , , , , , strFilter
 End Sub


Private Sub Form_Open(Cancel As Integer)
   Dim strSQL As String 
   Dim strWhere As String
    strSource = Me.OpenArgs & ""
strSQL = "SELECT [tbl_temp].[idTblTemp], [tbl_temp].[idInitiative], "
strSQL = strSQL & "[tbl_temp].[initiativeName], [tbl_temp].[initiativeType], " 
strSQL = strSQL & "[tbl_temp].[teamName], [tbl_temp].[budgetYear], "
strSQL = strSQL & "[tbl_temp].[initialValInt], [tbl_temp].[initialValExt], " 
strSQL = strSQL & "[tbl_temp].[initialValOth], [tbl_temp].[monthActual], "
strSQL = strSQL & "[tbl_temp].[typeActual], [tbl_temp].[totalFTE], "
strSQL = strSQL & [tbl_temp].[actualFTE], [tbl_temp].[remainFTE], "
strSQL = strSQL & "[tbl_temp].[averageFTE], [tbl_temp].[startMonth], "
strSQL = strSQL & "[tbl_temp].[endMonth] FROM tbl_temp"
 
    If strWhere <> "" Then 
        Me.lboActuals.RowSource = strSQL & " " & strWhere & " ORDER BY [initiativeName];"
    eLSE
        Me.lboActuals.RowSource = strSQL & " ORDER BY [initiativeName];"
    End If

 
End Sub

 

[richardw]

I'm really thnaking you arnelgp for your time but it still don't filter data ..

 

[arnelgp]

sorry

Private Sub Form_Open(Cancel As Integer)
   Dim strSQL As String 
   Dim strWhere As String
    [COLOR=Blue]strWhere[/COLOR] = Me.OpenArgs & ""
strSQL = "SELECT [tbl_temp].[idTblTemp], [tbl_temp].[idInitiative], "
strSQL = strSQL & "[tbl_temp].[initiativeName], [tbl_temp].[initiativeType], " 
strSQL = strSQL & "[tbl_temp].[teamName], [tbl_temp].[budgetYear], "
strSQL = strSQL & "[tbl_temp].[initialValInt], [tbl_temp].[initialValExt], " 
strSQL = strSQL & "[tbl_temp].[initialValOth], [tbl_temp].[monthActual], "
strSQL = strSQL & "[tbl_temp].[typeActual], [tbl_temp].[totalFTE], "
strSQL = strSQL & [tbl_temp].[actualFTE], [tbl_temp].[remainFTE], "
strSQL = strSQL & "[tbl_temp].[averageFTE], [tbl_temp].[startMonth], "
strSQL = strSQL & "[tbl_temp].[endMonth] FROM tbl_temp"
 
    If strWhere <> "" Then 
        Me.lboActuals.RowSource = strSQL & " " & strWhere & " ORDER BY [initiativeName];"
    eLSE
        Me.lboActuals.RowSource = strSQL & " ORDER BY [initiativeName];"
    End If

 
End Sub

 

[richardw]

Not even with this.. I don't know what could be the reason

 

[arnelgp]

is it possible for you to upload your db?