What is the problem with the record set query below , instead of returning a unique record its return one record in all the product lines, but If i use Dlookup its return the correct unique record see below:
Code:
Option Compare Database
Option Explicit
Private Sub Report_Load()
Dim db As DAO.Database
Dim strSQL As String
Dim prm As DAO.Parameter
Set db = CurrentDb
strSQL = "SELECT Quanties FROM [ViewSalesUsedForClosingValuation] WHERE [ProductID] =" & Me.ProductID
With db.CreateQueryDef("", strSQL)
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next
With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
If Not .EOF() Then
Me.txtSales = Nz(.Fields(0).Value, 0)
End If
End With
End With
Set prm = Nothing
Set db = Nothing
End Sub
=Nz(DLookUp("Quanties","[ViewSalesUsedForClosingValuation]","[ProductID] =" & [ProductID]),0)