How do I code a command button

[Michael Adams]

I have a form that is attached to a query. I have a drop down combo box that has employees, a text box that should do a count of "incidents" from the Type field in the query when I click the count button. what is the code needed for this to work?

[jbt007]

@Michael Adams
I would look into using myCount = DCount([What], [Where], [Criteria]). Then set the textbox value to myCount. Put the code on the button's "On Click" event.

[Michael Adams]

OK, maybe I am not doing this right. I entered the code you told me to on the buttons "OnClick" then in the text box default value I set to myCount. When I click the button I get this error "The expression On Click you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'mycount'.

[jbt007]

@Michael Adams
Michael,
Sorry, I'll be more specific. Here's the code for the on click event:

Private Sub cmdGetCount_Click()
    Dim myCount As Integer
    myCount = DCount("[fldNameToCount]", "tableName")
    Me.txtMyCount.Value = myCount
    Me.Refresh
End Sub

The code above assumes you have a textbox on the form named "txtMyCount" and a command button on the form named "cmdGetCount". It also assumes your table is in the same database as the form. You will need to substitute your field name into "fldNameToCount", leaving the brackets [], and substitute your table name in place of "tableName". If your text box and command buttons are named differently, you will also need to make appropriate substitutions.

For more details about "DCount" click on the word "DCount" in the VBA Editor and hit F1.

Hope this helps...

[Michael Adams]

That works out great, now I would like to separate the count according to the employee in the field "Who"

[jbt007]

What you want to use is the "criteria" part of the DCount. It can be kind of problematic if depending on what you are counting. If you are counting employee's names DCount will count all employees with the same name. (John Smith - for example may be several different people.) It's best if you can count an employee ID filed so you are sure you are only counting entries for a specific employee.

Having said that, (And because you haven't posted any specific information about your table structure or field names) I'll give an example on how to count an employee's name matching a form's field.

DCount("[fldToCount]", "TableName", _
   "[EmployeeID] = '" & Me.cmbxEmpID.Value & "'")

The above is air code so you may need to tweak it a bit. The idea is that the end result Access is looking for is "DCount(... EmployeeID = 'THO2314'). So if a person used ComboBox cmbxEmpID to pick the ID "THO2314" and then clicked the count command button, you would only count records where the EmployeeID field was "THO2314".

You should make allowance for what if the combo box is never clicked?

Do this by setting a default value "All" and then use an If, then, else statement that evaluates the value of the combobox before you try to count the data. If it has "All" selected, do the DCount without any criteria, otherwise, use the combobox value to filter the data.

Another way would be to disable the "Count Data" button until the combobox is updated. On the "Update" event of the combo box, put code that enables the command button.

Private Sub cmbxEmpID_Update()
    Me.CmdCountEmpID.Enabled = True
End Sub

Hope this helps...

[nico5038]

Hmm, guess your form query hold both employees and incidents.
When you want a count of the incidents from the Type field per employee you can create a GroupBy query like:

select employeeID, sum(Type) from qryYourForm group by employeeID
 

Now you can add a subform for this query and accept the wizard option to link the subform.
Access will now synchronize the subform with the employee of the mainform.

Getting the idea ?

Nic;o)

[jbt007]

@nico5038
Nic;o) - Another way to skin the cat... I wasn't sure if he wanted to change his form or not...

[Michael Adams]

There is still one problem, I have a query with the following fields: employee (this is the long name), CUDate(this is the date entered) WorkOrderNumber, type(this is the type of Work Order), WorkedBy (this is the intials of the employee).

I have used this code to list the count of three different types into text boxes.

Private Sub Sub_Cmd_Click()
    Dim typeCount As Integer
    typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type]= 'Incident'")
    Me.Inc_Text.Value = typeCount
    Me.Refresh
    typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type]= 'Request'")
    Me.Req_Text.Value = typeCount
    Me.Refresh
    typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type]= 'Change Order'")
    Me.CO_Text.Value = typeCount
    Me.Refresh
End Sub

the problem that I am running into is that before this code I need to sort the query by the field "workedby" then separte the count into the three fields listed in the above code.

What would I code first? I have tried

[Private Sub Sub02cmd_Click()
    Dim empcount As Integer
    empcount = DCount("[WorkedBy]", "Emp_Lst_Frm_Query", "[workedby] = '" & Me.empcmb.Value & "'")
    Me.Sub02txt.Value = empcount
    Me.Refresh
End Sub]

