A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

selecting multiple records



 
 
Thread Tools Display Modes
  #1  
Old July 29th, 2004, 10:35 PM
sps
external usenet poster
 
Posts: n/a
Default selecting multiple records

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.
  #2  
Old July 30th, 2004, 03:04 AM
Dirk Goldgar
external usenet poster
 
Posts: n/a
Default selecting multiple records

"sps" wrote in message

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.


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?


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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


  #3  
Old August 2nd, 2004, 06:11 PM
sps
external usenet poster
 
Posts: n/a
Default selecting multiple records

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

-----Original Message-----
"sps" wrote in

message

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.


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?


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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

  #4  
Old August 3rd, 2004, 08:22 PM
Dirk Goldgar
external usenet poster
 
Posts: n/a
Default selecting multiple records

"sps" wrote in message

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


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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query combining multiple records from one table can't add records Clint Marshall Running & Setting Up Queries 4 July 8th, 2004 01:25 PM
Selecting multiple records: Datasheet TomT Using Forms 1 June 30th, 2004 11:59 PM
Filtering Records on a form using multiple combo boxes Mark Senibaldi Using Forms 1 June 17th, 2004 07:05 PM
Showing multiple records on one page of a form Design by Sue General Discussion 0 June 17th, 2004 02:20 PM
Number of multiple records Brenda Hutton Worksheet Functions 2 December 15th, 2003 02:08 PM


All times are GMT +1. The time now is 12:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.