Lookup value from another table ado.

[atu]

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,

 

[Marshall Barton]

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.

I don't use ADO, but in DAO you can do all that with a TOP 1
query.

strSQL = "SELECT TOP 1 SalesPriceListID, UnitPrice " _
& "FROM SalesPriceList " _
& "WHERE StockID = """ & strStockID & """ " _
& " And StartDate <= " &
Format(dteTransactionDate), "\#m\/d\/yyyy\#")
& " ORDER BY StartDate DESC"

 

[atu]

Hallo Marsh,

Thanks for the reply. When I saw how short your sql string was compared to
mine, I became determined to improve mine as well until I went back to
re-read my notes on variables and quotes. I am new to quotes and literals
surrounding variables and I will manage over time. I managed to work around
them by standardizing my variables to variant types, working with one table
as a subform (Me) linked to unbound form (frmCashSales).

The problem I have now is that when I have more than one record in the
subform with different stockids (say 10 records with cigarettes, matches,
cooking oil etc) which have different prices, the function returns the
unitprice for the record on focus (ie unitprice has focus) for all records.
Furthermore, the price is applied to all stockids (cigarette price Tsh 8,000,
cooking oil 8,000 etc). When you move to another record, the unit price
changes as well to that stockid on focus, the whole form.

It's 5:30 right and I will go home soon but what I will try tomorrow is to
move the code to stockid after_update event and update the value and not the
function, like copying and pasting special values in excel.

What do you think?

Rgds,
atu

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

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

Dim Cnxn As ADODB.Connection
Dim rstSalesPriceList As ADODB.Recordset
Dim strCnxn As String
Dim strSQL As String
Dim strStockID As Variant
Dim dteTransactionDate As Variant

Dim varResult As Variant

strStockID = CVar(Me.txtStockID)
dteTransactionDate = CVar(Forms!frmCashSales.txtTransactionDate)

' Open a connection
Set Cnxn = New ADODB.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 Ledger Table with a cursor that allows updates
Set rstSalesPriceList = New ADODB.Recordset
rstSalesPriceList.CursorLocation = adUseClient
strSQL = "SELECT * FROM SalesPriceList WHERE CVar(StockID) ='" &
strStockID & "' And CVar(SalesPriceList.StartDate) <='" & dteTransactionDate
& "' Order By StartDate DESC"
rstSalesPriceList.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic,
adCmdText

varResult = rstSalesPriceList!UnitPrice

'Assign the return value.
GetUnitPrice = CDbl(varResult)

Exit_GetUnitPrice:

'Close the connection
rstSalesPriceList.Close
Cnxn.Close

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 txtUnitPrice_GotFocus()

txtUnitPrice = GetUnitPrice()

End Sub

 

[Marshall Barton]

Thanks for the reply. When I saw how short your sql string was compared to
mine, I became determined to improve mine as well until I went back to
re-read my notes on variables and quotes. I am new to quotes and literals
surrounding variables and I will manage over time. I managed to work around
them by standardizing my variables to variant types, working with one table
as a subform (Me) linked to unbound form (frmCashSales).

The problem I have now is that when I have more than one record in the
subform with different stockids (say 10 records with cigarettes, matches,
cooking oil etc) which have different prices, the function returns the
unitprice for the record on focus (ie unitprice has focus) for all records.
Furthermore, the price is applied to all stockids (cigarette price Tsh 8,000,
cooking oil 8,000 etc). When you move to another record, the unit price
changes as well to that stockid on focus, the whole form.

It's 5:30 right and I will go home soon but what I will try tomorrow is to
move the code to stockid after_update event and update the value and not the
function, like copying and pasting special values in excel.

What do you think?

I think is you should not be using code for this
calculation. Instead you could bind the price text box to a
calculated field in the record source query where. The
calculated field could be almost the same query I posted
used as a subquery.

A DAO query might be along these lines:

SELECT T.ItemID, ... ,
(SELECT TOP 1 X.UnitPrice
FROM SalesPriceList As X
WHERE X.StockID = T.StockID
And StartDate <= T.TransactionDate
ORDER BY StartDate DESC) As Price
FROM sometable As T
WHERE . . .