Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 22nd, 2010, 11:38 AM
Registered User
 
Join Date: Jan 2010
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default How to save the results of a DLookup function

I have set up a form which looks for a code which has already been entered. It then looks up the code in a linked table and returns the current value. This is working fine, but I would like to store the returned answer in a table. Because I'm using the Control Source to run the DLookup Function I can't store the result in a field in the table. Is there a way around this please?

Thanks, Fintan
Reply With Quote
  #2 (permalink)  
Old February 8th, 2010, 06:00 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Don't use the control source as the DLookUp function. Put that into a hidden textbox that retreives the data, and then assign the retreived info into the real textbox thas is bound to the control source of the table.

Either than or assign the real textbox's value directly via the DLookUp function using VBA instead.

Either way, if this info is always in this linked table for you to get, you shouldn't have to store it in yours. You're just creating redundant info that takes up space.
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
Reply With Quote
The Following User Says Thank You to SerranoG For This Useful Post:
FintanMacC (February 9th, 2010)
  #3 (permalink)  
Old February 9th, 2010, 02:35 PM
Registered User
 
Join Date: Jan 2010
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by SerranoG View Post
Don't use the control source as the DLookUp function. Put that into a hidden textbox that retreives the data, and then assign the retreived info into the real textbox thas is bound to the control source of the table.

Either than or assign the real textbox's value directly via the DLookUp function using VBA instead.

Either way, if this info is always in this linked table for you to get, you shouldn't have to store it in yours. You're just creating redundant info that takes up space.
Thanks Greg. I'll try that. The DLookup looks up the current price only, but I want to keep the info as a snapshot of what happened at that time, like in an order where the price changes each year but I want to keep the details of what was bought and exactly how much was paid.

Thanks again,

Fintan
Reply With Quote
  #4 (permalink)  
Old November 17th, 2013, 01:46 PM
Registered User
Points: 6, Level: 1
Points: 6, Level: 1 Points: 6, Level: 1 Points: 6, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default same problem here

Is the issue solved?

I have the same problem but I have not understood what to do.

Please help
Reply With Quote
  #5 (permalink)  
Old November 18th, 2013, 09:02 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Can you describe the nature of what you're trying to do in more detail?
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
Reply With Quote
  #6 (permalink)  
Old November 18th, 2013, 02:47 PM
Friend of Wrox
Points: 1,015, Level: 12
Points: 1,015, Level: 12 Points: 1,015, Level: 12 Points: 1,015, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

I generally us a control's after update event to look up the value and store it in the desired control.

For example in an Invoice form, when you select the product/item/service code the after update event would look up the prince and store it int he Unit Price control on the form.

There is an example database that ships with Access called Northwind. The Order form shows exactly how to do this. I urge you to check it out.

TIP: The Northwind example is a great learning tool.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Last edited by HiTechCoach; November 18th, 2013 at 03:02 PM..
Reply With Quote
  #7 (permalink)  
Old January 24th, 2014, 12:10 PM
Registered User
Points: 16, Level: 1
Points: 16, Level: 1 Points: 16, Level: 1 Points: 16, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2012
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have done similar things with what you are speaking of, but instead of DLookup() I used Alan Browne's ELookup(). It is much more efficient and resolves a lot of issues that Dlookup() has.

Here is a link to his webpage - http://allenbrowne.com/ser-42.html

I have used Elookup() with SQL to save its output to a table for future use or logging.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
vb DLOOKUP function and dialog box error bluezcruizer Beginning VB 6 2 January 18th, 2007 04:25 PM
How to save all search engine results urls in a te abojan ASP.NET 1.0 and 1.1 Basics 0 December 28th, 2006 06:50 AM
Help - Dlookup function kk_sg Access VBA 3 August 23rd, 2006 11:30 AM
Dlookup function giving Type Mismatch error Ron V Access 2 May 19th, 2004 01:31 PM



All times are GMT -4. The time now is 04:27 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.