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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|