PDA

View Full Version : Solved: referencing a record in a recordset



lifeson
11-12-2008, 08:10 AM
Hi All

I use the following to get a record from an access database.
It will always return a single record
How do I get the vallue from the recordset wihout having to write it to a worksheet first?
The record will be from TblCostFactor.price


Function Uplift(ptype As String, val As Double) As Double
Dim qry As String
Dim col As Long
qry = "SELECT TblCostFactor.FactorID, TblCostFactor.LowerRange, " & _
"TblCostFactor.UpperRange, TblCostFactor.Price, " & _
"TblCostFactor.PatchID, TblCostFactor.ProductID, TblCostFactor.ActiveFlag " & _
"FROM TblCostFactor " & _
"WHERE (((TblCostFactor.LowerRange)<val) " & _
"AND ((TblCostFactor.UpperRange)>val) " & _
"AND ((TblCostFactor.ProductID)='CHA' " & _
"Or (TblCostFactor.ProductID)='" & ptype & "'));"
Set recordSet = New ADODB.recordSet
With recordSet
'get records
.Open Source:=qry, ActiveConnection:=Connection
'Clear existing records
Uplift = .Fields(Price).Value ' I thought this would work but doesn't :(
End With
MsgBox Uplift
Set recordSet = Nothing
End With
End Function

Jan Karel Pieterse
11-12-2008, 08:22 AM
Change to:
Uplift = .Fields("Price").Value

lifeson
11-12-2008, 08:32 AM
Change to:
Uplift = .Fields("Price").Value


Thanks Jan

I was almost right :rotlaugh: