populate Count textbox based on drop-down list (1 Viewer)

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
Hi,

I have 4 dropdown fields in a continuous form with a search btn.
Employee_Name
Company_Name
Status
Type

How can i amend the 'employee text box count' to display the count per employee_name values being selected.
instead of hardcoding like below

=Count(IIf([Employee_Name]="John",0))
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
=Count(IIf([Employee_Name]= [EmployeeCombo], 0))
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
sorry, you should do it in the combo's
AfterUpdate event.

remove the expression ("=count(...") from the textbox.

if your employeeCombo has only Employee_Name on it:

Code:
Private Sub combo_AfterUpdate()
Me.UnboundTextbox = Nz( DCount("1", "tableToLookup", "Employee_Name='" & Me.combo & "'")
End Sub

if your employeeCombo has EmployeeID included, ie:

"select EmployeeID, Employee_Name From ...", then:
Code:
Private Sub combo_AfterUpdate()
Me.UnboundTextbox = Nz( DCount("1", "tableToLookup", "Employee_Name='" & Me.combo.Column(1) & "'")
End Sub
 

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
hi,
the combo will have only employee name.
I've tried below code but the count is 0 instead of actual count.

What have i missed out?

Private Sub Combo303_AfterUpdate()
Me.Txtempl = Nz(DCount("1", "tbl_empl", "employee_name='" & Me.Combo303.Column(1) & "'"))

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
you dont need to use the column if only names are on the list:


Private Sub Combo303_AfterUpdate()
Me.Txtempl = Nz(DCount("1", "tbl_empl", "employee_name='" & Me.Combo303 & "'"))

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
ur welcome.
 

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
btw how to do the count based on multiple dropdown values selected..

Employee_Name
Company_Name
Status
Type
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
Private Sub Combo303_AfterUpdate()
Call subCount
End Sub

Private Sub ComboCompanyName_AfterUpdate()
Call subCount
End Sub

Private Sub ComboStatus_AfterUpdate()
Call subCount
End Sub

Private Sub ComboType_AfterUpdate()
Call subCount
End Sub

Private Sub subCount()
Dim strCriteria As Variant
strCriteria = Null
If Trim(Me.Combo303 & "")<>"" Then
strCriteria = (strCriteria + " And ") & "employee_name='" & Me.Combo303 & "'"
End If
If Trim(Me.ComboCompany & "") <> "" Then
strCriteria = (strCriteria + " And ") & "Company_Name='" & Me.comboCompany & "'"
End If
If Trim(Me.comboStatus & "")<>"" Then
strCriteria = (strCriteria + " And ") & "Status ='" & Me.comboStatus & "'"
End If
If Trim(Me.comboType & "")<>"" Then
strCriteria = (strCriteria + " And ") & "Type ='" & Me.comboType & "'"
End If

Me.Txtempl = Nz(DCount("1", "tbl_empl", strCriteria), 0)
End Sub
 
Last edited:

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
Hi,

One small question & help pls...
The overall count is working fine. :)

However, i need to display an individual count per status next to these combos.
I created 3 textboxes for each status (ON-HOLD, WIP, CPL)

If i select employee_name e.g John and other combo-box null then display
His overall status is 35
ON-HOLD 10
WIP 10
CPL 15

When no combo box values are selected then the individal count should be the total per status
But if i make a combination of the combox box (employee_name, company_name, status, type) then the individual count should be updated respectively.
Employee_name John, company_name: company_test
ON-HOLD 1
WIP 0
CPL 2

How can i amend the code to do that pls?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
plz post all the code you have so far.
 

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
for the summarised count per status
I've created the 3 textbox and added code in label. but its that when click on clear button.. we cant clear as it has this formula.

ON-HOLD in textbox added >> =Count(IIf([status]="ON-HOLD",0))
WIP in textbox added >> =Count(IIf([status]="WIP",0))
CPL in textbox added >> =Count(IIf([status]="CPL",0))


when combos Employee_Name, Company_Name, Status,Type is null and click on stats button then the status textbox get loaded.

Is it possible to amend the previous coding for the subCount

Sorry im not much into coding..just an amateur trying to develop an apps.
Test of DB atached.
 

Attachments

  • test.zip
    701.1 KB · Views: 44
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
the prevous code i gave doest it work, are u using it.
if you are using it can you post the code sa i can add thise other textboxes.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
you did not post the code you have on subCount, so you have
do modify it to match the controlname you have.
Code:
Private Sub subCount()
    Dim strCriteria, strCriteria2
    strCriteria = Null
    strCriteria2 = Null
    If Trim(Me.Combo303 & "") <> "" Then
        strCriteria = (strCriteria + " And ") & "employee_name='" & Me.Combo303 & "'"
        strCriteria2 = (strCriteria2 + " And ") & "employee_name='" & Me.Combo303 & "'"
    End If
    If Trim(Me.comboCompany & "") <> "" Then
        strCriteria = (strCriteria + " And ") & "Company_Name='" & Me.comboCompany & "'"
        strCriteria2 = (strCriteria2 + " And ") & "Company_Name='" & Me.comboCompany & "'"
    End If
    If Trim(Me.comboStatus & "") <> "" Then
        strCriteria = (strCriteria + " And ") & "Status ='" & Me.comboStatus & "'"
    End If
    If Trim(Me.comboType & "") <> "" Then
        strCriteria = (strCriteria + " And ") & "Type ='" & Me.comboType & "'"
        strCriteria2 = (strCriteria2 + " And ") & "Type ='" & Me.comboType & "'"
    End If
    If strCriteria & "" = "" Then
        Me.Txtemp1 = 0
    Else
        Me.Txtempl = Nz(DCount("1", "tbl_empl", strCriteria), 0)
    End If
    If strCriteria2 & "" = "" Then
        Me![On-Hold] = 0
        Me![WIP] = 0
        Me![CPL] = 0
    Else
        Me![On-Hold] = strCriteria2 & " And [Status]='On-Hold'"
        Me![WIP] = 0 = strCriteria2 & " And [Status]='WIP'"
        Me![CPL] = 0 = strCriteria2 & " And [Status]='CPL'"
    End If
End Sub
 

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
Yes did use the previous provided code where total count working.
I've attached the zip DB instead of posting code
 

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
Getting 'Run-time error 2465' in below code Me![On-hold] which value expression should it actually has??

If strCriteria2 & "" = "" Then
Me![On-Hold] = 0
Else
Me![On-Hold] = strCriteria2 & " And [Status]='On-Hold'"
End If
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
try putting the correct textbox name.
 

akika

Registered User.
Local time
Today, 03:31
Joined
Aug 7, 2018
Messages
102
I did put the textbox name in the code

If strCriteria2 & "" = "" Then
Me![Text331] = 0
Else
Me![Text331] = strCriteria2 & " And [Status]='Pending'"


Text box value is below instead of count:
Emp_name='John Smith' And [Status]='Pending'

anyother amendment to be done?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,169
here, i made mistake previously.
 

Attachments

  • Test.zip
    55.7 KB · Views: 57

Users who are viewing this thread

Top Bottom