Bringing forward a data in a form

[cwwaicw311]

It is possible that in an Access form whereby for example, I have a CurrentBalance and after some transaction a NewBalance is calculated. Then when I save n close the form, when i open the form second time to do another transaction the NewBalance value would become the New CurrentBalance value so that the transaction can continue. Which mean that the NewBalance value can always be carried forward to do another transaction.


All this operation is done in the form n the all the data would also store in the table.

[MAF4Fam6]

After reviewing your last thread and the last copy of your database, I believe what you are trying to achieve is not feasible without using a calculated field within a Query. As RuralGuy mentioned on your last thread...it's simply not a good idea to store calculated values inside Tables.

Consider the attached modified Access database.

1. CurrentBalance and NewBalance fields were removed from the table.

2. Query was created and added a custom field named Product (Receive minus Issue).

3. I changed the Form's Record Source to the Query (you should always be using Queries as the Record Source to Forms).

4. CurrentBalance and NewBalance Text Boxes on your Form were moved to the Header Section:

a) DSum Function was used to display the CurrentBalance from the Query (changed the Text Box's Format to General Number in the Property Sheet).

b) Finally, I used/kept RG's calculation for the NewBalance Text Box.

I added a few records which result in a current balance of 900. Try adding a few records and see if this is what you are trying to achieve.

-RC

[cwwaicw311]

Thank, ur attached modified version really solve a lot of my problem.

[MAF4Fam6]

Your welcome.
Best of luck on the rest of your project.

-RC

[cwwaicw311]

Hi, there is a problem I hope u can help me solve. Since the modified version of ur do not store the CurrentBalance Value and NewBalance value. Is there anyway that both the value can be store either in table or in the query?

[MAF4Fam6]

If you don't mind me asking...
What is the rationale for having these values stored in a Table?
The calculated balances can always be regenerated in a Form/Report through use of another Query/Queries.

-RC

[cwwaicw311]

Actually is for recording and checking purposes. In the current spreadsheet the Current and NewBalance only show in the form. Whereas in the query only show the balance of receive - issue.

So maybe there is some record not has to be in a table to show the CurrentBalance so that it can act like sone sort of a inventory checking.

[MAF4Fam6]

My recommendation would be to create a report which lists your transactions (inventory). And then...place the Current Balance in the Header or Footer of the Report.

-RC

[cwwaicw311]

Ok, I have created the form which show all the transaction i make. But how do I transfer the value of CurrentBalance from the form to the report. Sorry with all this question, but I just started using Access not too long ago so do not know all function of Access yet. But I am learning quite with this project.

[MAF4Fam6]

No need to create a Form to create a Report.
You can create a report using the Report Wizard (use the Query (NOT the table) as the Record Source for the report).

Once you are done building the report, Add a Text Box in the Header/Footer and use the DSum Function to give you the Current Balance.

You can keep the most recent Form you built to make corrections to your previous transactions. Any correction to a previous transaction will automatically change the Current Balance in the Form or Report. We're human...and we make mistakes.

Post back if you need further assistance.

-RC