im having issues with getting a value from one table for use in another.

for instance im creating an invoice, i have a table with all the products listed in it, with columns description and Price, which i need to get information from those in a Line Items Table.

i have a data macro set up, but when i select the product i want, the data doesnt fill, it just fives me the algorithm used to call the requested data.

The txt/field box is filled with: [ReturnVars]![retDefaultUnitPrice]


Here is the Macros i have:

*NAMED MACRO - LineItemCodes.GetDetails


Parameter Name:
prmLineaItemCodeID

SetReturnVar
Name: retDefaultUnitPrice
Expression = 0
SetReturnVar
Name: retDefaultDescription
Espression = ""
SetReturnVar
Name: retDiscontinued
Espression = True

Look up a Record In SELECT LineItemCodes.ID, LineItemCodes.DefaultUnitPrice, LineItemCodes.DefaultDescription, LineItemCodes.Discontinued FROM LineItemCodes;
Where Condition = [ID]=[prmLineItemCodeID]

SetReturnVar
Name: retDefaultUnitPrice
Expression = [DefaultUnitPrice]
SetReturnVar
Name: retDefaultDescription
Expression = [DefaultDescription]
SetReturnVar
Name: retDiscontinued
Expression = [Discontinued]
----------------------------------------------------
*AFTER UPDATE - Embedded Macro*


RunDataMacro
Macro Name: LineItemCodes.GetDetails

Parameters
prmLineItemCodeID = [cboProductID]

SetProperty
Control Name: txtUnitPrice
Property - Value
Value = [ReturnVars]![retDefaultUnitPrice]

SetProperty
Control Name: txtDescription
Property - Value
Value = [ReturnVars]![retDefaultDescription]

SetProperty
Control Name: txtQuantity
Property - Value
Value = 0

Hi Susie2012,

I saw your posting here about the problems you are experiencing with using the "ReturnVars" collection in your macro. Specifically, you said:

So, actually, I think I know what is going on here. When you use this collection in a Macro, you MUST put an Equal (=) sign on the front of the expression, so that Access will evaluate the expression and return the value (and this needs to be added even though the Equal sign is already shown in some places in the macro editor). Otherwise, Access just thinks it is a string (in this case, the string value of "[ReturnVars]![retDefaultUnitPrice]") that should be stored in the variable. So, in your specific Macro, you need to put this exact code:

Specifically WITH the Equal (=) sign on the front of it. Does that make sense? Does it solve your problem?

Anyway, I hope that helps, but if there is anything else I can do to help, please post here and I'll do my best to try to answer your questions!

Sincerely,

__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->

yes, that helped, thank you so much

Hi Susie2012,

Thanks for the follow up post here. I'm so glad to hear that my solution fixed your problem with calling the "ReturnVars" collection from an Access Macro! The "ReturnVars" collection can be extremely useful when used effectively in Access Macros, and it is one of the newer features of Access and, IMHO, they are not particularly well understood in the community yet. NICE WORK on utilizing this feature in your application!

And thank you again for posting your Access questions here! If there is anything else we can do to help, you know where to post ...

Sincerely,

__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->

Hi Grifith,

I have a similar problem and can't get it working:

When I add "=" sign infront of the Set Property Value, I get the message "SetProperty macro action has an invalid value for the 'Value' argument.

I am trying to reproduce the following macros, which are mainly coming from an Access template.
But I got the following error:

"The identifiers '[DefaultUnitPrice]' could not be found."

Does anybody know where I am doing wrong?
And where should I look to make these macros working?

*NAMED MACRO - LineItemCodes.GetDetails

----------------------------------------------------
*AFTER UPDATE - Embedded Macro*