But it errors out. The code is correct until it gets to the criteria part of it. by the way, I am getting the data in empcmb from the table employee with the field name of initials.

What am I doing wrong?

[nico5038]

The easy way would be a group by query as posted before, like:

select workedby, Type, Count(*) 
from Emp_Lst_Frm_Query
group by workedby, Type
orde by 1, 2
 

Using this new query in a subform linked with the workedby field to the mainform based on Emp_Lst_Frm_Query, will give the three needed counts. When another Type is added, this won't effect your code, as the groupby will handle this :-)

Nic;o)

[jbt007]

@Michael Adams
Michael,

Your code looks ok from what I can see. Try some of the following:

1. What's the error message?
2. Are you sure the value in Me.empcmb combo box is in the db?
3. The integer type only goes up to about 32k, if there are more than 32k records try "Dim empcount As Long".
4. All the "D" functions can be tested in the immediate window. Place a pause in your VBA code on the DCount line and then run the form. When you click the Sub02cmd button the program will stop on that line. You may then type:
   

   ?DCount("[WorkedBy]", "Emp_Lst_Frm_Query", "[workedby] = '" & Me.empcmb.Value & "'")

   in the immediate window and it should tell you the count.
   
   You may also type:

   ?Me.empcmb.Value

   and hit enter to see what the value is in the combo box.
5. Try trimming both the field value and the combo box value. It could be they are not the same length. This would look like:
   

   DCount("[WorkedBy]", "Emp_Lst_Frm_Query", "Trim([workedby]) = '" & Trim(Me.empcmb.Value) & "'")

6. Finally - try buying VBA For Dummies by John Paul Mueller. Good basic book to get you started. I still refer to mine from time to time.

Hope this helps...

[Michael Adams]

Ok to answer your question, when I used the me.value the return was a number representing the line on which the Intials were stored in the table. I then changed that ot a me.empcmb.text and that returned the correct Intials of the combo box. However, what is happening is the query is returning the correct number of results regarding the .text but when I go to sort those results the code sorts the whole query not the sorted query based on the .text result.

Private Sub Sub02cmd_Click()
    Dim typeCount As Integer
    Dim empcount As Integer
 
    empcount = DCount("[WorkedBy]", "Emp_Lst_Frm_Query", "[WorkedBy]= ' " & Me.empcmb.Value & " '")
    Me.Inc_Text.Value = empcount
    Me.Refresh
 
empcmb=typeCount
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type]= 'Incident'")
    Me.Inc_Text.Value = typeCount
    Me.Refresh
End Sub
 

The net count should be 7; however, the code returns 8 because that is the total in the query.

[jbt007]

@Michael Adams
So list for me the objects on your form and what they are. From the name empcmb I was assuming this was a combo box. Is that true or is it a text box?

I am not following line 9 in your code: "empcmb=typeCount". What are you doing here?

As far as I know, sorting a query has no effect on the number of items in it. Why would your sort make a difference? I am not following this either. How are you sorting the query? Post the SQL statement for Emp_Lst_Frm_Query. (From the edit mode, click View, SQL.)

[Michael Adams]

I finally got it to work the way I wanted to. I ended up using a case statement based on the value of [workedby] = me.empcmb.value. Here is the final code for the button.

    Select Case typeCount = DCount("[workedby]", "Emp_Lst_Frm_Query", "[workedby]= '" & Me.empcmb.Value & "'")
    Case Me.empcmb.Value = "1"
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'MJA'")
        Me.TotalTxt.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'MJA'")
        Me.Inc_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'MJA'")
        Me.Req_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'MJA'")
        Me.CO_Text.Value = typeCount
        Me.Repaint
    Case Me.empcmb.Value = "2"
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'MOB'")
        Me.TotalTxt.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'MOB'")
        Me.Inc_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'MOB'")
        Me.Req_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'MOB'")
        Me.CO_Text.Value = typeCount
        Me.Repaint
    Case Else
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'SLT'")
        Me.TotalTxt.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'SLT'")
        Me.Inc_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'SLT'")
        Me.Req_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'SLT'")
        Me.CO_Text.Value = typeCount
        Me.Repaint
    End Select

[jbt007]

@Michael Adams
Great! Hope I helped...

[Michael Adams]

Everyone helped out great. Thanks so much for all the replies.