Loop through records leads to "cant go to next record" message

[Dynein]

Hello Everybody!
I am new to access and trying to programm a small database for me and my colleagues and I am stuck right now. I am trying to loop through a recordset (which is displayed on the form) of varying lenght, and sum up the values of one column ... I found the rs.openrecord solution somewehre else, yet this is not possible for me, since my form is getting its records from a query with a parameter restriction from the form (this solution always leads to an missing parameter error messaeg)

so i treid a different approach

Private Sub Form_Current()
Dim totalM As Integer
totalM = 0
DoCmd.GoToRecord , , acFirst
With Recordset
Do While .AbsolutePosition <= .RecordCount - 1 = True
totalM = totalM + Me.Pups
DoCmd.GoToRecord , , acNext
Loop
End With
Me.SumPups = totalM

End Sub

This always leads to the error message "cant go to next record" ... which i dont understand , since he should not go to a "new " not existing record anyways? I also tried "<" insteadt of "<=" and also without an "-1" ... no change so far...

Thank you for help already! I already found a lot of solutions in this forums when i got stuck before, and I hope you can help me this time too!

Best,
Dynein

 

[Minty]

Just use a DSum

DSum("YourField","YourTable","Optional your criteria")

as your controlsource for Me.SumPups.

To solve your recordset problem (which you don't need) you need to check for EOF in the recordset,

Do While NOT rs.EOF....

 

[Dynein]

Just use a DSum

DSum("YourField","YourTable","Optional your criteria")

as your controlsource for Me.SumPups.

To solve your recordset problem (which you don't need) you need to check for EOF in the recordset,

Do While NOT rs.EOF....

DSum would be the perfect solution, but my Criteria is depending on a user input from a combobox ... so it would be something like "[Location] Me.Loc" which apperently is not working ... I just always get and "" Field
I already tried an rs.EOF, where I always get the missing Parameter Message (Since my dataset is from a Query, which is filtered by the same Me.Loc parameter)

 

[pbaldy]

You can refer to the combo in a DSum():

http://access.mvps.org/access/general/gen0018.htm

 

[Minty]

If you use the on_Current event then your criteria can also be dynamic, you just need to get the syntax correct.
If Location is a number

DSum("Pups","YourTable", "[Location] = " & Me.Loc)

If location is text enclose the criteria in single quotes;

DSum("Pups","YourTable", "[Location] = '" & Me.Loc & "'")

 

[Dynein]

If you use the on_Current event then your criteria can also be dynamic, you just need to get the syntax correct.
If Location is a number

DSum("Pups","YourTable", "[Location] = " & Me.Loc)

If location is text enclose the criteria in single quotes;

DSum("Pups","YourTable", "[Location] = '" & Me.Loc & "'")

Ok this works! Thank you!