Access - Carry forward values to new record (1 Viewer)

Rakesh935

Registered User.
Local time
Tomorrow, 03:01
Joined
Oct 14, 2012
Messages
71
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:31
Joined
May 7, 2009
Messages
19,169
use the Form's BeforeInsert event to carry forward those values:
Code:
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
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 08:31
Joined
Jul 4, 2013
Messages
2,770
@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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:31
Joined
May 7, 2009
Messages
19,169
regarding, that concern, you can always use the Form's AfterUpdate Event to correct the amount:
Code:
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:31
Joined
Feb 19, 2002
Messages
42,976
This is a very bad design. In a relational database, each row should be independent of any other row and sort order should be immaterial. You are forcing a dependency and a fixed row sequence when there should be none.

There are other ways to achieve your goal of balancing a set of data. Usually this is done with temp tables where the set of data is stored until it balances and then the data is applied to the main table.

If you provide more information on WHAT your requirement is, we can suggest a better HOW.
 

Rakesh935

Registered User.
Local time
Tomorrow, 03:01
Joined
Oct 14, 2012
Messages
71
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:31
Joined
Feb 19, 2002
Messages
42,976
Be sure to obfuscate any sensitive data and zip the db before uploading.
 

Users who are viewing this thread

Top Bottom