DSum in subform

[LaurieHamlin]

I'm new to VB in Access. I'm developing a payroll database. I need to total hours in a subform by the type (regular, overtime) and employee (social security number). I've gotten it to total by type, but it adds for all employees and when I add the social security criteria it no longer works.
The subform is 'Hours Worked with Description' and is based on a table named 'Hours Worked table'. The main form is 'Hours Worked' which is based on 'Employees'. This is what I have:
=DSum("[Hours]","Hours Worked table","[Forms]![Hours Worked table]![Social Security] = " & [Forms]![Hours Worked]![Social Security] " And [Hours Worked table]!

 = 'b' ")
I've tried changing the social security criteria everyway I can think of.  I'll also have to add a criteria for date so if anyone can tell me how to do all that, I'd appreciate it.  I hate syntax!

 

[ajdesalvo]

In the Where criteria of the DSum, you need just the field name from the table, not the form name.

Also, if [Forms]![Hours Worked]![Social Security] is a text field, then single quotes are needed:

=DSum("[Hours]","Hours Worked table","[Social Security] = '" & [Forms]![Hours Worked]![Social Security] "' And [Hours Worked table]!ÿ = 'b' &quot

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[LaurieHamlin]

When I make those changes Access 97 changes it back to what I had before. When I view the form I get '#Name' in that field so it has to be close. Everything is spelled correctly. Any other ideas?

 

[ajdesalvo]

Laurie,
Can you elaborate on:

"When I make those changes Access 97 changes it back to what I had before."

What is getting changed back?

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[LaurieHamlin]

I want this total to show in a text box. I right click on the text box and type the formula in Control Source. Usually it saves the changes I make. Sometimes, no matter how many times I change it or save it, it goes back to what was in there before. It seems to do this with certain changes, not just randomly, as if it doesn't like the change, not as though it's a bug or malfunction.

 

[ajdesalvo]

When it changes it back, it is because there is some kind of syntax error in the control source (even though you don't get a complaint).

One thing you could do is make the subform linked to the main form by Social Security. Click on the subform, and on the Data tab make the Master and Child links [Social Security]. Then as you change the main form's employee record, only their info will be in the subform.

Then your total controlsource would be; =DSum([Hours]) in the subform, which you can place in the subform's form footer.
Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[LaurieHamlin]

Syntax is exactly the problem. It's already a subform linked on social security. Even though only the records for that employee show up, it's totaling the hours for all employees. Now you may see why I'm frustrated. Any other ideas? The '#Name' usually means it can't find something I referenced, but everything is clearly identified.

 

[ajdesalvo]

#Name also could mean that the control name is a valid field name that you have something other than the field in the control source.

For example, in you recordsource a valid field is Total, and you create a text box called Total and in the control source you add a calculation. This will display #NAME in the form.

This may not be your problem but I just wanted to clarify this.

I will keep on thinking!


Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[LaurieHamlin]

I did it! I changed it to this:
Private Sub Form_Current()
Dim bTot As Integer
Dim SocSec As String
SocSec = Me.Form![Social Security]
If [Social Security] = SocSec Then
If

 = "b" Then
        bTot = bTot + [Hours]
    End If
End If
Me.b = bTot
End Sub
It's less efficient, but it works.  Thanks for all your help.