Subform With Too Many Choices?

[PeteCresswell]

Trading system.

Parent accounts and child accounts.

Trader buys five mil into a parent account.

Now they have to allocate that amount across a number of sub-
accounts.
Might be two, might be twenty, might be two hundred subaccounts.

We know all the possible sub accounts in advance - but the number
is large: 1,000+

We don't want to confront the user with list of 1,000+ account
numbers.

Here's what I'm thinking:
-----------------------------------------------------------------------------------------------
01) Link the subform to a work table.


02) Populate the work table with a bazillion (more or less....)
empty records.


03) On the subform, we have two fields:

- A combo box that is linked to the work table's "AccountNumber"
and whose .RowSource includes all possible child accounts.

- A locked text box that reveals the name of whatever account is
chosen in the combo box

- An editable text box for the allocation amount


04) To do an allocation, user places cursor in combo box
and starts typing an account number.


05) Auto-complete jumps down the list as he types.


06) .LimitToList makes sure account number is legit.


07) User tabs out of the combo box and into Allocation


08) OnExit retrieves account name


09) User types allocation


10) User tabs out of allocation, and they're in the next
empty combo box....and so-on...
---------------------------------------------------------------------------------------------

Anybody got something better?

The idea being to not overwhelm the user with a huge list,
yet allow them to specify any one of many possibilities.

 

[Klatuu]

Close, but not quite. See comments in line below

Trading system.

Parent accounts and child accounts.

Trader buys five mil into a parent account.

Now they have to allocate that amount across a number of sub-
accounts.
Might be two, might be twenty, might be two hundred subaccounts.

We know all the possible sub accounts in advance - but the number
is large: 1,000+

We don't want to confront the user with list of 1,000+ account
numbers.

Here's what I'm thinking:

No. Link to the actual table where the allocation records will be
appended

02) Populate the work table with a bazillion (more or less....)
empty records.

No, always a bad idea. This will cause extensive database bloat.

03) On the subform, we have two fields:

- A combo box that is linked to the work table's "AccountNumber"
and whose .RowSource includes all possible child accounts.

This would be correct.

- A locked text box that reveals the name of whatever account is
chosen in the combo box

Also correct.

- An editable text box for the allocation amount

Also correct

04) To do an allocation, user places cursor in combo box
and starts typing an account number.
Correct



05) Auto-complete jumps down the list as he types.

Normal behaviour for a combo when Auto Expand property is set to Yes

06) .LimitToList makes sure account number is legit.

Be sure you set the combo's Limit to List property to Yes.

07) User tabs out of the combo box and into Allocation


08) OnExit retrieves account name

Not the The Exit event. The exist event fires whenever you leave the
control. You want to use the After Update event which will fire only when
the user makes a choice in the combo. Now, the easy way to retrieve the
account name is to have it in the combo's row source. So the row source
would be:
SELECT AcctNumber, AcctName FROM AcctTable;

Set the Column Count to 2. Then in the After Update, you assign the value
of the second column to the Account Name control in the subform:

Private Function cboAcct_AfterUpdate()

Me.txtAcctName = Me.cboAcct.Column(1)

End Sub

Note Column(1) is the second column. The first is (0)

As you move down through the subforms records, you just add a new record
after you complete the current record.

Also, as a useful tool for the user, I would suggest you show a running sum
of the amount left to calculate on the main form. The way you do that is to
put a control on the subform's footer section. The control source of that
control would be used to sum the allocations in the subform.

=Sum(txtAllocated)

Now, since you will not see a footer section of a subform in datasheet view,
you put a control on the main form that shows the value in the summing
control:

=SubformControlName.Form.txtAllocated

Now, to make it even better and ensure the allocations match the total
amount, use the After Update event of the control where you enter the
allocation to make sure you allocate all of the total and that you don't
over allocate.