Access - Carry forward values to new record

[Rakesh935]

Hi Guys,
I have a requirement in which I need to carry forward the result value of a previous record to the Actual value of a new record and this process to continue until the result value gets to 0 and all this is happening in an access form. For e.g.

RecodStat | Actualval | Inputval | ResultVal
First record | 10(MF) | 5(MF) | 5(CF)
CF to new record | 5(CFWD) | 3(MF) | 2(CF)
CF to new record | 2(CFWD) | 2(MF) | 0(CF)

Note: Manual Feed - (MF), Calculated field (CF), Carry Forward (CFWD)

For the first record - manual feed for Actualval and Inputval
carry forward to new record - manual feed for Inputval only

I was able to do this by select and insert sql queries but I'm sure there could be a better approach to this requirement.

Please help me with your valuable inputs and let me know if any further details are required.

Thanks again,
Rakesh

 

[arnelgp]

use the Form's BeforeInsert event to carry forward those values:

Private Sub Form_BeforeInsert(Cancel As Integer)
dim dblCarry As Double
With Me.RecordSetClone
   If Not (.Bof And .Eof) Then
      .MoveLast
     dblCarry=!ActualValue - !InputValue
     If dblCarry > 0 Then
         Me.ActualValue=dblCarry
    Else
         Cancel=True
    End If
End With
End Sub

 

[Cronk]

@Rakesh935


You are trying to make Access work like a spreadsheet. Your table is not normalized. Just record the "inputval" (I would have thought it an output) and use a query to determine the balance on hand.


What happens if you have 1000 records and the 547th record is amended? Or if another record is inserted in the middle, or a duplicate record is found and deleted?


If you persist with your design, I can predict you'll be back here asking how to fix such problems when they arise.



A search will show plenty of stock control examples on this site and others.

 

[arnelgp]

regarding, that concern, you can always use the Form's AfterUpdate Event to correct the amount:

Private Sub Form_AfterUpdate()
Dim dblCarry As Double
With Me.RecordsetClone
	If Not (.Bof and .Eof) Then
		.Movefirst
		dblCarry=!ActualValue-!InputValue
		.MoveNext
		While Not .Eof()
			.Edit
			!ActualValue=dblCarry
			.Update
			dblCarry = !ActualValue-!InputValue
			.MoveNext
		Wend
	End If
End With
Me.Recalc
End Sub

 

[Rakesh935]

Hey Pat,

I understand your point. I will try to share a sample of my database in some time to explain my requirement.

My database is of 1.5mb, let me check if I can upload it.

Thanks,
Rakesh