Insert a price from a product table into a price column in the order table, based on a product number

I am using the following event in my MS Access order database to lookup the newest prices from my product table, and store them in the order records.

Private Sub ProductID_AfterUpdate()
’ Evaluate filter before it is passed to DLookup function.
strFilter = “ProductID = " & Me!ProductID
’ Look up product’s unit Price and assign it to the UnitPrice control.
Me![UnitPrice] = DLookup(”[Price]", “Products”, strFilter)
End Sub

How do I do this on a Scriptcase Single record form?

Try this (this was AFTER calculations)

$master_field = ‘sum_billable_expense’; // Field name on the master application
$master_value = $billable_expense ; //This works
sc_master_value($master_field, $master_value) ;

//Update Master record
sc_exec_sql("UPDATE job SET sum_billable_expense = ‘$billable_expense’ WHERE job_id = ‘{job_id}’ ") ;

Thank’s a lot for your quick response. :slightly_smiling_face:

I found a Script for an Ajax_Event is the Scriptcase CRM sample database which I used with the right result. The event name I made was “ProdictID_onChange”

And the script:


  • Selecting a field from another table

// Check for record
$check_sql = "SELECT
WHERE ProductID = ".{ProductID};
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{Price} = {rs[0][0]};
else // No row found
{Price} = 0.00;

Good deal! thanks for sharing.