A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trying to use DLookup



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2010, 05:09 PM posted to microsoft.public.access.forms
Darrell Childress[_3_]
external usenet poster
 
Posts: 32
Default Trying to use DLookup

I have a table (tblSO_Items, this is my master table containing all
items) which contains the following fields
Number (this is a number field)
sono (text field)
lineno (text field)

I am entering data on a form into a separate table (tblProgressReport).
Here's what I would like to be able to do: After entering the sono and
lineno, I would like to have the form look in the tblSO_Items table and
automatically pull the contents of the Number field on the form. I know
this should be possible with the DLookup function, but I cannot get it
to work. Here's what I have in the AfterUpdate event of the lineno field
on the form:

Number = DLookup("[Number]", "tblSO_Items", "[sono]=(' &
Forms![sbfProgressReport]![sono] & ') And [lineno]=(' &
Forms![sbfProgressReport]![lineno] & ')")

But it does nothing. If I edit the above to be:
Number = DLookup("[Number]", "tblSO_Items", "[sono]='82003' And
[lineno]='13'") -- what I did here was enter an actual sono and lineno,
then it works perfectly and populates the Number field with the Number
field from tblSO_Items

Any help would be greatly appreciated, or if there is another way of
doing this.
Darrell
  #2  
Old May 14th, 2010, 05:43 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Trying to use DLookup

"Darrell Childress" wrote in message
...
I have a table (tblSO_Items, this is my master table containing all items)
which contains the following fields
Number (this is a number field)
sono (text field)
lineno (text field)

I am entering data on a form into a separate table (tblProgressReport).
Here's what I would like to be able to do: After entering the sono and
lineno, I would like to have the form look in the tblSO_Items table and
automatically pull the contents of the Number field on the form. I know
this should be possible with the DLookup function, but I cannot get it to
work. Here's what I have in the AfterUpdate event of the lineno field on
the form:

Number = DLookup("[Number]", "tblSO_Items", "[sono]=(' &
Forms![sbfProgressReport]![sono] & ') And [lineno]=(' &
Forms![sbfProgressReport]![lineno] & ')")

But it does nothing. If I edit the above to be:
Number = DLookup("[Number]", "tblSO_Items", "[sono]='82003' And
[lineno]='13'") -- what I did here was enter an actual sono and lineno,
then it works perfectly and populates the Number field with the Number
field from tblSO_Items

Any help would be greatly appreciated, or if there is another way of doing
this.



You're missing some double-quotes. Your function call should look like
this:

Number = DLookup( _
"[Number]", _
"tblSO_Items", _
"([sono]='" & Forms![sbfProgressReport]![sono] & _
"') And ([lineno]='" & _
Forms![sbfProgressReport]![lineno] & "')")

That's untested, so *I* may have made some mistakes as well.

I notice that you are treating [sono] and [lineno] as though they are text
fields. Is that right? If they are not text fields, but actually number
fields, then you don't need the single-quotes (') around the values you are
building in.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

  #3  
Old May 14th, 2010, 07:07 PM posted to microsoft.public.access.forms
Darrell Childress[_3_]
external usenet poster
 
Posts: 32
Default Trying to use DLookup

That worked! Thanks so much. I've spent nearly 2 hours trying to get
that to work. Yes, those fields are TEXT, even though they look like
numbers. They're from a linked table in our accounting system (Sage Pro
ERP - Visual FoxPro) that defines them as text...go figure. I will look
closely at what you've provided and try to figure out exactly where I
went wrong. Again, thanks, I can't begin to explain how much time you've
saved us.
Darrell

On 5/14/10 12:43 PM, Dirk Goldgar wrote:
"Darrell Childress" wrote in message
...
I have a table (tblSO_Items, this is my master table containing all
items) which contains the following fields
Number (this is a number field)
sono (text field)
lineno (text field)

I am entering data on a form into a separate table
(tblProgressReport). Here's what I would like to be able to do: After
entering the sono and lineno, I would like to have the form look in
the tblSO_Items table and automatically pull the contents of the
Number field on the form. I know this should be possible with the
DLookup function, but I cannot get it to work. Here's what I have in
the AfterUpdate event of the lineno field on the form:

Number = DLookup("[Number]", "tblSO_Items", "[sono]=(' &
Forms![sbfProgressReport]![sono] & ') And [lineno]=(' &
Forms![sbfProgressReport]![lineno] & ')")

But it does nothing. If I edit the above to be:
Number = DLookup("[Number]", "tblSO_Items", "[sono]='82003' And
[lineno]='13'") -- what I did here was enter an actual sono and
lineno, then it works perfectly and populates the Number field with
the Number field from tblSO_Items

Any help would be greatly appreciated, or if there is another way of
doing this.



You're missing some double-quotes. Your function call should look like
this:

Number = DLookup( _
"[Number]", _
"tblSO_Items", _
"([sono]='" & Forms![sbfProgressReport]![sono] & _
"') And ([lineno]='" & _
Forms![sbfProgressReport]![lineno] & "')")

That's untested, so *I* may have made some mistakes as well.

I notice that you are treating [sono] and [lineno] as though they are
text fields. Is that right? If they are not text fields, but actually
number fields, then you don't need the single-quotes (') around the
values you are building in.


  #4  
Old May 14th, 2010, 09:37 PM posted to microsoft.public.access.forms
Linq Adams via AccessMonster.com
external usenet poster
 
Posts: 1,474
Default Trying to use DLookup

You should also note that

Number

is a Reserved Word in Access, and field's name should really be changed to
something else. Sooner or later it may cause a problem.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #5  
Old May 17th, 2010, 02:32 PM posted to microsoft.public.access.forms
Darrell Childress[_3_]
external usenet poster
 
Posts: 32
Default Trying to use DLookup

Thanks for pointing that out Linq, that had not occurred to me, I will
try to change that.
Darrell

On 5/14/10 4:37 PM, Linq Adams via AccessMonster.com wrote:
You should also note that

Number

is a Reserved Word in Access, and field's name should really be changed to
something else. Sooner or later it may cause a problem.


 




Thread Tools
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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:33 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.