I have a mainform CashSales (TransactionDate as short date) and subform
CashSalesDetails (stockid, unitprice) bound to their respective tables. I
have also a SalesPriceList table (SalesPriceListID,
StockID,StockName,StartDate and UnitPrice) where a stock can have many prices
depending on when price changes (StartDate).

I would like the unitprice on the subform to automatically update itself by
considering the transactiondate on the mainform and the stockid on the
subform.

To get the correct applicable price,
1. select all records in SalesPriceList where the stockid match the stockid
on the subform,
2. select the above filtered records (from SalesPriceList) where startdate
<= transaction date from the mainform,
3. sort the remaining field by descending order.
4. get the first record.
5. set the subform unitprice to the value of the unitprice from
SalesPriceList.

At the moment I am getting an error 'Variable not defined', and the where
clause is highlighted in yellow.

Option Compare Database
Option Explicit

Public Function GetUnitPrice() As Double
'On Error GoTo Err_GetUnitPrice

 'Establish connection to the SalesPriceList table
 'recordset and connection variables

   Dim rstSalesPriceList As ADODB.Recordset
   Dim rstFiltered As ADODB.Recordset
   Dim strCnxn As String
   Dim strSQL As String
   Dim varResult As Double
   Dim strStockID As Long
   Dim dteTransactionDate As Date
   
   dteTransactionDate = Forms![CashSales].TransactionDate
   strStockID = Forms![CashSales]![CashSalesDetails].[Form]![StockID]
   
varResult = Null  'Initialize to null.
         
   'Opening the connection
   Dim Cnxn As ADODB.Connection
   ' Open a connection
   Set Cnxn = CurrentProject.Connection
   'strCnxn = "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='C:\Documents
and Settings\atupele.kyomo.AG\Desktop\Atupele\Business Plans\Shilabela
Trading Company\Accounting Database\StandingData.mdb';"
   'Cnxn.Open strCnxn
   
   ' Open SalesPriceList table to retireve the applicable unit price
   strSQL = "SELECT
SalesPriceList.SalesPriceListID,SalesPriceList.StockID,SalesPriceList.StartDate,SalesPriceList.UnitPrice FROM [SalesPriceList]"
   strSQL = strSQL & " WHERE #" & (SalesPriceList.StartDate) & "# <= #" &
Format(CDate(dteTransactionDate), "mm/dd/yyyy") & "#" And
"(SalesPriceList.StockID) =""" & [strStockID] & """"
   strSQL = strSQL & " ORDER BY SalesPriceList.StartDate DESC;"
   
   Set rstSalesPriceList = New ADODB.Recordset
   rstSalesPriceList.CursorLocation = adUseClient

   rstSalesPriceList.Open strSQL, Application.CodeProject.Connection, 1, 3,
adCmdTable
   
       If rstSalesPriceList.RecordCount > 0 Then              'If recordset
returns more than one records
       rstSalesPriceList.MoveFirst                           'Go to the
first record
       varResult = rstSalesPriceList!UnitPrice               'Set the
varResult to the unit price
                 
       Else
           'If recordset is one record
           varResult = rstSalesPriceList!UnitPrice
     
   End If
   
   rstSalesPriceList.Close
   Cnxn.Close
   
   'Assign the return value.
   GetUnitPrice = CDbl(varResult)

Exit_GetUnitPrice:
   
   Set rstSalesPriceList = Nothing
   Set Cnxn = Nothing
   Exit Function

'Err_GetUnitPrice:
  ' MsgBox Err.Description, vbExclamation, "GetUnitPrice Error " & Err.Number
   
   'Resume Exit_GetUnitPrice

End Function

Private Sub UnitPrice_GotFocus()

UnitPrice = GetUnitPrice()

End Sub

I would appreciate it if you could explain to me the logic behind the
correct solution when giving your answers.

Rgds,