calculated control help

[MarkRCC]

Hi.

I'm working on a form with a related subform. On the form, I use a combo box to find a name(person or company which then populates the main form with that persons info. Works great. I then tab down to the subform which allows me to enter transactions for the person or company listed on the main form. Again, this works ok except that I want to perform some calculations and display them on the subform using an unbound text box. I've written the formula in the control's "control source" property, but all it returns is #Name?. The basic formula is as follows:

base commission (which is manually entered by the user in the subform) * extended commission rate(which is a percentage) that is stored with the broker's static info- LN, FN, Address, etc)

For example: A broker, John Smith, has an extended commission rate of 170% and for this particular transaction get's a base commission of $100.00 (this amount is entered for each transaction by the user).

This may just be a syntax issue, but I've tried rewriting it about a hundred ways.

Anyone have any suggestions?

TIA

Mark

 

[Beejay57]

Have you preceded you calculation with =
eg: =[baserate]*100

 

[Krystoff]

Whats the actual formula that you are using?

What it sounds like is that the extended commission is on the Main Form and the Formula is on the subform. So it would look like this:

=BaseCommission * Me.Parent.ExtendedCommission

HTH

Chris

 

[MarkRCC]

Hi.

To Beejay57: Yep. I've included the "=" sign at the beginning.

To Krystoff: The extended commission is indeed on the main form and the formula is being calculated as an unbound text box on the subform. I tried typing in the formula using the syntax you mentioned. Still getting the #Name? message.

What happens is that the user selects a broker on the main form. This broker's addy info and extended commission data appear. The user then tabs down to the subform and enters the Insurance Company that the broker had sale(s) for using a combobox. This automatically fills in a field on the subform listing that company's commission rate. The user then fills in the broker's base commission rate for that sale. With this information, the broker's commission for that sale, as well as my company's "cut" can be calculated. I want this info to show up on the screen so the user can verify all is correct.

Any other thoughts?

Many TIA!

Mark

 

[Krystoff]

Ok got it to work and my syntax was a bit messed up. The "Me." syntax works in VBA but doesn't appear to in the actual form.

So the correct syntax is:

=BaseCommission * Parent.ExtendedCommision

That should do it for you.

HTH

Chris

 

[MarkRCC]

Hi Chris.

We must be on the same wave length. I just got it yesterday while watching the NASCAR race <Hats off to the rednecks, eh??>. It worked perfectly.

Now to kick it up a notch. I have a similar calculated text box on the same form/subform (using the same inputs). The difference is that the insurance company the broker worked for needs to be tested as the formula for the calculation may be different depending on the company or even entered manually.

For example, John Smith gets a base commission of $100.00 and an extended commission of 170%. He worked for ABC Company, LMN Company, AND XYZ Company this month. The commission calculations would be as follows:

ABC Company: base commission * extented commission
LMN Company: base commission * 40%
XYZ Company: user enters actual amount

Is it possible to use a CASE statement to test which company has been selected and place the correct formula in the ControlSource of the appropriate calculated text box?

As usual, many, many TIAs for all your help and suggestions!

Mark

 

[Krystoff]

Sure thing, it can easily test that. However you will need to do it in code as I think an Iif statement would be way to long.

There are a couple things to think about though. I would probably place the code in the AfterUpdate Event of the Insurance Company field. That way if a user goes back and changes the Insurance Co. the text field gets updated.

So here is what I would do.


***************************
Select Case InsCo
Case &quot;ABC Company&quot;
Me.BaseCommission.ControlSource = &quot;=BaseCommission * Parent.ExtendedCommission&quot;

Case &quot;LMN Compnay&quot;
Me.BaseCommission.ControlSource = &quot;=BaseCommission * .40&quot;

Case &quot;XYZ Company&quot;
' This is the one Im not sure about as I don't know if you want the user to enter the calculation?

Case Else
' This is what it defaults to if something is selected thats not in any of the previous case stmts.

End Select

*********************************

That should work. I used what we have been using already in my code. If you know VBA code a little better you can make the textbox call the code continuously.

IE:
Make the code for this into a module instead of on the form. Then call the function from the textbox. If you want to go that way and don't know how let me know and I will see if I can help you out.

Chris

 

[MarkRCC]

Thanks Chris.

On a whim this morning I added a field to my &quot;CompanyInfo&quot; table which called &quot;ORFormula&quot;...I manually entered the formula required for each company (there's only about 20 different companies so it only took a short time). On the subform, I created an unbound calculated text box and placed the field name in the &quot;ControlSource&quot; in three different ways as follows:

[tblCompanyInfo]![Formula]
Formula
[Formula]

I'm getting the #Name? error no matter what.

When I create a new field in an existing table, are we not
able to use the field? Any idea what the proper syntax would be?

Again, MANY TIAs!

Mark

 

[MarkRCC]

One big OOPS on my last post.

I just realized that I'm getting errors because it won't place the formula from the formula field into the control source property and process the formula. It's trying to place the actual formula string into the unbound field.

At least that's what I think it's trying to do...

Mark

 

[MarkRCC]

Ok.

I just sh**canned the above &quot;trial&quot; and went with the case statement.

I followed Krystoff's (Chris) advice and created a CASE statement in the AfterUpdate property of the Company Name box on my subform. I'm not getting any error messages but nothing is showing up in the &quot;base commission&quot; ControlSource text box.

Any suggestions or ideas why?

TIA!!

Mark

 

[MarkRCC]

<patting myself on the back>

I realized that the SELECT CASE InsCo line was wrong. To make it work, I had to make &quot;InsCO&quot; match the field that the CASE statements was testing. It was spelled wrong. DOH.

Anyway. MANY thanks for all the help.

Mark

 

[Krystoff]

Sorry I hadn't checked this for a while. Looks like you got everything working!

Should have told you about the Select Case statement testing the field. Sorry about that.

Have fun and I was glad I could help.

Chris

 

[MarkRCC]

Hi all.

The Select Case statement is working perfectly! In fact I've got a nested Select Case working.

Many thanks for the help. You folks are great!!

Mark

 

[MarkRCC]

One more question if I may.

Unfortunately, I must save an &quot;answer&quot; from one of the calculated controls to my database. I know...it's taboo to save data from a calculated control.

Is it possible to have the calculated control &quot;save&quot; the information to a field in one of my tables? If so, what would be the best way to do it?

I tried to use the &quot;AfterUpdate&quot; property of the calculated control with:
Me.fieldName = Me.calctxtboxName and
Me.<tblName>.<fieldName> = Me.calctxtboxName

I got no error messages using either one, but nothing is being saved to the field.

Any suggestions on this one?

TIA

Mark

 

[WildHare]

I might be wrong, but I wouldn't think a unbound calculated control ever fires an AFTER UPDATE event - my understanding was the after update event fires after the bound control's underlying field is updated in the recordsource...

That being said (and very possibly wrong, anyway..) why not set the field's value after you have updated the last field that makes up part of the calculation. This should be very straightforward.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm