• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1791
  • Last Modified:

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
0
vfv
Asked:
vfv
  • 6
  • 3
  • 2
1 Solution
 
GRayLCommented:
In Help type - Running Sum its all there
0
 
Jeffrey CoachmanCommented:
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
0
 
GRayLCommented:
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?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Jeffrey CoachmanCommented:
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

0
 
GRayLCommented:
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?
0
 
vfvAuthor Commented:
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
0
 
Jeffrey CoachmanCommented:
vfv,

Many issues:

1. Move Public variables to a separate new Module. (It will be confusing to have Report Variables defined in a Form's Code)

2. If you are going to perform mathematical calculations between dissimilar Datatypes (Integer and Currency), consider doing this via a function, so you can set the resulting datatype. (This is not terrible crucial though)

3. Please adopt a standard naming convention.
LNC:
http://en.wikipedia.org/wiki/Leszynski_naming_convention
Or...
RVBA:
http://www.xoc.net/standards/default.asp
In your code, it is almost impossible to tell what is an object, and what is a variable.

4. Avoid using spaces in your object names.

5. Set the VBA option to "Require Variable Declarations". (This adds "Option Explicit" to all new modules and requires you to explicitly declare all variables)

6. Consider modifying this code so that it will return accurate results if more that one Vessel Name is allowed to be selected in the future. (This will be a separate question)

7. *Finally*, the main issue here is that what you are referencing as "Fields" in the textbox control sources are really "Variables" in code, so either unbind them and load the values directly into the controls on the Detail format event, (as I did), or create Functions to retrieve the values, and set the function as the control source.

Here is your sample back.
It is tested and working in Access 2003 and Access 2007.

JeffCoachman

Access-EEQ-24353315-2003CodeNotW.mdb
0
 
Jeffrey CoachmanCommented:
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
0
 
Jeffrey CoachmanCommented:
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
0
 
vfvAuthor Commented:
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
0
 
Jeffrey CoachmanCommented:
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
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now