Creating loop to run functions for each variable in database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Imran J Khan - 28 May 2008 15:45 GMT

I have a simple flat file database of a questionaire of about 130
questions/variablea. I need to a report with the frequencies, mean, median,
mode, min, max, range, standard deviation etc. I thought it would be easier
if I could code VBA to create functions for each of the above (frequencies,
mean, median etc) and that would go through each variable and calculate the
each function for that variable. I need help creating a loop that will go
through each variable and pass the nescessary arguments for each function
etc. I am a novice :) vba programmer, so I may not be very clear in
explaining what I want.

Reply to this Message

Klatuu - 28 May 2008 16:08 GMT

A loop wont work using individual variables, but it would work with either a
recordset record or an array.

How are these variables definded and populated?

Signature

Dave Hargis, Microsoft Access MVP

> I have a simple flat file database of a questionaire of about 130
> questions/variablea. I need to a report with the frequencies, mean, median,
[quoted text clipped - 5 lines]
> etc. I am a novice :) vba programmer, so I may not be very clear in
> explaining what I want.

Reply to this Message

Imran J Khan - 28 May 2008 16:34 GMT

By variables i meant fields in the database. They are populated using a data
entry form in the same Access Database. Whatever value is entered is what
that variable is defined as :). Hope this is a little more clear. And also,
Thanks for the reply, I really appreciate your effort.

> A loop wont work using individual variables, but it would work with either a
> recordset record or an array.
[quoted text clipped - 10 lines]
> > etc. I am a novice :) vba programmer, so I may not be very clear in
> > explaining what I want.

Reply to this Message

Klatuu - 28 May 2008 16:48 GMT

Okay, you can loop through your table as a recordset. Then you can loop
through the Fields collection of each record. A record has a Fields
collection that can be indexed either by the field's name or by its ordinal
number. The numbering starts at 0, so the first field in the record is
Fields(0), the last is Fields(Fields.Count -1)

So, hopefully, the fields are all contiguous in the record. Here is a
sample of how you might do this:

Dim rst As DAO.Recordset
Dim lngFldCnt As Long

Set rst = Currentdb.OpenRecordset("MyTableName")
If rst.RecordCount = 0 Then
MsgBox "No Data Available"
Exit Sub
End If

'Fully populate the recordset

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
'Evaluate the fields in the record starting with the fourth field.
For lngFldCnt = 3 To rst.Fields.Count -1
'Do your functions here.
Next lngFldCnt
.MoveNext
Loop
.Close
End With
Set rst = Nothing

Signature

Dave Hargis, Microsoft Access MVP

> By variables i meant fields in the database. They are populated using a data
> entry form in the same Access Database. Whatever value is entered is what
[quoted text clipped - 15 lines]
> > > etc. I am a novice :) vba programmer, so I may not be very clear in
> > > explaining what I want.

Reply to this Message

Imran J Khan - 28 May 2008 16:55 GMT

Fantastic Dave, Thanks a lot. I can't try it right away as I still have to
wait for the data to be entered. I will add my functions where you indicated
in the mean time. Thanks again.

> Okay, you can loop through your table as a recordset. Then you can loop
> through the Fields collection of each record. A record has a Fields
[quoted text clipped - 49 lines]
> > > > etc. I am a novice :) vba programmer, so I may not be very clear in
> > > > explaining what I want.

Reply to this Message