validate a field based on sum of other fields

[lela2a]

I have to assign commission to agents for each "order". I can have anywhere
from 1 agent at 100% commission to 5 agents with every possible combination
of percentages. Obviously, I need the percentage entries to only add up to
100 and I'd like to force the user to adjust the figures until they total 100.

Firstly, am I right in assuming it would be better to be assigning the
agents to the orderIDs in separate records: OrderID, Agent, Agent Percentage
(as opposed to having one record contain the OrderID, Agent1,
Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage,
etc...)? The former method seems better for developing reports of the agent
total commissions, but the latter seems easier for accomplishing this
particular goal of data input.

I hope I explained this in as non-stupid a way as possible! Thanks ahead of
time for your patience!

 

[Jeanette Cunningham]

lela,
One order can have many agents.
One agent can be on many different orders.
The above is a many to many relationship.

Use 3 tables
a table for Orders
a table for Agents
a Commission table that has the OrderID from the Orders table and the
AgentID from the agents table

there is a one to many relationship between Orders and Commissions
there is a one to many relationship between Agents and Commissions

Once you have the tables set up like this, it will be easier to see how to
set up the form to set commission to 100%

Jeanette Cunningham

 

[lela2a]

I knew I wouldn't explain it properly. Yes, I have an Order table, an Agent
table and the table I referred to as "assigning the agents to the orderIDs in
separate records" - you referred to it as "a Commission table that has the
OrderID from the Orders table and the AgentID from the agents table."

Sorry, nothing jumped out at me any better now than it did before your hint.
Could you be A LOT more specific? Tx.

 

[Jeanette Cunningham]

A form that shows all the agents for the same order would be easier to make
the commissions add up to 100%

Here is a way to set up the forms for a many to many relationship - is this
what you are asking?

Use a main form (single view) for one of the Many side tables. Within the
main form, use a continuous subform bound to the junction table with the
Link Master set to the main form's PK field. This subform has at least one
visible control that is a combo box bound to the second table's PK field.
This form's Current event and the combo's AfterUpdate event sets an
invisible text box on the main form to the value of the combo box.

Along side the continuous subform is another subform bound to the other many
side table with its Link Master property set to the invisible text box.

With this arrangement, you can navigate or search for the main form record.
The continuous subform displays the items assigned to the main form record.
Clicking on a record in the continuous subform automatically displays the
related
record in the other subform.

If you already have forms similar to the above, would you mind explaining
your question in more detail.

Jeanette Cunningham

 

[lela2a]

Thanks, Jeanette. I think this will take some time to digest and apply.

What I have set up now is one main form which is assigning the agent and
commission to the orderID. I put a subform on it which shows all the agents
assigned to it and I calculated a box to total the percentages. By your
explanation...which I'm having trouble with (what's a PK?) I think I need an
event on ...the percentage assigned field (?) ... but how do you get it to
wait until the user has entered all the agents they were planning on? All I
can envision is having it alert when the totals do not equal 100, but that is
going to happen on each entry until they have entered all of them - in some
cases 5 in all.

I'm not really understanding the hidden boxes or their purpose.

Sorry, I know the solution is in your answer but you're over my head right
now. Thanks so much for trying!!

 

[Jeanette Cunningham]

PK is an abbreviation for the primary key field.
The hidden text box is to keep the second subform in sync as you move to
another rexord on the 1st subform.
If you are only using 1 subform at the moment, just forget about the hidden
textbox.

The calculated percentage can update each time a percent is assigned to an
agent.
What you can do is to check the sum of percentages, to see if it is over 100
when you are saving the record for that OrderID.

On the before update event for the subform put code that checks the sum of
all the percentages entered.
If the sum is greater than 100 then you set Cancel = True and pop up a
msgbox telling the user about the problem.

Jeanette Cunningham

 

[lela2a]

Phew! I understand this one! Thanks, lots, Jeanette. l.