iterative calculations on a form

  • Thread starter Thread starter CJG via AccessMonster.com
  • Start date Start date
C

CJG via AccessMonster.com

Hi!
I have a data entry form which users collect various chemical readings over a
period of time. In order for the user to know when to stop collecting the
data, I would like to add a few stabilization reference fields as an FYI on
the form based on calculations from the data from last 2 records. Depending
on the results of the stabilization reference calcuations, there could be any
where from 2 to 10 records.

For instance, a user may collect 2 rows of info:
SampleID Time DO Cond
MW-01 8:45 30 250
MW-01 9:00 27 246
After calculating the stabilization reference number for DO, the user would
see it's still outside the range and would have to collect another round of
data,so their data would then be:
SampleID Time DO Cond
MW-01 8:45 30 250
MW-01 9:00 27 246
MW-01 9:20 26 245
When the calculation is done again, using the last 2 rows of data, DO is in
the range and they can now stop collecting data.

I know conceptually what I need to do, but am unsure as how to translate
programatically.

Any pointers in the right direction would be greatly appreciated.

Thanks,
CJG
 
Any pointers in the right direction would be greatly appreciated.

Thanks,
CJG
Click to expand...

Any pointers in the direction of the meaning of "DO" or
"Stabilization", or how the previous two records are used to do the
calculation, would be helpful. I have no clue what you are trying to
accomplish since I don't know what these terms mean nor how you expect
the calculation to be done.

John W. Vinson[MVP]
 
Sorry if this was confusing and maybe iterative was not the right description.


Basically I want to take the last 2 records entered thru my form, perform a
calculation in another field on the form that displays the result of a
calculation of a particular field for those 2 records.

For example I have a field named DO (or dissolved oxygen) on my form (and in
my underlying table) The stabalization reference calculation would be (DO1-
DO2/((DO1+DO2)/2)) *100, where DO1 is the value from the DO field in record
(TotalRecords -1) and DO2 is the value of DO in the last record (TotalRecords)
.. An example of data would be:
SampleID Time DO Cond
MW-01 8:45 30 250
MW-01 9:00 27 246
MW-01 9:20 26 245

I want to assign DO1 = 27 and DO2 = 26, since they are totalrecords-1 and
totalrecords and calculate my stabilization reference number

I've had some minor success, but am unable to figure out how to get the value
of a field from a particular record. I have the following in AfterUpdate
but get "Syntax error (missing operator) in query expression 'DoCmd.
GoToRecord acDataTable, stDocName , acGOTO , "(TotalNumberOfRecords -1)").
I know the TotalNumberOfRecords is returned correctly, but don't know (or
maybe understand) how to use the criteria in the DLookUp.

Dim TotalNumberOfRecords As Integer
Dim DO1 As Integer
Dim DO2 As Integer
Dim DOStab As Integer
Dim stDocName As String

stDocName = "tbl_purgeparms"

TotalNumberOfRecords = DCount("[DO]",stDocName)
'returns total number of records entered so far

If (TotalNumberOfRecords >=2) Then

'Note: Here is where I'm not sure how to go to the record
TotalNumberOfRecords -1

DO1= DLookUp("[DO]",stDocName,DoCmd.GoToRecord acDataTable, stDocName ,acGOTO
, "(TotalNumberOfRecords -1)")

DO2=DLookUp("[DO]",stDocName ''''samething...how to get reference the
last record

DOStab = (DO1-DO2/((DO1+DO2)/2)) *100

End If

I hope this explains a bit more of what I want to accomplish...and if I'm
even going down the right path.

Cath
 
Back
Top