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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |