By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

I got to be making calculations too complicated

P: 8
I'm coming from a Corel paradox background and moving into an Access environment. So I'm struggling with something that I think is probably way simpler than I'm making it out to be.

Access 2007
WindowsXP SP2

What I have is a table in a subform that tracks dollars spent per project
There is sub sub form that breaks down each dollar amount per fund. For example: sewer/water/streets/parks. In the sub sub form the fields I'm having trouble with is Percentage and CalcValue. What is desired is for a project manager to be able to enter the percentage of the cost that is fund related and then have the CalcValue field become populated with the correct dollar amount. I want the data stored in its own table for other purposes, so that we can then find out what all the Sewer related costs were for example.

So, if a street rehab project had a project survey cost of $100,000.00 and 20% was related to Sewer, 30% was Water and 50% was Street related then the CalcValue fields should automatically populate $20,000, $30,000, and $50,000 in the child table respectively.

I tried making a query object, which was ok but wouldn't allow me to add new records. No good.

I made a table that I could populate but couldn't figure out how to get expression builder to sucessfully recognize a related field in a higher level sub form and then calculate off of said field.
=[CalcValue]=[Funding]![Amount]*[Funding Source]![Percentage]

So I moved onto VBA script trying to combine the two; a table and an update query that would run when the Percentage field was departed or changed. And after some hunting on the forums here I came up with this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Percentage_Change()
  2.  
  3.       On Error GoTo Err_Update_Click
  4.  
  5.           Dim stDocName As String
  6.  
  7.          stDocName = "CalcForFundSrcSubform"
  8.          DoCmd.OpenQuery stDocName
  9.          Me.Requery
  10.  
  11. Exit_Update_Click:
  12.     Exit Sub
  13.  
  14. End Sub
  15.  


[QUERY]
UPDATE Funding INNER JOIN [Funding Source] ON Funding.FundingID = [Funding Source].FundingIDLink SET [Funding Source].CalcField = [Funding]![Amount]*[Funding Source]![Percentage]
WHERE ((([Funding Source].CalcField) Is Null));
[/QUERY]


So, after all this. I'm getting to think two things. First, I'm missing Paradox; I'd be so done by now. Second, I'm guessing that I'm making this waaaay to complicated. Is there something that I'm missing? What am I doing wrong.

Mark
Sep 14 '07 #1
Share this Question
Share on Google+
7 Replies


P: 8
I have figured out part of the problem. Microsoft's security tripped me up. It was protecting me and making so none of the code was running.

I'm now back to trying to make an expression work. I'm putting it "on click" so I can verify when the code runs.

Expand|Select|Wrap|Line Numbers
  1. =[Forms]![ProjectBudgetForm]![FundingSubform].[Form]![Amount]*[Percentage]
  2.  
Sep 14 '07 #2

nico5038
Expert 2.5K+
P: 3,072
Hi Mark,

Basically we don't store result like the calculated value in a table because of "data-redundancy".
Just imagine that the sewer price goes up and you "forget" to correct the calculated value :-(

The general solution is to use queries to calculate the result dynamically from the "basic" data. This will also allow the additon of any number of "sewer" and/or other part.

Getting the idea ?

Nic;o)
Sep 14 '07 #3

P: 8
Hi Mark,

Basically we don't store result like the calculated value in a table because of "data-redundancy".
Just imagine that the sewer price goes up and you "forget" to correct the calculated value :-(

The general solution is to use queries to calculate the result dynamically from the "basic" data. This will also allow the additon of any number of "sewer" and/or other part.

Getting the idea ?

Nic;o)
Yep, sure do. The amount of the bills aren't likely to change so I hadn't considered that. But you are correct, it is possible.
And again, little differences I am learning. In Paradox the form is built so that if you changed a value in a related field all the related values would change downstream. Not unlike a spreadsheet. It's one of the differences I'm getting used to.
This is exactly the clarification I'm needing, Thank you!

So, I set up a control, not bound to anything, stack it in with the other fields (I'm still getting over how the interface operates, it is odd) and have made it a straight calculated field. It achieves the desired results, as long as the correct record is selected before you push the (+) symbol next to the record.

So how do I force focus to a record when a user push the plus symbol next to the chosen record?
Sep 14 '07 #4

nico5038
Expert 2.5K+
P: 3,072
Hmm, I guess you're using linked tables as you mention the + sign.

I normally start with defining a "normal" form, in your case based on a project, and add a subform by pressing that button in design mode.
Next the Access wizard will propose to link the subform by a field and you can acceopt that proposal to have Access sync the main (project) and the sub (costs).

The subform will be by default in datasheet format, but when you change it to "Continuous" (see the properties under the Format tab), then you can use a footer that's holding =Sum([detailsectionfieldname]) to get totals.

Nic;o)
Sep 15 '07 #5

