how to dlookup a value

[jefferyzhong]

For example,
table 1 has the following info
PO ProductName Price .....

table 2 has the following info
ProductID ProductName Price

I want table 1 "Price" Could be "dlookup" from Table 2, according to the same "ProductName".

I know how to do it by Query, but I need it in the table as a input because sometimes we need revise the price manually!

thanks in advance

 

[Remou]

Hi
The way it goes is:
Dlookup("Field", "Table", "Where")
Sooo ..
[tt]Dlookup("Price", "Table 2", "ProductName ='" & Me!ProductName & "'")[/tt]
This is assuming that you are using Dlookup in code and that Productname is a control on your form. Hope I have that right

 

[BobStubbs]

I wrote an FAQ on the DLookup function.

The examples in faq705-4978 may help you.

Bob Stubbs (London, UK)

 

[CantDo]

Sorry, perhaps I misunderstand the problem but, why would you use Dlookup on a table field when you could just as easily use the built-in field table-lookup function?

Let me know if that would work for you and if you need me to tell you how.

 

[dhookom]

CantDo, are you recommending the use of lookup fiedls? I avoid this at all cost since they seem to be mis-understood. Consider reviewing this link:

http://www.mvps.org/access/lookupfields.htm

I would enter all records using forms. Use a combo box based on table 2 (is that really the name of the table?) that contains a column for the "default" price. Use code in the after update event of the combo box to fill in the value of the price in table 1(again, why don't you use real table names?).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

Duane, Yes I am. I've read the posting you refer to and I think I understand most of it. But since I only work in Access and have used lookup fields effectively for a few years now, I don't see the harm in suggesting the solution to another Access user. It's simple and effective if you understand how the lookup works.

In the database I'm currently working on, I'm effectively using six lookup tables and I plan to use two more (if I ever get this Groups and GroupMembership stuff worked out!).

In any event, I'm only trying to contribute to this forum because this site has proven useful to me so far and I believe in giving back. Feel free to ignore me if you think my suggestions are ignorant; and if they are I apologize.

I've never tried to use the method you've described. Can you give me a more concrete example of its use? I'd like to understand it so I can decide whether to use it inmy current (or future) databases. Thanks.

 

[dhookom]

CantDo,
Some people have success using lookup fields but I have seen tons of people in news groups, tek-tips, Access list-servers that have gotten hopelessly lost using lookup fields. Veteran Access developers never use tables for entering data. This is always done through forms, code, or action queries.

I also don't ever use Input Masks or field captions.

If you have a combo box on a form named cboProductID with a row source like:
SELECT ProductID, ProductName, DefaultUnitPrice
FROM tblProducts
ORDER BY ProductName
And you have a text box on your form named txtUnitPrice bound to the UnitPrice field in your sales table. You can use code in the After Update event of the combo box to push the DefaultUnitPrice from the combo box into the txtUnitPrice control:

   Me.txtUnitPrice = Me.cboProductID.Column(2)

This will set the unit price to the default unit price from tblProducts.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

OK. Thanks. I'll try that in the right circumstances in future.

BTW, when I say I use lookup tables, that doesn't mean I don't enter data by using forms and code. I think I misunderstood your terminology: to me a lookup field is a field tied to a lookup table. I think you mean a field with hard-coded lookup values, which I shy away from as well. Perhaps we've just misunderstood one another. But your article seems to discuss the type of field I'm referring to and I find that type of field quite useful when you're starting with relatively static choices for a field value.

I use forms and code to programatically add new items (NotInList) to a combo box tied to a lookup table and I also include a separate edit form accessible from my main menu to edit and add lookup table values.

Anyway, thanks again.

 

[dhookom]

I do use lookup tables and couldn't imagine creating an application without them. I will never define a lookup field within a table design.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]