Link to home
Start Free TrialLog in
Avatar of vfv
vfv

asked on

Running Balance, Elapsed days

I added three column on "Inventory Report", Running Balance, Days In Storage, Storage Charge and I also added related source code in Private Sub ReportHeader_Format and Detail_Format. I am planning to convert this into Access 2007. I hope whatever changes I have to make, it will work on Access 2007 too. I attached the file for your information.
I appreciate your help.
vfv
db1.mdb
Avatar of GRayL
GRayL
Flag of Canada image

In Help type - Running Sum its all there
Avatar of Jeffrey Coachman
vfv,

You really should consider taking a closer look at the design of your report.

1. The "Fields" you are trying to create, should really be created in the underlying RecordSource, not in the report.

Standard Report Design theory states that  the query should do the "Heavy Lifting" not the report.

JeffCoachman
Hi Jeff:  I consider adding the running sum function in the Detail of a report, somewhat less than 'heavy lifting'.  What are you seeing that I'm not?
Ray,

When I open the report, the first thing I was confronted with was three successive Parameter prompts, followed by a "Stack OverFlow" error.

So right away, I knew that something was amiss.
;-)

So I opened the Report in design view, and I notice that none of these "Fields" even exist in the source tables.
They all appear to be created in code.

Looking at the code is where I stopped, because I really did not have the time/energy to figure out:
- why code was even needed
- what the code was doing
- why were there "Dims" in the declarations section
...ect.

So I guess in retrospect I should have asked:
1. "Is this report returning what you want, just without the running sums?"
2. "Why is code needed for this?"

Lastly, it was not even clear, (again, to me at least), what the asker wanted a running sum of?

With all that being said, perhaps I should have left it at your first post.

You always seem to "see" things in a question that most other Experts miss.
;-)

So I will leave this to you.

Sorry for any confusion I may have caused.

Jeff

Thanks Jeff,  teach me to skim the surface;-)  Thanks for your appreciation.  

vfv:  Jeff raises a bunch of valid points.  I don't understand your Inventory Transactions table.  You appear to have just added the fields to a report, showing this is where I want things to happen?  What did the report do before you make the changes outlined in your question?  That would be good to know.  Perhaps upload a new mdb?
Avatar of vfv
vfv

ASKER

Thank you Ray and Jeff. I appreciate your comments and I fully understand your points. I created the attached database in 2004 and it was working fine until I tried to use it in Access 2007. If you try to run it in Access 2003 it does compute the three fields in questions by using the source code I created in report VB. I am not claiming I am expert in this but I just need help making this to work in Access 2007. Again I appreciate your time in resolving this problem. Would it be possible, to send me the file after you make the correction.
Thanks so much
vfv
Pdb1.mdb
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Finally,

I am no expert on inventory management reporting (QtyIn, QtyOut, LIFO/FIFO, ...ect) so there may very well be a better way to create this report.
You should research this.

Jeff
There have been many posts here about code that worked fine for years in Access 2003, but now fails in Access 2007.

It appears that MS has "Tightened" up the code Syntax in Access 2007.
;-)

Your best bet is to leave the DB in the 2003 format, if the DB will be run on machines with either version.
Create a new 2003 format DB and impaort all of the objects into it.
To be sure, Compile the code and run the Compact/Repair utility.
*Make sure that you do not make any changes to the design of the database in Access 2007.

Jeff
Avatar of vfv

ASKER

Jeff, wonderful job, I also appreciate for the tips and the links. I wanted to let you know how much I appreciate the extra time you put into modifying the source code. I sure would like to raise another issue, I am having, it would be a new posting right after this. I hope you would have the time to look into this.
Thank so much,
vfv
Thanks.

<I sure would like to raise another issue, I am having, it would be a new posting right after this. I hope you would have the time to look into this.>
There are scores of great Experts here, many of them have MS Access skills that exceeds my own.

But if you post the link to this new Q here, I'll take a look.

;-)

Jeff