Totals Row

Background: database is simple and controlled by one form with multiple tabs that include subforms under each tab.

I have a totals row in a subform that counts the number of rows above it in order to tell me how many times something has happened to an employee (given an award, punished, etc.)

I would like a way for that total to show in a text box on another of the forms tabs. So how do I get a total from a datasheet to a text box? I need it to do this for each employee record when the employee is selected from the drop down menu controling the form. (this part already works for the other fields)
Seems like it should be easy but I have had no luck yet. Can someone either help me with this or point me in the right direction to speed up my journey.

re: Totals Row

If you haven't done so already, create a textbox on the main form where you want the totals to appear. In the control source for this text box, type in the following:
Replace [Name of Subform] with the actual name of your subform and replace [Name of Field on Subform with Total] with the actual name of the text box that holds the value on your subform.

I don't think you have to worry about the tabs as long as you reference the subform correctly. Just make sure the text box is on the tab you want it on.

re: Totals Row

I am not referencing a field on the subform, I am referencing a totals row on the datasheet. I basically need to populate the textbox with the number of rows on the subform table.

re: Totals Row

Ok, I was over-complicating this by trying to pull from the total row in the datasheet. Here I am with one line of VB code and I am a single step away from where I need to be. What I have is below and it populates the textbox with the total number of awards given to all records. Now I need to limit it only to the record that is open.

Private Sub Form_Current()
'Populates the totalawards field with the number
'of awards received by the employee
Me.txtTotalAwards.Value = DCount("awardID", "tblawards")
End Sub

re: Totals Row

Hi Intel,

I'm sorry about that. I misunderstood what you were asking.

re: Totals Row

That's ok, happens to the best of us and it is hard to diagnose over a forum. I am still working on getting the VB code to only pull the active record though.

re: Totals Row

It is still not limiting the results to one employee, anyone else with any ideas?

re: Totals Row

nevermind, found another thread that covered record counts in subforms.