DLookup Function

[WorkRelated]

I am trying to return the price of an item in After Update using the DLookUp
Function in VB but can not get it to work. Please help.

NewDGUOrderDetail (Table/Form)
DGUOrderID - AutoNumber
DGUName - Text with Display Control Combo Box Row Source SELECT
NewDGUProducts.DGUName FROM NewDGUProducts;
Quantity - Number
PriceM2 - Currency
Width - Number
Height - Number

NewDGUProducts (Table/Form)
DGUID - AutoNumber
DGUName - Text
PriceM2 - Currency
TotalPrice - Number (in form will be a formula to calculate)

In the NewDGUOrderDetail Form I am albe to pull up the DGU Name but not the
price I am adding an After Update to the DGUName in VB as follows


Private Sub DGUName_AfterUpdate()

Me!PriceM2 = DLookup("PriceM2", "NewDGUProducts", "DGUID = " & [DGUID])

End Sub


I have tried changing it from DGUID to DGU Name but am still not getting
anywhere, I am fairly new to this function. Please tell me what I am doing
wrong.

 

[å±±æ ¹]

I am trying to return the price of an item in After Update using the
DLookUp
Function in VB but can not get it to work. Please help.

NewDGUOrderDetail (Table/Form)
DGUOrderID - AutoNumber
DGUName - Text with Display Control Combo Box Row Source SELECT
NewDGUProducts.DGUName FROM NewDGUProducts;
Quantity - Number
PriceM2 - Currency
Width - Number
Height - Number

NewDGUProducts (Table/Form)
DGUID - AutoNumber
DGUName - Text
PriceM2 - Currency
TotalPrice - Number (in form will be a formula to calculate)

In the NewDGUOrderDetail Form I am albe to pull up the DGU Name but not
the
price I am adding an After Update to the DGUName in VB as follows


Private Sub DGUName_AfterUpdate()

Me!PriceM2 = DLookup("PriceM2", "NewDGUProducts", "DGUID = " & [DGUID])

End Sub


I have tried changing it from DGUID to DGU Name but am still not getting
anywhere, I am fairly new to this function. Please tell me what I am doing
wrong.

 

[Tom Wickerath]

If I understand correctly (?), you have a combo box on a form named DGUName,
with row source of:

SELECT NewDGUProducts.DGUName FROM NewDGUProducts;

and you'd like to get the current price (PriceM2 from NewDGUProducts table)
when you select a new product. Is this correct? If so, you can avoid the
overhead of calling the DLookup function by simply including the price in the
row source for your combo box:

SELECT DGUName, PriceM2 FROM NewDGUProducts ORDER BY DGUName;

Set the column count = 2, and width of the second column = 0" so that it
does not display. Then, in your AfterUpdate code, reference this second
column as follows:

Private Sub DGUName_AfterUpdate()

Me.PriceM2 = DGUName.Column(1)

End Sub


Note that the column is "zero based" when referencing it in VBA code. Thus,
the 1st field selected in the row source becomes Column(0), and the 2nd field
becomes Column(1).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________