Calculated Field on Form, loop options?

[AlliCrader]

Hello All,



I have built a form that has a subform and they are linked by RECNO. The subform can have one or multiple entries. I am trying to calculate a field that will pull all of the entries into it, not just one. I have figured out how to successfully pull the first record that matches into it, but I cannot figure out how to pull the rest of them. I know that this would typically probably need a loop of some sort, but I am not familiar with VBA and I cannot figure out how to do this in access.

In the photo I have attached, it shows my field I am trying to enter this in at the bottom, I have got the first one in there, but how do I get the additional ones? The recno is equal on all three of these.

Can anyone help?

[pbaldy]

So you want the data from all 3 sub records concatenated together? I suspect you could modify this to return multiple fields:

http://allenbrowne.com/func-concat.html

[AlliCrader]

Yes, that is what I am trying to do. Is there a way to do this within access instead of visual basic?

[pbaldy]

Not that I can think of, perhaps somebody else knows a way.

[AlliCrader]

I am trying to use this but no matter what I do to fix syntax I either get #Name? or just nothing.

[pbaldy]

Without knowing your table structure and seeing your effort to use it, hard to say why you get an error. Can you attach the db here?

[AlliCrader]

I final got it, but now I am having a whole different issue. My condition is not working. It is pulling everything. Below is my code. I need it to only do it if [RECNO]=[Line Total Query]![RECNO].

=ConcatRelated("[Line Total Query]![Line Item]","[Line Total Query]","[RECNO]=[Line Total Query]![RECNO] ","[RECNO]"," ")

[AlliCrader]

they are auto number fields by the way

[pbaldy]

You missed the concatenation. Try


=ConcatRelated("[Line Item]","[Line Total Query]","[RECNO]=" & [RECNO],"[RECNO]"," ")

[AlliCrader]

Perfect! Thank you!

[pbaldy]

Happy to help and welcome to the site by the way!

[AlliCrader]

Thanks! I am now getting and error when I first start a new record because the RECNO is not there yet, do you know if there is a way around that?

[pbaldy]

Untested, but try:

=ConcatRelated("[Line Item]","[Line Total Query]","[RECNO]=" & Nz([RECNO], 0),"[RECNO]"," ")

[AlliCrader]

Thanks! Works like a charm.

[pbaldy]

No problemo!