Sum function in controlsource doesn't!

[aristos]

Hello, all.

Here's our situation:
We have a subform that has:
1. Form header
2. Details
3. Form footer

Details is set as continuous forms. It has a field called "H1" that defaults via the form controls to 0.

In the footer we have a control called "TotalH1" whose controlsource is set to "=sum([H1])".

When we open the main form, we create a temporary table via SQL that is called "tmpAssignments". We then assign the recordsource of the subform to this temporary table.

When the form opens, TotalH1 shows as #Error!
NO changes to the value of H1 cause the total field to show correctly.

So far as we can tell, there are no null values in the H1 field.

Can anyone help?

Thanks in advance! You all are great

Mike

 

[HNA]

Hi,

Does H1 Suming Other Field's, If Yes U Have To Have ERROR To Sum Suming Field.
Other Thing, What Kind Of SQL Tmp Table U Have Created.

HNA Do It Well, Or Don't.

 

[aristos]

H1 is linked to a table that is created with the following code:

...
strSQL = "CREATE TABLE tmpAssignments ("

For i = 1 To 12
strSQL = strSQL & "H" & Trim(Str(i)) & " INTEGER CONSTRAINT H" & Trim(Str(i)) & "_C NOT NULL, "
Next

For i = 1 To 12
strSQL = strSQL & "R" & Trim(Str(i)) & " CURRENCY CONSTRAINT R" & Trim(Str(i)) & "_C NOT NULL, "
Next

strSQL = strSQL & "StaffID TEXT CONSTRAINT StaffID_C NOT NULL UNIQUE , "
strSQL = strSQL & "JobID TEXT ) "

DoCmd.RunSQL strSQL
...

H1 does not do any summing - it just takes a numeric value.

Mike