Looping through records in a continous form

[accessnihon]

Good morning :-)

I am currently struggling with an operation that might be pretty simple to solve. I am at the moment thinking about the creating the following solution:

A user enters a value x between 0 and 1 into a field and clicks a button.
The vba code should then loop through all FILTERED items of the current recordset and sum up their population, until the value the user has entered is reached.

Once this point has reached, all records that have not been summed up yet, should be filtered out.

I don't know if my wording is understandable, but would really appreciate any help for generating the necessary code/solution.



best regards

Private Sub sumup_Click()

Dim popArray() As Integer
Dim RecordCount As Integer

RecordCount = DCount("[population]", "[main]")
ReDim popArray(RecordCount)

popCounter = 0
    While popCounter < 0.02
    popCounter = popCounter + ([population] / DSum("[population]", "[main]"))
Wend

End Sub

[June7]

If by current recordset you mean an open form, think I would use the form RecordsetClone method. Something like:
(The input of between 0 and 1 throws me off)

Dim x As Double, pop as double
x = 0.01
With Me.RecordsetClone
While Not .EOF And x < Me.textboxname
pop = pop + .[population]
x = x + 0.01
If Not .EOF Then .MoveNext
Wend
End With
MsgBox "Population = " & pop

[accessnihon]

Sorry for my late reply.
My internet access was somewhat restricted due to the holidays, but I can promise better reaction times now.

If by current recordset you mean an open form,

That is indeed what I mean. However, it is important that all restrictions applying to the open form (i.e. active filters) are also active for the loop functions.
Values that don't match the filter, should not be part of the mentioned "RecordsetClone".

I also think that my explanation was kind of spoiled, in the initial post.
The value X is a percentage, therefore it should be between 0 and 1. When summing up the population values, it would be interesting for me to only add the relative percentage.

I tried to implement this via the dsum command, but am not sure if that will actually work.

popCounter = popCounter + ([population] / DSum("[population]", "[main]"))

Any suggestions/help is very welcome.
thanks for the help so far.

[June7]

Making me think. I use RecordsetClone a lot. If the form is filtered, it is usually filtered by the WHERE argument of DoCmd.OpenForm. In one case is a subform and the filter is done by the Master/Child links. The clone is always a duplicate of the form's recordset.

As for the DSum, it must reference table, not form. Without filter criteria it will sum every record in table.