Full Version: reference previous record in continous form
UtterAccess Forums > Microsoft® Access > Access Forms
I have a continous form based on a table.
PriceID , PriceMin , PriceMax
I would like PriceMin to equal PriceMax of the previous record.
Something along the lines of PriceMin = Price Max where PriceID = PriceID -1
so the results will be somehting like:
Priceid PriceMin PriceMax
1 0 300
2 300 600
3 600 1000
I'm not too sure of the syntax for something like this....Is my logic correct in this , is it a viable solution?
i think is better practice to just reference the price in your query or your report instead of having the same price twice ? Using a "+1" or"-1" in your query ?!
o do what you want i would go on the onupdate event of the continous form :
If isnull(PriceMin) Then
PriceDLess1 = DMax("[PriceID]","{tablename}) - 1
Me.PriceMin = Dlookup("[PriceMin]","{tablename},"[PriceID]=" & PriceIDLess1 &"")
End If
The reason I want to do it like this is because it is supposed to represent tier pricing so it need to be visible and I don't trust the users to put in the correct thing....
Note that your table shouldn't even have PriceMin and PriceMax on it if PriceMax is always the PriceMin of the previous record. You'd be storing a calculated value and doubling the size of your table. Simply have PriceID and Price... period.
When in a form or report, you'd have two textboxes: Me.PriceMin (which is unbound) = DLookUp("[Price]", "tblPrices", "[PriceID] = " & Me.PriceID - 1) and Me.PriceMax (which is bound to Price field in the table).
Change tblPrices to your real table name.
no worries. Without knowing the rest of your build propject and how these continious forms will operate you may need to expand the Dmax and the Dlookup to take into account further criteria.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.