ConcatRelated issue with TempVars

[GrandMasterTuck]

[SOLVED] ConcatRelated issue with TempVars

Hi folks,

Here's hoping somebody knows a way around this. I have a form that has a field that's supposed to fetch a list of values from a query and concatenate them. I use Allen Browne's code for this:http://allenbrowne.com/func-concat.html

Here's my field code:

=ConcatRelated('Data','tblNamesAliases','PersonID=83344')

This works perfectly, no problems. But I need to adjust the value of that PersonID integer based on the record selected on my form. It's not always going to be 83344. In fact, the ONLY time it will be 83344 is if that specific person record is selected.

I tried several different ways of getting the data. I tried a TempVars value in the code, and set said TempVar upon opening the form, like so:

=ConcatRelated('Data','tblNamesAliases','PersonID=[TempVars]![TheID]')

And I get that same error everybody else gets, where it says TOO FEW PARAMETERS, EXPECTED 1.

So I tried to set a field on the form to the TempVar's value, and reference the field instead of the TempVar, like so:

=ConcatRelated('Data','tblNamesAliases','PersonID=[MyFormField]')

Same error.

I switched back to that hardcoded integer, and it runs perfect. So I know it's a problem with the way I'm supplying a value for that PersonID. I just don't know how to get it to work. And I NEED this to work!

I tried single quotes, double quotes, I tried coding quotes around the value of the PersonID (even though that value is an integer in the table). I even tried to code parts of the string in pieces in VBA, then assemble a new string from the pieces and encasing that in an EVAL() function... nothing.

Any idea how I can get this to work?

 

[moke123]

=ConcatRelated("Data","tblNamesAliases","PersonID= " & [MyFormField])

 

[arnelgp]

also:

=ConcatRelated("Data","tblNamesAliases","PersonID=" & [TempVars]![TheID])

 

[Galaxiom]

also:

=ConcatRelated("Data","tblNamesAliases","PersonID=" & [TempVars]![TheID])

Yes, but one would never use TempVars in VBA when the value is already right there on a form. TempVars have much more scope than is required.

The only place TempVars should be used is in queries where they provide a bridge between VBA and SQL.

 

[moke123]

So I tried to set a field on the form to the TempVar's value, and reference the field instead of the TempVar, like so:
Code:
=ConcatRelated('Data','tblNamesAliases','PersonID=[MyFormField]')

didnt see this line.

if PersonID is a control on your form you would use

=ConcatRelated("Data","tblNamesAliases","PersonID= " & Me.PersonID)

 

[GrandMasterTuck]

Thanks to all the replies! It works! The issue was that I kept putting the [TempVars] bit within the quotes for the WHERE part of the statement, and it should have been OUTSIDE the quotes, appended with an ampersand!

So instead of this:

=ConcatRelated("[Data]", "tblNamesAliases", "[PersonID]=[TempVars]![TheID]")

It should have been this:

=ConcatRelated("[Data]", "tblNamesAliases", "[PersonID]=[COLOR="Red"]" & [TempVars]![TheID][/COLOR])

You guys are awesome. Thanks again!

Yes, but one would never use TempVars in VBA when the value is already right there on a form. TempVars have much more scope than is required.

The only place TempVars should be used is in queries where they provide a bridge between VBA and SQL.

Well, yeah, I get that, but I'm not using this code in VBA. I'm using the code as the SOURCE value for a field on the form. I mean, it's not like there's a hard rule against using a TempVar, is there? I mean, it works, right? So who cares if one uses it? I understand it's probably not as efficient, especially when we're talking about a value that IS visible on the form in some field or another. But in my example, I created a field that used the TempVar as the source for its value, because I was thinking maybe I couldn't use the TempVar directly in the ConcatRelated code (due to some other threads I'd read over the past few days in regards to others having this issue). As it turns out, that wasn't the issue, I just essentially wasn't coding the string correctly and forgot to assemble the string with ampersands when supplying the value of a TempVar as a parameter in the string.