dlookup

[Guest]

I made a query called "STOCK", in that query there is a calculated filed
called [Stock].
I want to have the value of that stock for each [ProductID] as it appears in
the form "Products Deliveries".
In thye form "Product deliveries" I created an unbound text box and then I
typed
=dlookup("[stock]","stock","[ProductID] = " &Forms![Products
deliveries]![ProductID])
It does not work as it returns an error message.
What do I do wrong?
Thanks for your help

 

[Allen Browne]

The expression looks okay, provided:
a) ProductID is a Number field (not a Text field);
b) The ProductID has a value (which it may not at a new record);
c) The form is open, and is not a subform.

It ProductID is a Text type field, you need extra quotes:
=DLookup("stock","stock","[ProductID] = """ &
Forms![Products deliveries]![ProductID] & """")
Explanation of the quotes:
http://allenbrowne.com/casu-17.html

If ProductID is a Number field, it will fail when ProductID is blank,
because the 3rd argument resolves to just:
ProductID =
which is clearly invalid. To avoid that, use Nz(), e.g.:
=DLookup("stock","stock","[ProductID] = " &
Nz(Forms![Products deliveries]![ProductID],0) & )

If this does not work, open the Immediate Window (press Ctrl+G), and enter
this as one line:
? DLookup("stock","stock","[ProductID] = " &
Nz(Forms![Products deliveries]![ProductID],0) & )
When you press Enter, what error number an message do you get?

 

[Guest]

Thank you Allen for your suggestion.
1. You are right productiD is a text field so I copy-pasted your expression
into the unbounbd text box and it still does not work. I get a #Name? message
in the text box.
2. All these quotes and "&" characters get me confused. Are you sure you
have typed the expression properly?
3. I also followed your advice about the "immediate window". But once I have
typed the expression, how do I run it to detect the error message?
4. I have also tried the initial expression using a number field ([SP],
standing for Selling Price) and it also did not work. I know I am doing
something wrong somewhere, bu8t what is it? Should I format the unbound text
box to the same format of the data I am requesting?
5. As you seem to be well proficient in the matter, what are the best
training and support books for Access 2003? Have you been trnsposing access
2003 applications into MySQL?
Thanks
Rgds

 

[Allen Browne]

Re #1, use the Debug window to get the expression right.

It's just a matter of pressing Enter after you type the expression.
More info about how to use DLookup in this article:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Re #2, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Re #3, press Enter after the expression, and see how Access responds.

Re #4, let's work on one thing at a time. Perhaps post another question
after this one is solved.

Re #5 (resources), I have a bunch of stuff at the website below, including a
page of Links to other resources.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen for your suggestion.
1. You are right productiD is a text field so I copy-pasted your
expression
into the unbounbd text box and it still does not work. I get a #Name?
message
in the text box.
2. All these quotes and "&" characters get me confused. Are you sure you
have typed the expression properly?
3. I also followed your advice about the "immediate window". But once I
have
typed the expression, how do I run it to detect the error message?
4. I have also tried the initial expression using a number field ([SP],
standing for Selling Price) and it also did not work. I know I am doing
something wrong somewhere, bu8t what is it? Should I format the unbound
text
box to the same format of the data I am requesting?
5. As you seem to be well proficient in the matter, what are the best
training and support books for Access 2003? Have you been trnsposing
access
2003 applications into MySQL?
Thanks
Rgds
--
fab

I made a query called "STOCK", in that query there is a calculated filed
called [Stock].
I want to have the value of that stock for each [ProductID] as it appears
in
the form "Products Deliveries".
In thye form "Product deliveries" I created an unbound text box and then
I
typed
=dlookup("[stock]","stock","[ProductID] = " &Forms![Products
deliveries]![ProductID])
It does not work as it returns an error message.
What do I do wrong?
Thanks for your help

 

[Guest]

Allen,

Thks a lot for your help. I now got it working. What happened is that I had
not created a field in the table "Products deliveries" that could accept the
DlookUp value.
It's now done and working perfect
Rgds
--
fab

Re #1, use the Debug window to get the expression right.

It's just a matter of pressing Enter after you type the expression.
More info about how to use DLookup in this article:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Re #2, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Re #3, press Enter after the expression, and see how Access responds.

Re #4, let's work on one thing at a time. Perhaps post another question
after this one is solved.

Re #5 (resources), I have a bunch of stuff at the website below, including a
page of Links to other resources.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen for your suggestion.
1. You are right productiD is a text field so I copy-pasted your
expression
into the unbounbd text box and it still does not work. I get a #Name?
message
in the text box.
2. All these quotes and "&" characters get me confused. Are you sure you
have typed the expression properly?
3. I also followed your advice about the "immediate window". But once I
have
typed the expression, how do I run it to detect the error message?
4. I have also tried the initial expression using a number field ([SP],
standing for Selling Price) and it also did not work. I know I am doing
something wrong somewhere, bu8t what is it? Should I format the unbound
text
box to the same format of the data I am requesting?
5. As you seem to be well proficient in the matter, what are the best
training and support books for Access 2003? Have you been trnsposing
access
2003 applications into MySQL?
Thanks
Rgds
--
fab

I made a query called "STOCK", in that query there is a calculated filed
called [Stock].
I want to have the value of that stock for each [ProductID] as it appears
in
the form "Products Deliveries".
In thye form "Product deliveries" I created an unbound text box and then
I
typed
=dlookup("[stock]","stock","[ProductID] = " &Forms![Products
deliveries]![ProductID])
It does not work as it returns an error message.
What do I do wrong?
Thanks for your help

 

[Guest]

Allen,

I have another question about this DlookUp
I have embedded in my form "2006 SALES" a subform called "2006 SALES DETAILS
EXTENDED Subform" which calculates the individual products sold.
This subform reads the ProductID with a combo box and then takes the Product
Name automatically from that combo box built out of the Products table.
How can I load at the same time in the field Unit Price (called [SP]), the
SP coming from the Products table and also manage to modify it and load it
into the 2006 SALES DETAILS Table if need may be and at the same time give
infomation about my available stock?I have tried with the DLookUp but it
returns an error.....
After your info about the DLookUp for the Stock, it now works as I told you
but I can read the balance (Stock) only once I have validated the whole
record and passed to the next record. Would there be a way to be more dynamic
in reading that value?

--
fab

Re #1, use the Debug window to get the expression right.

It's just a matter of pressing Enter after you type the expression.
More info about how to use DLookup in this article:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Re #2, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Re #3, press Enter after the expression, and see how Access responds.

Re #4, let's work on one thing at a time. Perhaps post another question
after this one is solved.

Re #5 (resources), I have a bunch of stuff at the website below, including a
page of Links to other resources.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen for your suggestion.
1. You are right productiD is a text field so I copy-pasted your
expression
into the unbounbd text box and it still does not work. I get a #Name?
message
in the text box.
2. All these quotes and "&" characters get me confused. Are you sure you
have typed the expression properly?
3. I also followed your advice about the "immediate window". But once I
have
typed the expression, how do I run it to detect the error message?
4. I have also tried the initial expression using a number field ([SP],
standing for Selling Price) and it also did not work. I know I am doing
something wrong somewhere, bu8t what is it? Should I format the unbound
text
box to the same format of the data I am requesting?
5. As you seem to be well proficient in the matter, what are the best
training and support books for Access 2003? Have you been trnsposing
access
2003 applications into MySQL?
Thanks
Rgds
--
fab

I made a query called "STOCK", in that query there is a calculated filed
called [Stock].
I want to have the value of that stock for each [ProductID] as it appears
in
the form "Products Deliveries".
In thye form "Product deliveries" I created an unbound text box and then
I
typed
=dlookup("[stock]","stock","[ProductID] = " &Forms![Products
deliveries]![ProductID])
It does not work as it returns an error message.
What do I do wrong?
Thanks for your help

 

[Allen Browne]

You could try issuing a Recalc after the productID is updated:
Private Sub ProductID_AfterUpdate
Me.Recalc
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have another question about this DlookUp
I have embedded in my form "2006 SALES" a subform called "2006 SALES
DETAILS
EXTENDED Subform" which calculates the individual products sold.
This subform reads the ProductID with a combo box and then takes the
Product
Name automatically from that combo box built out of the Products table.
How can I load at the same time in the field Unit Price (called [SP]), the
SP coming from the Products table and also manage to modify it and load it
into the 2006 SALES DETAILS Table if need may be and at the same time give
infomation about my available stock?I have tried with the DLookUp but it
returns an error.....
After your info about the DLookUp for the Stock, it now works as I told
you
but I can read the balance (Stock) only once I have validated the whole
record and passed to the next record. Would there be a way to be more
dynamic
in reading that value?

--
fab

Re #1, use the Debug window to get the expression right.

It's just a matter of pressing Enter after you type the expression.
More info about how to use DLookup in this article:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Re #2, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Re #3, press Enter after the expression, and see how Access responds.

Re #4, let's work on one thing at a time. Perhaps post another question
after this one is solved.

Re #5 (resources), I have a bunch of stuff at the website below,
including a
page of Links to other resources.

Thank you Allen for your suggestion.
1. You are right productiD is a text field so I copy-pasted your
expression
into the unbounbd text box and it still does not work. I get a #Name?
message
in the text box.
2. All these quotes and "&" characters get me confused. Are you sure
you
have typed the expression properly?
3. I also followed your advice about the "immediate window". But once I
have
typed the expression, how do I run it to detect the error message?
4. I have also tried the initial expression using a number field ([SP],
standing for Selling Price) and it also did not work. I know I am doing
something wrong somewhere, bu8t what is it? Should I format the unbound
text
box to the same format of the data I am requesting?
5. As you seem to be well proficient in the matter, what are the best
training and support books for Access 2003? Have you been trnsposing
access
2003 applications into MySQL?
Thanks
Rgds
--
fab


:

I made a query called "STOCK", in that query there is a calculated
filed
called [Stock].
I want to have the value of that stock for each [ProductID] as it
appears
in
the form "Products Deliveries".
In thye form "Product deliveries" I created an unbound text box and
then
I
typed
=dlookup("[stock]","stock","[ProductID] = " &Forms![Products
deliveries]![ProductID])
It does not work as it returns an error message.