P: 8
Thanks. But I think what I need is to get more familiar with the Access environment and how to force record focus.

Here's the thing, I have a form with two sub forms by linked fields.

Base(projects) >> Sub(costs) >> Sub Sub(cost breakdown)

The base form is simply that, it shows project name, which displays the first sub form by way of a datasheet of costs below it. Alongside each datasheet entry is a plus (+) symbol which will display another datasheet (sub-sub form) of breakdown when pressed.

Unfortunately if there is more than one cost entry (middle form) and you just click on the plus symbol of the last entry what happens is the form expands and then the calculated fields in the sub-sub calculate form the incorrect sub form record; as the focused record in the sub form is still the first record. Clicking on the plus symbol does not change record focus.

So to get correct values on the sub-sub form, a person needs to first click the record to attain focus and then the plus symbol to assure correct calculations.

What I want to know is how do I make it so that if a user just selects the plus symbol, record focus is set to the record alongside the corresponding plus symbol?

Hmm, I guess you're using linked tables as you mention the + sign.

I normally start with defining a "normal" form, in your case based on a project, and add a subform by pressing that button in design mode.
Next the Access wizard will propose to link the subform by a field and you can acceopt that proposal to have Access sync the main (project) and the sub (costs).

The subform will be by default in datasheet format, but when you change it to "Continuous" (see the properties under the Format tab), then you can use a footer that's holding =Sum([detailsectionfieldname]) to get totals.

Nic;o)
Sep 17 '07 #6

P: 8
SOLVED.

So my problem was not with forcing focus, but rather the expression that calculated the field in the datasheet.

I had this before:
=[Forms]![ProjectBudgetForm]![FundingSubform].[Form]![Amount]*[Percentage]

Which did the job. Unfortunately as a calculated field on a sub sub form it started to return some strange results as I bounced around on the sub form.

What I wanted was the ability to find a conditional value, that is to say, I need the dollar amound from this table when the ID fields match. I found the command DLookUP:
=DLookUp("[Amount]","Funding","[FundingID]=" &[FundingIDLink])*[Percentage]

Now my forms are showing the correct values when and where I need them to.
Sep 17 '07 #7

P: 1
I'm coming from a Corel paradox background and moving into an Access environment. So I'm struggling with something that I think is probably way simpler than I'm making it out to be.

Access 2007
WindowsXP SP2

What I have is a table in a subform that tracks dollars spent per project
There is sub sub form that breaks down each dollar amount per fund. For example: sewer/water/streets/parks. In the sub sub form the fields I'm having trouble with is Percentage and CalcValue. What is desired is for a project manager to be able to enter the percentage of the cost that is fund related and then have the CalcValue field become populated with the correct dollar amount. I want the data stored in its own table for other purposes, so that we can then find out what all the Sewer related costs were for example.

So, if a street rehab project had a project survey cost of $100,000.00 and 20% was related to Sewer, 30% was Water and 50% was Street related then the CalcValue fields should automatically populate $20,000, $30,000, and $50,000 in the child table respectively.

I tried making a query object, which was ok but wouldn't allow me to add new records. No good.

I made a table that I could populate but couldn't figure out how to get expression builder to sucessfully recognize a related field in a higher level sub form and then calculate off of said field.
=[CalcValue]=[Funding]![Amount]*[Funding Source]![Percentage]

So I moved onto VBA script trying to combine the two; a table and an update query that would run when the Percentage field was departed or changed. And after some hunting on the forums here I came up with this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Percentage_Change()
  2.  
  3.       On Error GoTo Err_Update_Click
  4.  
  5.           Dim stDocName As String
  6.  
  7.          stDocName = "CalcForFundSrcSubform"
  8.          DoCmd.OpenQuery stDocName
  9.          Me.Requery
  10.  
  11. Exit_Update_Click:
  12.     Exit Sub
  13.  
  14. End Sub
  15.  


[QUERY]
UPDATE Funding INNER JOIN [Funding Source] ON Funding.FundingID = [Funding Source].FundingIDLink SET [Funding Source].CalcField = [Funding]![Amount]*[Funding Source]![Percentage]
WHERE ((([Funding Source].CalcField) Is Null));
[/QUERY]


So, after all this. I'm getting to think two things. First, I'm missing Paradox; I'd be so done by now. Second, I'm guessing that I'm making this waaaay to complicated. Is there something that I'm missing? What am I doing wrong.

Mark
Dear Sir/Madam

I want learn programming with Corel Paradox 12, Would help me ?

My email address "appajar789@gmail.com"

Pl replay

thanks
Feb 8 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.