selecting multiple records

  • Thread starter Thread starter sps
  • Start date Start date
S

sps

I have a form a single "main" record is displayed and a
list of other records on a subform. Multiple subform
records will match to the one main record. I need to be
able to have the user select multiple records on the
subform so I can set this match. As of now I can only
select records that are next to each other.

I also need to be able to calculate a total of a field in
the selected records and compare it with a value on the
main form. How would I reference the selected records or
get a count of how many records are selected?

Thanks for the help.
steve.
 
sps said:
I have a form a single "main" record is displayed and a
list of other records on a subform. Multiple subform
records will match to the one main record. I need to be
able to have the user select multiple records on the
subform so I can set this match. As of now I can only
select records that are next to each other.
Click to expand...

You cannot do this with a subform, if you try to do it by clicking,
shift-clicking, or ctrl-clicking on record selector. You can either use
a multiselect list box for the purpose, or you can add a Yes/No field
"IsSelected" to the table on which the subform is based, bind a check
box on the subform to that field, and check off the ones you want to
select. You'd probably want a command button to clear all the check
boxes, or else clear them all in the main form's Current event.

All this is assuming that the subform is always supposed to show all
records, and not just those that you've related to the main form's
record. This is not the usual way to relate tables. Is it your
intention to store the relationship between the selected subform records
and the main form record? If so, you'll need three tables: the main
form's table, the table that contains the records from which to choose,
and a table that will contain one record for each match-up between the
other two tables (this table's fields are the keys from each of the
other tables). In such a case, the most common way to present it is
with a subform based on that third, linking table. Then each new record
is created by either selecting the second table's key from a combo box
on the subform, or sometimes by choosing the matching record from a list
box on the main form, which action runs code that creates the record for
the subform.
I also need to be able to calculate a total of a field in
the selected records and compare it with a value on the
main form. How would I reference the selected records or
get a count of how many records are selected?
Click to expand...

That depends on how you've set it up, and whether the relating records
are actually stored in a third table or are merely transitory. If you
explain a bit about what you're trying to do and why, this part should
be pretty simple.
 
Thanks for the reply, I was afraid it wouldn't be a simple
thing to do. The purpose is as follows...

(Since I wrote I modified it to be two subforms but the
needs are the same)
I am working on a reconcile process where subform1 (the
old main form) contains a record with information about a
payment made and subform2 contains information about the
items purchased. I need to match up these records to
clear out the account. I have a third table (as you
mentioned) that holds the keys from form1 and form2 and
some other information relevant to the match.

It all works fine when selecting one record from each
subform. However, it is common to have multiple records
from one table match up to a single record in the other
table. I have worked around this by adding a status to
the record and marking it as partial or full. This works
but it relies on the user making the correct choices,
which I don't consider to be a sound practice. I would
like a way to handle this with more control.

I tried the list box idea, it is not ideal (no longer
provides sorting options) and I can't figure out how to
access the second or third records selected, only the
first one.

As far as the adding needs, I want to be able to calculate
a total for some records selected BEFORE they are matched
up to help make the decision on the correct matching. I
want to be able to select 2 or more items, click a button
and have the sum of the amounts displayed. (automatically
updating a text box on the main form would be best but I
would settle for the message box)

Thanks again for your help.
Steve
 
sps said:
Thanks for the reply, I was afraid it wouldn't be a simple
thing to do. The purpose is as follows...

(Since I wrote I modified it to be two subforms but the
needs are the same)
I am working on a reconcile process where subform1 (the
old main form) contains a record with information about a
payment made and subform2 contains information about the
items purchased. I need to match up these records to
clear out the account. I have a third table (as you
mentioned) that holds the keys from form1 and form2 and
some other information relevant to the match.

It all works fine when selecting one record from each
subform. However, it is common to have multiple records
from one table match up to a single record in the other
table. I have worked around this by adding a status to
the record and marking it as partial or full. This works
but it relies on the user making the correct choices,
which I don't consider to be a sound practice. I would
like a way to handle this with more control.

I tried the list box idea, it is not ideal (no longer
provides sorting options) and I can't figure out how to
access the second or third records selected, only the
first one.

As far as the adding needs, I want to be able to calculate
a total for some records selected BEFORE they are matched
up to help make the decision on the correct matching. I
want to be able to select 2 or more items, click a button
and have the sum of the amounts displayed. (automatically
updating a text box on the main form would be best but I
would settle for the message box)

Thanks again for your help.
Steve
Click to expand...

I think I would do it with two list boxes with their MultiSelect
properties set to "Simple", text boxes to show the totals of the
selected items in each list box, and a command button to "commit" the
matchup by writing out records to the third table you describe.
Probably the list boxes' rowsources would be set to queries that would
exclude any records that have already been matched, so that they show
only the unreconciled items.

The user's process would be to click a single item in one list box, and
one or more items in the other list box. I don't know if one of the
list boxes is always the "one" side and the other is always the "many"
side, or whether it could go either way. I don't see any way it could
make sense for both list boxes to have multiple items selected. If one
of the list boxes is always for selecting one item only, then that list
box doesn't need to be set for MultiSelect.

AfterUpdate events for these list boxes would recalculate the "totals"
text boxes, and the "commit" button would (maybe) only be enabled if the
recalculated totals in those text boxes were equal. The "commit"
button's Click event loops through the ItemsSelected collections of both
list boxes to build and execute insert queries to create the appropriate
records in the reconciliation table. Then it requeries the list boxes
to remove the reconciled items.

To make this scheme work, you need to use the ItemsSelected property of
the multiselect list boxes, which returns a collection of the
row-numbers of the selected items. You also need the ItemData and/or
the Column property of the list box to get data from each row and column
in the list box.
 
Back
Top