cycle thru recordset filed collection

[Mark Kubicki]

I want to cycle thru the field collection creating a variable based on the
name of each filed and then assigning the value of the field to that
variable. The table has only 1 record, which consists of a series of user
defined default values (these values are used when generating, with code,
the value of a textbox in a report).

ex:
in Table1, there exists fields:
PrntNotes - value in record 1: True
PrntOption - value in record 1: True
PrntDescription - value in record 1: False)
...

What I ought to wind up with is a set of variables:
varPrntNotes - value: True
varPrntOption - value: True
varPrntDescription - value: False

thanks in advance...
-mark

 

[Klatuu]

The concept interested me, so I tried every way I can think of to do this,
but it just will not work.
The closest I could get was one of two ways. One is to use a VBA collection
object. This was the easiest, but then there was no way to know the field
names. What I did was use the field name as the Index property of the
collection, and the field value as the Item property of the collection. So
if you know what field name you want, you can retrieve the value using the
field name as the index. But this doesn't make much sense, because if you
already know the field name, it becomes pointless.

The other was creating a two dimensional array with the field name in the
first element and the value in the second, but then you are right back where
you started, you have to know the field name.

Now, in either case, if you know the field name you can retrieve the value.
As I said, this is easier using a collection.

But, I can't figure out how you intend to use this. You description is a
bit vague. If I understand, you want to allow users to define Default Values
for some text boxes on a form. Each field in this table will be used to
populate the Default Value property of a text box corresponding to a field in
the table. You want the value of the table field to be the value for the
Default Value property of the text box control.

Let me know if this is correct and I can suggest a way to do that. It would
also be useful if you let me know if the number of fields in this table will
vary and if it will be used for more than one form.

 

[Mark Kubicki]

thanks for all so far...

the intent is that there is a form that the user chooses options on:
prntNotes (Y/N) ' print notes
prntOptions (Y/N) 'print options
inclAddlInfo (Y/N) 'include additional information
....

I have a report where I dynamically build the text to be displayed; so the
code in a subroutine strGenerateDescription may (loosely) look like this:

BuildText = ""
IIf(varPrintNotes, BuildText = BuildText & ( frm!luminairType.Value
+ ", PLEASE NOTE THAT: " + frm!Note.Value, )
IIf(varPrintOptions, BuildText = BuildText & ( "ADDITIONAL OPTIONS:
" + frm!Option.Value, )
strGeneratedDescription = BuildText

where the value of the text box on the report is set to
strGeneratedDescripotion

since this evaluated with the same criteria for each recod in the report, it
seems to make sence to gather it into a set of variables ONCE, rather than
checking the values at each record

 

[Klatuu]

Seems to me it would be easier to use a query and populate a calculated field
in the query based on the options on the form.

 

[Mark Kubicki]

it would be except that the "build of information" is fairly complex, and
keeping track of it in a query is at best cumbersom...
-m.

 

[Klatuu]

You can call a function from a query. It would be less cumbersom that what
you are trying to do. Another option would be to use the IIf statments
directly in the control source of controls on the report.

 

[Mark Kubicki]

k... i'll try both

You can call a function from a query. It would be less cumbersom that
what
you are trying to do. Another option would be to use the IIf statments
directly in the control source of controls on the report.

 

[Klatuu]

Okay, post back if you have any problems with it.