Trying to use DLookup

[Darrell Childress]

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

 

[Dirk Goldgar]

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.

 

[Darrell Childress]

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

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.

 

[Darrell Childress]

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