Validation rule using calculated value from another subform

[Mishanya]

I have form with 2 subforms.
1st subform shows assets' names (securities etc) and its overall
possessions(calculated sums of all transactions money-wise and units-wise)
and is based on query using tblTransactions
2nd subform is TransactionForm - with controls for AssetName,
TransactionKind (Buy-Sell), Price and Quantity and is based on
tblTransactions.
I need to set the Quantity control validation rule, so it won't allow
selling asset that is not in the 1st subform, or selling greater quantity
then the asset overall possession as calculated in the 1st subform.
What shoud I put (in the Validation Rule property? maybe some kind of
=<DLookup?)?

 

[Arvin Meyer [MVP]]

You can refer to a single value in the other subform, but if there are
multiple values, It's better to use DLookup or pull a recordset on the table
or query. You won't be able to use the Validation property, because you
cannot use code in a property, but, you can easily use the Before_Update
event to run the code.

The code shouldn't be too difficult, there are examples of both methods in
the help files. If you need more help, please post back with the relevant
names of all the forms, fields and controls.

 

[Mishanya]

Hello Arvin and thank U 4 reply

1) The 1st subform is multiple-value case (datasheet).
2) I'd started to sketch some Dlookup in BeforeUpdate event in the 1st
place, but then remembered that in the help files "domain" is always a table,
not a form, so I quit (I'm a beginner).
Anyway if U have time to back me up, I''ll appriciate this.

The whole structure is this:

1) MainForm (Single) hosts a few subforms for selected client.
2) Subform1 is datasheet with client' accounts records.
3) Subform2 has 2 subsubforms: AccountAssets and Transactions.

Selecting Subform1 record (click event on account number) loads/reloads a
recordset of Subform2 (AccountAssets - all the assets' possessons in the
selected account and Transactions - entry-form to enter new transaction for
this accont).

Subsuborm AccountAssets is based on query between tblAccounts and
tblTransactions and shows total of all transactions for every asset in the
selected account - Asset, TotalAssetQty and TotalAssetValue (wich is
TotalAssetQty*CurrentPrice).

Form Transactions is based on tblTransactions and has cboSelectAsset,
cboTransactionKind and Quantity controls.

This is it - now I need to to restrict the Quantity control of the
subsubform Transactions, so that, provided cboSelectAsset is selected and
cboTransactionKind control is selected as "Sell", it will check 1) that the
selected asset shows in form AccountAssets (i.e. has positive TotalAssetQty
as only positive totals are queried) and 2) its TotalAssetQty is not less
then entered Quantity. Otherwise it would throw message "Quantity entered is
greater then the account possesses".

 

[Arvin Meyer [MVP]]

You can add a hidden textbox (height set to 0") in a subform footer then
tuck that up against the detail section and set the footer height to 0" at
well. This will effectively make it disappear. Now, on the main add a
textbox for that quantity and you can easily refer to the main form Quantity
textbox from anywhere (any subform)

So set the zero height textbox to: =Sum([TotalAssetQty])

and assuming we've named it txtTotal, set the main form textbox to:

=[MainFormName]![SubformControlName].Form![txtTotal]

If this isn't what your are looking for, I'll try to understand better with
your next post.

Just an aside to make you aware. DLookup only looks up 1 value, but DSum,
and DCount can return a result of multiple rows. Both can use a query as a
domain, as well as a table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com