Need ideas on making a functional circular reference??? 2

[Johnnycat1]

Ok this is what I am trying to do...

I have a simple form that has the folliwing cells.

[ProductQuantity]
[Cost]
[Margin%]
[Sell]

[ProductQuantity] & [Cost] are numbers entered by the user

The user also needs to enter either the [margin%] or the [sell]. If they enter the [Margin%] then the [Sell] needs to be a result of the following formula:

[ProductQuantity] x [Cost]/(1-[margin%])

If they enter the [Sell] then [Margin%] needs to be the result of the following formula:

([Sell]-([ProductQuantity] x [cost]))/[Sell]

The issue is that I need to be able to give the user the ability to pick either cell [Sell] or [Margin] to enter their data.

Is is possible to have the default value of [Sell] created by the [ProductQuantity] x [Cost]/(1-[margin%] formula but in the On Enter event of the [Sell] have the [Margin%] re-calculated from the ([Sell]-([ProductQuantity] x [cost]))/[Sell] formula?

I am open to any other methods also.

As always, I really appreciate the help that you are willing to give.

 

[ck1999]

Can you use option buttons

1st option label % Margin
2nd option label Sell

then in the frame on click event procedure

if option1 then
txtsell = [ProductQuantity] x [Cost]/(1-[margin%])
txtmargin = ""
else if option2 then
txtmargin = ([Sell]-([ProductQuantity] x [cost]))/[Sell]
txtsell= ""
end if

you are going to have to change for the correct syntax for choosing option and your textbox names

ck1999

 

[TheAceMan1]

How are ya Johnnycat1 . . .

The first thing you nee to do is, in your equations change [blue]x[/blue] to [blue]*[/blue]!

There's no need to go thru all your trying to do. Just use the [blue]AfterUpdate[/blue] event of [blue]Margin%[/blue] & [blue]Sell[/blue] to call the following routine (copy/paste to the forms code module):

[blue]Public Sub ProperCalc(CtlName As String)
   
   If CtlName = "Margin%" Then
      Me!Sell = Me!ProductQuantity * Me!cost / (1 - Me!Margin%)
   ElseIf CtlName = "Sell" Then
      Me!Margin% = Me!Sell - (Me!ProductQuantity * Me!cost) / Me!Sell
   End If

End Sub[/blue]

The call from the [blue]AfterUpdate[/blue] event of [blue]Margin%[/blue] would look like:

[blue]   Call ProperCalc("Margin%")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Johnnycat1]

Once again... AceMan1 to the rescue.

I really appreciate your help.

I know about the x and the * I just used the x in lieu of the * in the text of my thread. The code has always been *.

As for your code, it is excellent. I have not used the Call method before but it is exactly what I needed.

However, when I change the value "Margin%" it instantly changes the value of the "Sell" just like it should but when I change the value of the "Sell" it does not write the new "Margin%" value until I go back into the "Margin%" cell. Only then is "Margin%" updated.

Shouldn't the After_Update of "Sell" run the ProperCalc instantly?

Again, you are a lifesaver!

 

[TheAceMan1]

[blue]Shouldn't the After_Update of "Sell" run the ProperCalc instantly?[/blue]

Yes it should! I showed you what the [blue]AfterUpdate[/blue] event should be for [blue]Margin%[/blue]. The same needs to be done for the [blue]AfterUpdate[/blue] event of [blue]Sell![/blue].

[blue]   Call ProperCalc("Sell")[/blue]

This also brings to light the [purple]use of a common routine![/purple] . . .

[blue]Cheers!.[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886