Form Lookup

[melanieh]

I have a question on my form:

I have one text box that combines several items in an expression to make a specific ID.

Then, I have a second text box that I want to take the ID from the first text box and lookup the price on a table. (I have a table with the IDs and the prices for each ID listed.)

(I've tried DLookup as suggested on another thread on the site but I may not be understanding it correctly.)

Does anyone have any ideas for me to try? I can explain more specifics if anyone would be willing to help.
Thank you so much!

 

[DCrake]

Simple Software Solutions

Hi Melanie

Table 1 Contents

Field1 "A"
Field2 "B"
Field3 "123"

TblPriceList Contents

fldStockCode AB111
fldPrice £100.00

fldStockCode AB120
fldPrice £200.00

fldStockCode AB123
fldPrice £150.00

fldStockCode ZZ999
fldPrice £1000.00


Form Contents

Me.TxtStockCode = Field1 & Field2 & Field3 (AB123)

Me.TxtItemCost = Nz(DLookup("fldItemPrice","TblPriceList","fldStockCode='" & Me.TxtStockCode & "'"),0)

To summarise:

The TxtStockcode is a mixture of other fields in your source table that makes up the stock code.

The TxtItemCost looks up the price of the item in the price list table where the stock code equals the stock code on the form. If it does not return a valid value, ie Null, This could be because the make up of the stock code is incorrect or that the stock item has not been added to the price list table. The Nz() function returns zero. This stops the dreaded 'Invalid Use of Null' error.

If you are not familiar with the Nz() function then is primary purpose is to enable the user to replace and expected value with a default value should the expected value not exist. This works for all data types. The first parameter is the expected value, the second is the replacement value.

X = Nz(Expected valued,Replacement value)


Code Master:

 

[melanieh]

Thank you. I will try this when I get home!
I appreciate it.