Accessing multiple fields in report function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan Dorrough - 17 Jan 2006 01:08 GMT

I need to be able to access multiple fields within a VBA function called for
each record of a report (MSAccess 2000). For example, suppose I wanted to
return a string containing the names of all of the boolean fields that are
true for each record, how could I do it? Or suppose I simply wanted a count
of all of the boolean fields that are true in each record, how could I do
that?

Any suggestions would be appreciated.

Dan

Reply to this Message

Allen Browne - 17 Jan 2006 02:46 GMT

Dan, you will need to pass all the fields from the record to your function.

If the function is called CountTrue, and you need to pass 3 fields, you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of fields, you
can declare its arguments as a ParamArray. There's an example of parsing and
operating on the array parameters here:
http://allenbrowne.com/func-09.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I need to be able to access multiple fields within a VBA function called
>for
[quoted text clipped - 4 lines]
> of all of the boolean fields that are true in each record, how could I do
> that?

Reply to this Message

Dan Dorrough - 17 Jan 2006 15:47 GMT

Allen,

Thanks for the suggestion.

That is similar to the way that I started out to do it (passing each
variable as a parameter). However, it isn't really practical in this
instance. The table being reported on is a survey. Some questions have
50-100 independent choices ("choose all that apply") for answers which would
mean passing 50-100 parameters to the function. The maximum length of an
expression is 2500 bytes (or so) and a function with that many parameters
can exceed the maximum expression length.

I had thought that I could do something like:

Public Function CountSelected(strPrefix As String) As Integer
Dim DB As Database
Dim Tbl As TableDef
Dim fld As Field
CountSelected = 0

Set DB = CurrentDb
Set Tbl = DB.TableDefs("Survey")
For Each fld In Tbl.Fields
If Left(fld.Name, 4) = strPrefix Then
If (Not IsNull(fld)) And fld.Value Then
CountSelected = CountSelected + 1
End If
End If
Next fld

End Function

however, that dosen't work and I'm not sure why. Looks like maybe that
"table" that I get isn't the same as the table being processed. None of the
fields have values. Accessing any of them raises an exception. I don't do
enough MS Access programming to feel confident that this is really a
plausible solution.

I'll take a look at the ParamArray and see if that might suggest a solution.

Dan

> Dan, you will need to pass all the fields from the record to your function.
>
[quoted text clipped - 15 lines]
> > of all of the boolean fields that are true in each record, how could I do
> > that?

Reply to this Message

Allen Browne - 17 Jan 2006 16:33 GMT

You have a *field* for each possible answer?

There is a better way to design surveys than that. Duane Hookom has an
example here:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Sur
vey%202000'

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 65 lines]
> do
>> > that?

Reply to this Message

Dan Dorrough - 17 Jan 2006 16:47 GMT

Yes. I agree that a *field* for each possible answer is a lousy design.
However, I don't have any control over that. The database already exists. My
job (in this case) is to analyze the data that is in the database and print
suitable reports.

Dan

> You have a *field* for each possible answer?
>
> There is a better way to design surveys than that. Duane Hookom has an
> example here:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Sur
vey%202000'

> > Allen,
> >
[quoted text clipped - 65 lines]
> > do
> >> > that?

Reply to this Message

Allen Browne - 17 Jan 2006 16:55 GMT

So you do want all these fields, but you don't want to pass all the fields
into the function?

The only other option would seem to be to open a recordset, so the function
itself can loop through the Fields of the Recordset to get the count of
items.

Opening a recordset for each item will be a very inefficient approach
though.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Yes. I agree that a *field* for each possible answer is a lousy design.
> However, I don't have any control over that. The database already exists.
[quoted text clipped - 92 lines]
>> > do
>> >> > that?

Reply to this Message

Dan Dorrough - 17 Jan 2006 18:24 GMT

> So you do want all these fields, but you don't want to pass all the fields
> into the function?

That is correct. I don't want to pass in all of fields individually.

> The only other option would seem to be to open a recordset, so the function
> itself can loop through the Fields of the Recordset to get the count of
> items.

In Delphi, I would simply pass in (a reference to) the table and then
process the fields in the current record of the table. This would be a very
low overhead operation. Is there anything equivalent that can be done in
Access/VBA?

Dan

> Opening a recordset for each item will be a very inefficient approach
> though.
[quoted text clipped - 12 lines]
> >> There is a better way to design surveys than that. Duane Hookom has an
> >> example here:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Sur
vey%202000'

> >> > Allen,
> >> >
[quoted text clipped - 76 lines]
> >> > do
> >> >> > that?

Reply to this Message

Allen Browne - 18 Jan 2006 02:09 GMT

You can pass the name of the table, and the primary key value, and then
OpenRecordset() to get the data in the particular record.

The performance issue is with the connections you need to open and close the
recordset constantly. There may be a way to avoid that. For example, if this
were happening in a report, you could declare a module level recordset
variable, initialize it in Report_Open, and close it in Report_Close. Then
at any record, you could FindFirst in the already-open recordset, and avoid
the bottleneck.

That general approach (walking an open recordset rather than constantly
opening and closing it) can also be achieved through a class module, or even
a public Recordset variable if you have a way to close it when you are done.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>> So you do want all these fields, but you don't want to pass all the
>> fields
[quoted text clipped - 124 lines]
>> >> > do
>> >> >> > that?

Reply to this Message

Dirk - 17 Jan 2006 09:43 GMT

Since there is no true boolean type in the database you could also tag the
names of the fields that you would like to have tested and instead of passing
each field pass the whole recordset.

public function countTrueFields(byref rsRecordset as Recordset, byval strTag
as String) as integer
dim intCount as Integer
dim fldField as Field
intCount = 0
For Each fldField in rsRecordset.Fields
if Left(fldField.Name, Len(strTag)) = strTag and fldField.Value then
intCount = intCount + 1
end if
next fldField
countTrueFields = intCount
end function

> I need to be able to access multiple fields within a VBA function called for
> each record of a report (MSAccess 2000). For example, suppose I wanted to
[quoted text clipped - 6 lines]
>
> Dan

Reply to this Message

Dan Dorrough - 17 Jan 2006 16:05 GMT

Dirk,

That looks like it ought to work. What might the rsRecordSet parameter look
like when called in a report (I'm pretty new to MSAccess/VBA programming)?

Thanks,

Dan

> Since there is no true boolean type in the database you could also tag the
> names of the fields that you would like to have tested and instead of passing
[quoted text clipped - 23 lines]
> >
> > Dan

Reply to this Message