Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 2nd, 2012, 10:39 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data Macro to get data from another table

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
  #2 (permalink)  
Old April 2nd, 2012, 10:56 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Did you try adding an equal (=) sign?

Hi Susie2012,

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

Quote:
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]
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:

Code:
=[ReturnVars]![retDefaultUnitPrice]
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! *** ---------------------------------------------------------------------------------------------------------->
  #3 (permalink)  
Old April 2nd, 2012, 11:25 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yes, that helped, thank you so much
  #4 (permalink)  
Old April 3rd, 2012, 06:54 AM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Great, glad to hear it is working!

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! *** ---------------------------------------------------------------------------------------------------------->
  #5 (permalink)  
Old July 1st, 2013, 01:49 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
  #6 (permalink)  
Old April 17th, 2015, 09:24 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Exclamation Get data from a table and show them in a form

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

Code:
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*

Code:
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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to transpose data from Excel to Word. intern11 Excel VBA 2 February 1st, 2017 10:39 AM
Two data table adapter not created when using data source wizard in VS 2008 sakshismriti Visual Studio 2008 0 November 17th, 2010 01:33 AM
macro no get data from web.... sal21 VB How-To 0 November 9th, 2007 04:29 PM
How to Save data through MACRO kotaiah Excel VBA 1 September 21st, 2006 07:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.