Loop thru every field in record

[bobfin]

I have several Access 2003 tables where the first field is the Account Number and all the rest of the fields represent specific months. The number of months varies with each table. The names of the month fields are of the form "2006-03". The values in those fields is the amount that was paid on the account for that month.

For each account, I want to create a large string showing evey non-zero payment and the month it was paid. How can I create that string? I woulld like to create a function to create the string and then use it in a query, but I don't know how to code the loop.

 

[CraigDolphin]

Here's a place to get you started using recordsets http://www.devdos.com/vb/lesson4.shtml. These let you loop through lists (queries) and this should let you build your string however you want.

 

[bobfin]

Lesson 4 shows how to get to various records in a RecordSet, but I need to know how to get the fieldnames and values of all the fields in the current record of the current database. Is there another lesson that shows this?

 

[CraigDolphin]

Erk...ok...so I just saw the request for information on buidling the string (which requires looping through a recordset), and didn't take the time to more carefully parse your question.

The long and the short of it is that your database needs a redesign. You should NOT have multiple tables with a column/field for each month. What you should have is ONE table.
PaymentTable
PaymentID (Autonumber PK)
AccountNumber (FK)
PaymentDate
PaymentAmount

To query this table by a month-year summary involves one query:

Add PaymentTable to a new query. Add all the fields to your query grid except Payment Date.

In a blank field in the query grid put something like MyPaymentMonth: Format([PaymentDate],"mm-yyyy")

Right click in the grid, turn 'totals' on. Change the 'Group By' option in the PaymentAmount Field to 'Sum'

Save the query once you're satisfied that it is working fine. If you want to limit the results to a particular account then use a suitable criterion in the critera row of the design grid. You can use this query as the source for a recordset as described in the tutorial.

If you want to display it like an excel spreadsheet, make a crosstab query that uses your first query as an input.

 

[MarkK]

You can also traverse the fields collection of any recordset using a For...Each...Next loop. You don't have to know anything about the fields in advance. Code looks a little like this...

Sub PrintFieldNameAndValue(rst as dao.recordset)
  dim fld as dao.field

  for each fld in rst.fields
    Debug.print fld.name & ": " & fld.value
  next fld
End Sub

 

[bobfin]

That For...Each approach looks promising. I tried turnning it into a function that would return the concatenated string of all the fieldnames and non-zero values. The idea was to make a query that would select each record in the table and output 2 columns: the Account and the result of the function. Here's the code for the function.

Function GetMonthAmt() As String
Dim dbCur As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strNote As String

Set dbCur = CurrentDb
Set rst =

' Initialize string
strNote = " "

'Go thru every field in record and add month & amount to string
For Each fld In rst.Fields
If fld.Value <> 0 Then
strNote = strNote & " " & fld.Name & " for $" & fld.Value
End If
Next fld

GetMonthAmt = strNote
End Function

How do I make this function only look at the current record of the table (tblImport) that the query will be using?

 

[MarkK]

To back up a little. What I would do is change your table structure. Having a table with multiple columns that contain the same kind of data is a make work project, and that's really what you're up against here. If you want to spend a bunch of time doing something new, research Normalization, and normalize your table structure.

Tables should look like...
tAccount
AccountID (PK)
AccountName

tMonthlyActivity
ActivityID (PK)
AccountID (FK)
ActivityMonth
ActivityYear
Amount

You can write something that will parse the data in the structures you have, but these will remain fragile, subject to errors, and a nightmare to maintain. Get your table structures right and everything'll get way easier to do.