Concatonate an Unkown Number of Fields 1

[originalxavier]

Hello all,

I have a form and a subform. The form just has a person's name and contact information. The subform has the equipment that is assigned to them. What I would like to do is pass all of the data in the subform out to an unbound text box so that it can be stored for encryption.
So I have four fields with an undetermined number of records since all the users have different equipment. My question is this: How can I pass the data from the subform into the unbound text box? I am pretty sure that it will have something to do with a "For Next" loop or something along those lines... But I am drawing a blank.

Example:

Dim UunboundTtextBox as String
UnboundTextBox = Text1 & Text2 & Text3 & Text4

As you can see this would be great for one line, but there can be one, two, or twenty lines...

All suggestions are appreciated.
Thanks in advance!

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[EBGreen]

You could get the items assigned into a record set then loop through the recordset appending each value to the string. Something like this:

Do Until oRecordSet.EOF
  UnboundTextBox = UnboundTextBox & oRecordSet.Fields("equip")
  oRecordSet.MoveNext
Loop

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[originalxavier]

How would I specify the fields to capture in the subform in your example?...

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[EBGreen]

Is this data in a table in the DB, or only on the subform?

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[originalxavier]

it is in a table in the db. The subform pulls the data from the table.

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[EBGreen]

So all you need to do is costruct a query to retrieve the fields into a recordset and loop through it as above.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[originalxavier]

Is there no way to get the data directly from the report? The reason I ask this is because users enter the search criteria based on another form which passes the information to the report...

Example:

DoCmd.OpenReport "Report1", [acViewPpreview], , "[Signed] = TRUE AND [EntryDate] >= [Enter Beginning Date]"

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[EBGreen]

I don't know of a way, but I'm sure there is one.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]