Previous Record Value in Module 1

[SKIGAL]

Please help! I have a form that captures the Beginning Balance, Items Received, Items Processed, and Remaining Balance. For each new record added, I want the current record's beginning balance to be the previous records' ending balance.

I have tried using MovePrevious and Find Previous, along with several days worth of other things. Can anyone point me in the right direction?

Thanks in advance for your help!

 

[vbajock]

It would help if you posted your code.

 

[Bullsandbears123]

Well, I would make a command button on the form that copies the text boxes value for "Remaining balance" then make new record. And set the Beginning value the saved value.
The code should look something like this.
(you'll need to change the text box names to yours)


Private Sub cmdadd_Click()
On Error GoTo Err_cmdadd_Click

dim mybalance as double

mybalance = remainingbalancetextbox.value


DoCmd.GoToRecord , , acNewRec

beginingbalancetextbox.value = mybalance


Exit_cmdadd_Click:
Exit Sub

Err_cmdadd_Click:
msgbox Err.Description
Resume Exit_cmdadd_Click

End Sub

 

[SKIGAL]

Sorry about not posting the code. I've tried so many things with it that it's all screwed up now.

Thanks Bullsandbears123 for the tip. It does seem a lot easier to do it that way.

 

[SKIGAL]

That was a good idea Bullsandbears123. However, I have multiple items being received on the same form. And the user can only add records and cannot see the previous days record (unless they have permission) - it's a data integrity issue. So, when the user opens the form, there is only a blank record there, therefore I have to go find the value from the previous record.

This is what I've got so far. For every new record added, the [remaining balance] from the first record is always returned. Can anyone assist me with this?

Function MRPFBegBal()

Dim dbs As Database, rst As Recordset
Dim strcriteria As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblWhlSaleInv", dbOpenDynaset)
strcriteria = "[Date] = " & Forms![frmWhlSaleInv]![Date]

If rst.BOF Then
MRPFBegBal = 0
Else
rst.FindPrevious strcriteria
MRPFBegBal = rst![MRPF-File Remaining]
End If
rst.Close
Set dbs = Nothing
End Function

 

[Bullsandbears123]

I would possibly add a autonumber Transaction number to the table then use this....

Function MRPFBegBal()

Dim dbs As Database, rst As Recordset
Dim strcriteria As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblWhlSaleInv", dbOpenDynaset)
strcriteria = &quot;[Transactionnumber] < &quot; & Forms![frmWhlSaleInv]![Transactionnumber]

rst.movelast

If rst.BOF Then
MRPFBegBal = 0
Else
MRPFBegBal = rst.Fields(&quot;MRPF-File Remaining&quot
End If
rst.Close
Set dbs = Nothing
End Function

 

[DanJR]

What happens when a user edits an existing record? The balances will get out of synch. If this is an issue then another way is to not store the begin and end balances in a table, but instead create the balances on the fly e.g.:

In the form, set the control source property of the start balance textbox to:
[tt]=GetBeginBalance()[/tt]

and set the control source property of the End Balance to:
[tt]=[txtBeginBalance]+[Items Received]-[Items Processed][/tt]

then the code for the form:
[tt]
Option Compare Database
Option Explicit

Private Function RequeryBalance()
Me.txtBegin.Requery
End Function

Public Function GetBeginBalance() As Double
Dim rs As DAO.Recordset
Dim strSQL As String

If IsNull(Me!TransactionNumber) Then
strSQL = &quot;SELECT Sum(([Items Received]-[Items Processed])) AS Balance &quot; & _
&quot;FROM tblTransaction &quot;
Else
strSQL = &quot;SELECT Sum(([Items Received]-[Items Processed])) AS Balance &quot; & _
&quot;FROM tblTransaction &quot; & _
&quot;WHERE [TransactionNumber] < &quot; & Me!TransactionNumber
End If

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)


If (rs.BOF And rs.EOF) Then
GetBeginBalance = 0
GoTo Cleanup
End If

If Not IsNull(rs!Balance) Then
GetBeginBalance = rs!Balance
Else
GetBeginBalance = 0
End If

Cleanup:
Set rs = Nothing

End Function

Private Sub Form_AfterUpdate()
Call RequeryBalance
End Sub
[/tt]

Just an alternative thought.
Cheers,
Dan