Access Form Update calculation Programming line by line

[Stefan Moser]

Dear friends
I'll do have a form which is like a Week Kalender


Date "KW" ; "Sum of Goods in Place" ; "consumtion of this goods this week" ; "New goods arrive this week" ; "Check of goods in place"
The weeks are form now to now + 52 Weeks

I absolutely failed to program a function which start from first line in a form or query to the last line and does the following calculations
The following routine is not the problem it is the walk through loop which I could not program in VBS

No I want to have a function or procedure which does the following thing



IF "Check of goods in place" are >0 then
"Sum of goods in Place" = "Check of goods in place" - "consumption of this goods this week" + "New goods arrive this week"
Else
"Sum of goods in Place" = Previous "Sum of goods in Place" - "consumption of this goods this week" + "New goods arrive this week"
End IF

[Ruegen]

IF "Check of goods in place" are >0 then <-this is a string getting compared to a 0. It should be the field and the field value the item you want to compare

If [MyField] > 0

So also is the field a number (integer)?

then you have

"Sum of goods in Place" = "Check of goods in place"

well you aren't declaring a control or string

so me.ControlName = "Check of goods in place"
would work however that is just passing a string and not a value of a field (which could be an integer).

dim strMyString as string
strMyString = "Check of goods in place"

or if an int field
dim intMyInteger as int
intMyInteger = 3
(or whatever number the ID of the row is).

Suggest looking up making variables or giving a control a value...

[Stefan Moser]

Thank you very much !

I've doe a few adjustments and now it worked


Private Sub ButtonTest_Click()
On Error GoTo ErrorHandler
' Den mit dem Steuerelement übereinstimmenden Datensatz suchen.

Dim rs As Object
Dim OldVal As Double
Set rs = Me.Recordset.Clone

For n = 1 To 50


rs.FindFirst "[TimeStamp]=" & n
rs.MovePrevious

OldVal = Me.Bestand_avg

If IsNull(Me.Bestand_avg) Then Me.Bestand_avg = 0
If IsNull(Me.Wareneingang) Then Me.Wareneingang = 0


rs.MoveNext
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'rs.Edit

If Not rs.EOF Then Me.Bookmark = rs.Bookmark


If Me.Inventur = 0 Then
Me.Bestand_avg = OldVal - Me.[Verbrauch pro Monat AVG] / 4 + Me.Wareneingang
Me.Bestand_min = OldVal - Me.[Verbrauch pro Monat max] / 4 + Me.Wareneingang
Me.Bestand_max = OldVal - Me.[Verbrauch pro Monat min] / 4 + Me.Wareneingang
Else
Me.Bestand_avg = Me.Inventur - Me.[Verbrauch pro Monat AVG] / 4 + Me.Wareneingang
Me.Bestand_min = Me.Inventur - Me.[Verbrauch pro Monat max] / 4 + Me.Wareneingang
Me.Bestand_max = Me.Inventur - Me.[Verbrauch pro Monat min] / 4 + Me.Wareneingang
End If

Me.Wareneingang = Me.Bestellung_VPE * Me.VPE

'rs.FindFirst "[ID_Charge] = '" & Me![Search_Charge] & "'"
'rs.FindFirst "[ID_Form] = " & Me![Search_Spec]
'Me.Inventur = 0
'[Bestell-Nr] = 111

Next n
Me.Refresh
'rs.
Exit Sub


ErrorHandler:
Resume Next
End Sub

[Stefan Moser]

No I gota error message

Do you have an idea ho to fix this ?

[Ruegen]

Thank you very much !

I've doe a few adjustments and now it worked


Private Sub ButtonTest_Click()
On Error GoTo ErrorHandler
' Den mit dem Steuerelement übereinstimmenden Datensatz suchen.

Dim rs As Object
Dim OldVal As Double
Set rs = Me.Recordset.Clone

For n = 1 To 50


rs.FindFirst "[TimeStamp]=" & n
rs.MovePrevious

OldVal = Me.Bestand_avg

If IsNull(Me.Bestand_avg) Then Me.Bestand_avg = 0
If IsNull(Me.Wareneingang) Then Me.Wareneingang = 0


rs.MoveNext
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'rs.Edit

If Not rs.EOF Then Me.Bookmark = rs.Bookmark


If Me.Inventur = 0 Then
Me.Bestand_avg = OldVal - Me.[Verbrauch pro Monat AVG] / 4 + Me.Wareneingang
Me.Bestand_min = OldVal - Me.[Verbrauch pro Monat max] / 4 + Me.Wareneingang
Me.Bestand_max = OldVal - Me.[Verbrauch pro Monat min] / 4 + Me.Wareneingang
Else
Me.Bestand_avg = Me.Inventur - Me.[Verbrauch pro Monat AVG] / 4 + Me.Wareneingang
Me.Bestand_min = Me.Inventur - Me.[Verbrauch pro Monat max] / 4 + Me.Wareneingang
Me.Bestand_max = Me.Inventur - Me.[Verbrauch pro Monat min] / 4 + Me.Wareneingang
End If

Me.Wareneingang = Me.Bestellung_VPE * Me.VPE

'rs.FindFirst "[ID_Charge] = '" & Me![Search_Charge] & "'"
'rs.FindFirst "[ID_Form] = " & Me![Search_Spec]
'Me.Inventur = 0
'[Bestell-Nr] = 111

Next n
Me.Refresh
'rs.
Exit Sub


ErrorHandler:
Resume Next
End Sub

Not sure how it relates to the code above but ok good to see you have it sorted

[Stefan Moser]

It says Update or CancelUpdate without ADDNew or Edit

[Stefan Moser]

Do you have an IDea how to fix this error Message ?

[Stefan Moser]

More specifi I get this after excuting the Sub if I leafe the dataset ( click into next row)

[Ruegen]

What are you trying to achieve?

I could look through the code to find an error but what are you trying to do?

Are you wanting to use a do until or while loop with the recordset?

[Stefan Moser]

Dear Ruegen
thanks for your response and advise
I will have access to my laptop on Tuesday
then I will add comments into my code and a screenshot of my form to explain what I'm up to
Have a nice Pentecost holiday
and thanks for responding this fast

Stefan

[Stefan Moser]

Dear Ruegen
I missed a rs.Update
now it works
Thanks