d look up code problems

[fordy]

=DLookup("[StockPrice]","StockTable", "[StockID]

This is what I have, I want it to look up the stock price from the previous record and place it in stock id in new record. Is this right.

 

[jimbrooking]

Think of the DLookup function as a simple SQL query of the form

SELECT [StockPrice] FROM StockTable WHERE [StockID] = 37;

The third argument of the DLookup function is the WHERE clause of the SQL statement.

You probably want to say something like

=DLookup("[StockPrice]","StockTable", "[StockID] = " & me.tbxStockID )

The order of args for DLookup is

1. The field containing the value returned by the function (in double-quotes)
2. The name of the table or query containing the field (in double-quotes)
3. A qualifying string that should resolve or evaluate to a unique row in the table, e.g., the row containing [StockID]=37.

The latter should be written in the form I showed above: the name of the field containing a value, followed by an equal sign (in quotes), concatenated with a variable containing the value the sought field has in the table. If the qualifying variable in the table is a string, the search argument must be quoted, as in

"[PartName] = """ & me.tbxPartName & """"

which could resolve/evaluate to

[PartName] = "Widget"

Of course, you can have multiple conditions in the DLookup just as you can in a SQL statement's WHERE clause.

HTH,
Jim

 

[fordy]

I cant get it to work, each new record must look up the value in the last. so waht should the end strinc read

 

[jimbrooking]

If the table has an autoincrement-type index ("StockID") you could use

DMax("StockID","StockTable")

to find the index of the last-stored record. This might be iffy if more than one used is adding stuff to the table - the DMAX could pick up the record added by someone who added a record right after you did. Not a concern for single-user apps, though.