Update a listbox based on two comboboxes values in another form

R

richardw

Registered User.
Local time
Today, 06:42
Joined
Feb 18, 2016
Messages
48
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 :):D
 
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
 
what values in your listbox need to be updated. do you need to add new items. from where to we get the values?
 
Hi CJ

I tired this code but i didn't work
Code: 
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
 
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
 
Code: 
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):

Code: 
Private Sub Form_Open(Cancel As Integer)
    Dim strSource As String
    strSource = Me.OpenArgs & ""
    If strSource <> "" Then Me.lbo_actuals.RowSource = strSource
End Sub
 
I tired this code but i didn't work
Click to expand...
sorry, misread your post
looks like other have come up with alternative solutions
 
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.
Click to expand...
just for this part of code :
DoCmd.OpenForm stDocName, , , , , , strSQL
Click to expand...
 
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];
Click to expand...
 
stDocName is a string variable for the name of your form.

Private Sub cmdApply_Click()
const stDocName As String = "yourFormNameHere"
'
' rest of code
 
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 :

Code: 
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

Code: 
Private Sub Form_Open(Cancel As Integer)
 
    Dim strSource As String
    strSource = Me.OpenArgs & ""
 
    If strSource <> "" Then Me.lboActuals.RowSource = strSource
 
End Sub
 
please post rowsource of your 2 combobox.
 
-- For cboFilterTeam (The source is a Query)

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

-- For cboFilterPeriod (Typed manually)

Code: 
1;2;3;4;5;6;7;8;9;10;11;12
 
your field, MonthActual, is it numeric?
 
Yes! It's numeric and it represents months from 1 to 12
 
Code: 
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
 
I'm really thnaking you arnelgp for your time but it still don't filter data ..
 
sorry

Code: 
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
 
Not even with this.. I don't know what could be the reason :(
 
is it possible for you to upload your db?
 
You must log in or register to reply here.

Similar threads

D
Solved Cannot Assign ActiveControl/ Screen.ActiveControl
Replies
8
Views
457
isladogs
isladogs
S
How to programmatically change the currently selected row in a continuous form / listbox
Replies
5
Views
891
theDBguy
theDBguy
N
Solved How to scan a barcode from a parent form and update the child/subform in MS access
Replies
3
Views
658
Mark_
M
S
Solved Multiple listboxes on main form
Replies
6
Views
742
soupy8728
S
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
1,863
Faoineag
F

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom