Add Total row to a Datasheet view programatically

[afh110]

Hi Guys,

This is straight forward Q. Im displaying a dynamic Datasheet in a subform with a dynamic Query feeding it. However, I need to show a TOTAL row to my datasheet view from code. Any ideas on this one?

thanks

[OBP]

Change it to a Subform in Continuous Forms Mode and add Totals in the Footer.

[afh110]

Hi OBP, Im not sure how to go about it can you please give me a bit more hints on how to do these changes ?

[OBP]

Open the Form in Design view and in the Default View Property select "Continuous Form".
On the Main Menu>View tick Headers & footers.
In the footer add an Unbound Field, in the Field's Control source enter
=Sum([Fieldname])
where Fieldname is the name of a field that contains Numeric data that can be "Summed".
If you look at a Report with Totals it uses the same technique.

[afh110]

Correct me if im wrong but your asking me to make the form continuos? But I want the total to show in the subform not the form! anyhow since i have a subform in the main form , changing it to a continuous is not allowed!

I tried doing that for the subform but there is no "Default" option there.

Initially i wanted to show a field (in the Datasheet view) that shows the sum of a set of fields in the Datasheet. But I wouldnt mind to have another control that adds up a set of fields inside the datasheet view. Any ideas :S

[OBP]

I meant the Subform, not the Mainform.
Are you using Access 2007 Split Forms?
As that is not a real Subform, you would nead to create a real one.

[afh110]

Im not using a split subform, I have an embedded subform control in which the datasheet view is displayed. I am using Access 2007.

Im not sure if my subform is considered an actual subform, this is what i did to get to where i am:
1- Open main form
2- Drag a subform control into main form
3- Set the subform Source Control to a query.

When i run that i get a datasheet view. Is that a subform?

[OBP]

Yes it is, when you click on the Subform with the Mainform in Design view and then Click it again it should show you the Default View in the Properties.
Or open the Subform in Design view it should show you the Default View in the Properties.

[afh110]

When i click on the subform twice all i get is that the focus is set inside the control ! I don't see a default view property!

[OBP]

Do you see the Properties at all? If not click Maine Menu>View>Properties or press f4.

[afh110]

I see the properties table but i went through allthe options in that table i dont see the Default veiw option! However i see that property if i select the main form!

[afh110]

Im trying to attach the DB (after stripping it down) but then again the uploading is not working! If anybody would like me to email them the copy instead ? Maybe you can take a look at it and see what im doing wrong!

[OBP]

Create an a new blank access 2003 database, Import the Table, (remove the data from the table) and Subform in to it and post that as a zipped file.

[afh110]

I stripped it down to 2.3 Mbs..i don't think it needs to be any smaller and the site doesnt want to upload!!

[OBP]

Did you Zip it, it has to be Zipped?
The site limit for attachments is about 400Kb

[afh110]

Ok i figured out why i can't see the default view property! its because i make my subform bound after form load and not prebounded to a table or a query (as the wizard would do).
The source object of my subform is a dynamically created table therefor i can't use the wizard to make it bound. Ideas :)?

[afh110]

Ok i figured out why i can't see the default view property! its because i make my subform bound after form load and not prebounded to a table or a query (as the wizard would do).
The source object of my subform is a dynamically created table therefor i can't use the wizard to make it bound. Ideas :)?

[OBP]

Do you recreate the Table each time and change it's Structure, or can you just remove the data and write new data to it?

[afh110]

I can save an empty table as a template , populate it and clearing it as i go.. so yes

[OBP]

Providing the table Structure doesn't change you can create a Query from it.
The query can be used as the Record Source of your subform.
When you rewrite your table requery your form.

[afh110]

How do i show the totals tho? I dont have a problem displaying the data but i have problem showing a total!

[OBP]

It uses the same technique as Reports, you create an Unbound text box in the Form Footer and in the Control Source enter =sum([fieldname])
where fieldname is the name of the field to be totalled.

[afh110]

The FieldName in my case is a column in a Datasheet view. How to i reference a particular or a range of cells in a DataSheet view?

Im not sure but maybe i miss understood you, but although i have a non changing table scheme which i can use to write and update as i please, I need the user to be able to see the data in a datasheet view on the form and edit the data inside the datasheet view till the unbound textbox we created in the footer of the form total up to 100%. Is what you said still applicable?

[OBP]

As far as I know, are they entering %ages?

[afh110]

here is a workflow:
1- User defines a list of objectives with their weights (%) on a looping form (one objective entered at a time). These objectives and weight percentages are loaded into pubic arrays.
2- User clicks next to go to another form where the form lists all the entered objectives and thier respective weights to validate that the total of the weights add up to 100%.

So i was looking for some kind of control to list the objectives and weights in a grid format (keeping in mind that the size of that grid will change depending on the number of objectives entered). I couldnt find anything, so my work around was that i would create a temp table , load that objectives and weights to it and then bind a subform to that table. This gives me a datasheet view that allows a user to edit the values inside. Now i need to be able to total the weight percentage column to actually check if the total adds up. Here is the code im working with.


in this code i have already loaded the objectives and weights into a public array and wrote that array into a table called "ObjWeightTbl". Now on the next form load, the code is the following:



Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim i, j As Integer
Dim dB As DAO.Database
Dim ObjWeightTbl As DAO.TableDef
Dim NewQry As QueryDef
Dim TableExist As Boolean
Dim DBname As String
Dim rs As DAO.recordset
Dim AryEnd As Integer
Dim SqlResult, MySQL As String

'Check if Table exist temp and delete
With CurrentDb
Set dB = CurrentDb
DBname = dB.Name
TableExist = IsTable(DBname, "ObjWeightTbl")
If TableExist = True Then
dB.TableDefs.Delete ("ObjWeightTbl")
End If
MySQL = "CREATE TABLE ObjWeightTbl (ID counter, Object text (50),Weight number)"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True

AryEnd = CInt(Forms!Step2_BSC_Creation!Txt_ObjCounter.Value) 'UBound(AryObj())
For i = 0 To AryEnd - 1
SqlResult = "INSERT INTO ObjWeightTbl(object,Weight)VALUES('" & AryObj(i, 1) & "','" & AryObj(i, 3) & "');"
.Execute (SqlResult)
Next i
End With

'link table to subform datasource
With CurrentDb
Set dB = CurrentDb
DBname = dB.Name

QryExist = IsQuery(DBname, "GetObjWeightQry")
If QryExist = True Then
dB.QueryDefs.Delete ("GetObjWeightQry")
End If
Set NewQry = dB.CreateQueryDef("GetObjWeightQry", "Select * from ObjWeightTbl;")
SF_Step21DS.SourceObject = "Query.GetObjWeightQry"
End With
' Do sum and check total =100%

'==============this is what i need to figur out how to do!
Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub\0
\0\0\0\0\0