Calculations with values in forms & subforms

[Lithara]

Hi Everyone! Bear with me, I'm new to Access and I may not understand or use all the terminology correctly.

Background: I'm creating a database to track the inspections of many businesses. Each business will have many inspections throughout the year and each inspection has many items. I have a form set up which includes Business info and Inspection info (#items inspected, subtotal for inspection) on the main form, and Items in error listed on a subform (no error=no items listed).

Problem 1: I need to make a count of all items that are listed. I can do this in the subform, with no problem (in one of the footers #Discrep:count([item])) but now I need to report and use that value on the main form to calculate the percent error for the entire inspection. I don't know how to pull in onto the main form to set up my calculation.

Problem 2: This one is related and is probably the same fix. I have PriceCharged and PostedPrice for each item. I need to calculate the net error and then, using the subtotal on the main form, calculate the % dollar error.

Any suggestions, oh great and powerful gurus of the Access world? I hope I explained that clearly enough for you.
Thank you very much in advance!
~Lith

 

[mresann]

Problem 1: Open the subform for editing that supplies the value for (Discrep:count([item]). Keep in mind, you will have to close the main form to do this operation if you have the subform bound to a subform CONTROL on the main form. Click the VBA module code button. On the VBA module for the subform, find a procedure for Form_Current. I assume the subform textbox control name that contains the value you want displayed on the main form is called "Discrep". Let's say the text box on the main form you want displayed is called "txtDiscrep". Put this code in the Form_Current procedure:

Me.Parent!txtDiscrep.Value = Me.Discrep.Value

In addition, go to this site for the coding protocol between forms and subforms to two layers:

Subform Coding Protocols

Problem 2:
I need a little more information on where the values are coming from, that is PriceCharged and QuotedPrice. In any case, if you need a percentage of how much off the Quoted Price is from the Price Charged, then using the Quoted Price as the basis for calculaton, and try:

[Discrep.Value] = abs((([PriceCharged]-[QuotedPrice])*100)/[PriceCharged])

That will get you a percentage amount. If you want to use positive or negative amounts in your percentage, remove the abs function.

I hope this gives you a direction to move. Good luck!

 

[Lithara]

Thanks for responding, unfortunately you lost me at VBA. I know nothing about VBA programming except that it's visual basic. Is there a way of creating a control on the mainform and, using the properties function, report what I need? I've tried the following:

in sbfmScanInsp form footer (calculated field made invisible):
discre: IIf([ItemName]="No error",0,Count([ItemName]))
(and I can get the correct value when I make it visible.)

then in my frmScannerInsp (my main form, in the detail section):
discrepency field =[sbfmScanInsp].Form![Discre]

but all I get on my main form is: #Name?

I've tried some of the suggestions from other posts but I can't get it to work.

As for Problem 2.
I have a subtotal on my main report and need to calculate the percentge error from that. On paper, the calculation is either:

1) total %err = ((sum(PriceCharged)-sum(PostedPrice))/Subtotal)*100
or
2) =(Sum(error)/Subtotal) *100

where the values for the sum of the price charged and posted price or total error is on the subform and the Subtotal is on the main form. But I think if I can get the two forms to talk to each other I can solve that problem fairly easily! Thanks for your input!
~Lith

P.S. I went to the suggested site, it looks like it might have a lot of useful information but I still couldn't get the syntax correct. I'm not sure I understood the examples, maybe when I get a little more experience under my belt.

 

[Lithara]

OK, I think I figured it out. In case anyone wants to know: For problem 1) I was using the wrong syntax in the control source line found in properties.
And for problem 2) I created a query of the table I was using with the calculated fields that I needed. Then I created a new subform based on the query to replace the old one based only on a table. The main form and subform now talk to each other and I can perform further calculations with the values in both forms. Now I need to figure out how to Count only the lines that are marked as overcharge and a separate count of undercharges only....
Well that's another problem, but if anyone has any suggestions, I'm all ears.

Thanks!
~Lith