urgent - sum all rows for an unknown number of fields

[lala]

and also, sum an unknown number of fields' data for each row


before anyone says that my database is not normalized and there's no way there should be all these rows, i'm just automating someone's report and it was easier for me to just keep sutomatically adding 4 fields every month than to normalize it and then write code to put it back together they way they need it to look

so i'm working with their look to begin with


here's the way it looks


week1 week2 week3 week4 week5 week6 grandtotal
maibox1 5 3 7 5 7 6
mailbox2 8 4 6 3 5 8
mailbox3 3 5 3 2 4 5
mailbox4 4 5 2 4 2 5

grand total


i need to calculate the totals for both the GRANDTOTALs on the right and on the bottom

right now there's about 70 columns and as i said, every month 4 more keep adding

i know i have to do a loop to loop through all the fields, but how do i write the loop so it knows to check that the fields exists (i have a function that does that, here it goes)

Function FieldExists(ByVal fieldName As String, ByVal tableName As String) As Boolean
Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim strName As String
    Set db = CurrentDb
    Set tbl = db.TableDefs(tableName)
    For Each fld In tbl.Fields
        If fld.Name = fieldName Then
            FieldExists = True
            Exit For
        End If
    Next
End Function

so wither with this function, or some other way, how can i do this?


thank you!!!

 

[lala]

i think i'm an idiot, i think i can do it within this function
i'm about to try
am i right?

 

[lala]

    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim RowSum As Double
    
    
    Set db = CurrentDb
    Set tbl = db.TableDefs("Mailboxes")
    
    
    For Each fld In tbl.Fields
    RowSum = RowSum + fld.Value
    Next

MsgBox RowSum

ok, i think i'm getting closer, just don't know how to get the value of the field when i loop through the fields

 

[lala]

i almost got it, can someone please help

2 problems here
it doesn't stop at the end of the line and doesn't start adding the next line all over, it keeps adding them all

and once that gets fixed i don't know how to past the total for the line into the GrandTotal field

Dim dbs As Database, tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim i As Integer
Dim RowSum As Double


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Mailboxes", dbOpenSnapshot)

i = 2

With rst
rst.MoveLast
rst.MoveFirst
        Do While Not rst.EOF
        i = 2
            Do While i < ((rst.Fields.Count) - 1)
            Set fld = rst.Fields(i)
            If i < 73 And Not IsNull(fld.Value) Then
            RowSum = RowSum + fld.Value
            Else
            RowSum = RowSum
            End If
            i = i + 1
            Loop
            
            rst.MoveNext
            MsgBox RowSum
        Loop
        rst.Close
End With

 

[Brianwarnock]

I'm having trouble visualising your table design but hey that sure looks like a spreadsheet, and wouldn't it be easy to do it then.

Brian

 

[lala]

it is a spreadsheet originally, i'm automating some monthly steps they're doing to it in access and then exporting to excel, all with vba

i got everything to work except one thing
i can't assign the calculated value to the field

this is the part i'm having a problem with

--------------------------------------
'i've tried the 3 ways below
rst.Fields("GrandTotal").Value = RowSum
rst.Fields("GrandTotal") = RowSum
fld("GrandTotal").Value = RowSum
--------------------------------------------


can you please help?

Dim dbs As Database, tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim i As Integer
Dim RowSum As Double


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Mailboxes", dbOpenSnapshot)

i = 2

With rst
rst.MoveLast
rst.MoveFirst
        Do While Not rst.EOF
        i = 2
            Do While i < (rst.Fields.Count - 1)
            Set fld = rst.Fields(i)
                If Not IsNull(fld.Value) Then
                RowSum = RowSum + fld.Value
                Else
                RowSum = RowSum
                End If
            i = i + 1
            Loop
            
            rst.MoveNext
            'MsgBox RowSum

--------------------------------------
        'i've tried the 3 ways below    
        rst.Fields("GrandTotal").Value = RowSum
        rst.Fields("GrandTotal") = RowSum
        fld("GrandTotal").Value = RowSum
--------------------------------------------


        RowSum = 0
        Loop
        rst.Close
End With

 

[lala]

just to explain some more, this is one of their monthly reports that i'm automating. all their monthly reports are spreadsheets in excel and every month someone has to go and get new data and then manipulate it, formate it, save it and so on

i'm automating everything, so that the only thing there is to do is click a button and everything gets done.

so this is not a regular database, it's just an automation program in access

 

[boblarson]

Unless I'm missing it, you seem to be missing

.Edit

before trying to assign the values and then

.Update

after assigning them.

 

[ghudson]

Hopefully you will not need to use this database as-is in four years since you will have exceeded the maximum number of fields in a table at a rate of adding 4 fields per month.

255 is the maximum number of fields in an Access table.

 

[lala]

Hopefully you will not need to use this database as-is in four years since you will have exceeded the maximum number of fields in a table at a rate of adding 4 fields per month.

255 is the maximum number of fields in an Access table.

we thought about that, they're ok with starting all over then
i know it's not the best but i have to go with their requirements, i wish i didn't have to keep adding fields but they've been doing that for 2 years and want to keep on doing it this way

 

[lala]

Unless I'm missing it, you seem to be missing

.Edit

before trying to assign the values and then

.Update

after assigning them.

i'm slow, what do you mean?

put .EDIT and .UPDATE where?

rst.Fields("GrandTotal").Value = RowSum
rst.Fields("GrandTotal") = RowSum
fld("GrandTotal").Value = RowSum

 

[boblarson]

Sorry, I misread it. You aren't assigning a value to a recordset, you are GETTING a value from the recordset.

 

[lala]

.Edit
'rst.Fields("GrandTotal").Value = RowSum
'rst.Fields("GrandTotal") = RowSum
fld("GrandTotal").Value = RowSum
.Update


if you meant like this then EDIT gets highlighted

 

[lala]

Sorry, I misread it. You aren't assigning a value to a recordset, you are GETTING a value from the recordset.

well, i'm trying to do both
first i got the values and then i want to sum them up and assign them to one of the fields

 

[lala]

Sorry, I misread it. You aren't assigning a value to a recordset, you are GETTING a value from the recordset.

Bob, why are you banned???????????????

 

[boblarson]

Oh, then you would need:

.Edit
.Fields("GrandTotal").Value = RowSum
.Update

 

[lala]

EDIT gets highlight with the debug message OPERATION NOT SUPPORTED FOR THIS TYPE OF OBJECT

 

[boblarson]

Change this:
Dim rst As Recordset

To this:

Dim rst As DAO.Recordset

 

[lala]

same thing

 

[boblarson]

Okay, can you post the exact code you are now trying to use as we have gone through several steps in the process and I doubt it is exactly like what you had originally.