count table records based on selected items in combo box as condition using VBA code

[Coucou]

I have a combo box with three items that link to the field in a table. What i want is to count the the total table records when i select each of the items in combo box. Example, when i select "Employed" in the combo box then all the records with the status Employed will be counted.

[Seth Schrock]

You would use the Count(*) to count the records and then have the WHERE clause filter the results based on your selections. For example, if your field is EmployedStatus and your combo box was named MyCombo on a form named MyForm, your WHERE clause would look like this:

WHERE EmployedStatus = Forms!MyForm!MyCombo

[Coucou]

I am beginner with this, Could you be a little more detail of that, would be better if you can give lines of sample code with that. Thanks

[Seth Schrock]

To do that I need more information from you. What is the table structure for the table that you are searching in? What is the row source for the combo box? What have you tried and what were the results?

[zmbd]

Coucou:
There are also the domain functions and in this case, they may be suitable.
For what you want, open the form in design mode.
Right click the combobox that you have your list in and show the properties box.
One way: is to set the Dcount() function as the control source in a textbox on your form. (I'd probably use an IIF to keep an error message out of the box while the CBO is empty.)

Examples of expressions

Another method is to use vba:
Click on the events tab
Click on the after update event
Select the "code builder" option
This will open the VBA editor to your form's code module and insert the private sub key phrases.
You will then need to write your vba code...
In this case, as I said, the DCount() function should work (type it in the vba window, select, press [F1] for syntax) to return your record count and then you just need to display that information in a text box value or label caption


Which option is best depends on your form and database design, and the size of your dataset.

[Coucou]

I created a table Student(ID,Name,...,EmpStatus) where EmpStatus tells if a student is employed or unemployed. So I like to have a form with combo box so that i just need to select the combo box and then it returns with the total number of those who employed or unemployed. What i have tried but it didnt work was:

For i = 1 To CboEmpStatus.ListCount - 1
If CboStatus.Selected(i) = True Then
str = "select * from Student where EmpStatus=" & CboEmpStatus.Column(0, i)
Set db = CurrentDb
Set rst = db.OpenRecordset(str)
TxtCount = rst.RecordCount

[Seth Schrock]

It is possible to do it the way that you have setup (after a few errors are fixed), however there is an easier way. Using the Domain functions that Z suggested. Personally, I would have a message box pop up when you selected which one you wanted from the combo box. This way you could easily find out how many unemployed students you have without making any design changes. In the combo box's AfterUpdate event, I would put

Dim intCount as Integer
intCount = DCount("*", "Student", "EmpStatus = " & Me.CboEmpStatus)
MsgBox "Student Count: " & intCount

[zmbd]

In post #6 where you are using the RecordCount method. I can only assume that you are using the DAO record method; thus, you must do somthing along the lines of (please note: I am entering the following from memory therefor the syntax may be subject to error):

With rs
    .movelast
    .movefirst
    lngRecordCount = .recordcount
end with

It's a fluke. The recordcount will always return 1 if there is at least 1 record; however, until you move to the last record and then back to the first record you will not receive the true count.

Why do this the hard way with the record set?

[Coucou]

Well tried with dcount function but it worked well with the StudID field but not the EmpStatus field which datatype is Text. Is it a problem with datatype?

[Seth Schrock]

There isn't any issue with the text data type, but you should look at the combo box to see if the field that it is bound to is a number field or a text field. If you look at the row source, it should be the name of a table, query, or it could have just some SQL code (which would start with SELECT). If the first field in the row source is a number field, then we would need to change the DCount() function to look at the text column instead of the number column of the textbox. Line 2 of my code block in post 7 would end with

Me.CboEmpStatus.Column(1)

The column numbers start at 0, so the second column has an index of 1.

[Coucou]

Finally i got it now! Thanks for your help on this!

[Seth Schrock]

Glad to be able to help.