Hello,
This is a fairly lengthy post. My apologies.
The context for my questions is a form that opens with a listbox full of some 60 room/household area names and a subform where a user can place names selected from the listbox in descending order of selection. Names are added to the subform by double-clicking the listbox name or selecting it and then clicking on a "Add Room" command button. The subform's display capacity is 20 selected rooms.
Rooms are added from the parent form. Each time, the underlying table of the subform must have a record appended (this works) and then the subform requeried.
The subform cannot be based directly on a table as the table must filtered. A query currently does this. All of this would be fine except:
When more than 20 records have been added, each additional record causes the subform display to 'jump' slightly and the focus constantly stays on the 1st room entered, thus the user cannot see what they just added without having to scroll down. I cannot figure a way from the parent form to code such that for the additional record over 20 the following occurs:
Records 10-20 are displayed with record 21 at the bottom and current record
And for records 22-30:
The above display is updated by addition of the single new record until the subform again fills
Then finally:
This cycle repeats if needed for each additional set of 10 records.
I've been rooting around forums and Access books looking for information about the above subject but remain unsure of the possibilities. Answers to the following questions might provide significant enlightenment for me, so I ask that knowledgeable folks please respond.
Given an unbound form and subform
1) Can a recordset be assigned to a subform?
2) Can it be assigned in the subform? If so, in what event?
3) More importantly, can it be assigned in the parent form? If so, in what event?
4) What is the syntax for the above?
Given that a recordset CAN be assigned to a subform
1) If the number of records returned is greater than the subform control, how can the display be set to show only the lowest records? For example 50 records are returned for a subform that can display only 30 records. How would you code so that records 20 thru 30 are displayed with empty space at the bottom of the subform?
2) How would you code so that if the number of records returned is greater than the subform control's capacity AND the display is in descending order AND when adding new records you always want to display at least the last 10 records before additional ones fill up the subform control again
3) If a recordset can be assigned (I certainly have not been successful!), would the RecordsetClone and Bookmarks be of use?
4) Adding records 1 through 20 may work very nicely, but the display starts 'jumping' once the subform displayed is filled. Can this be stabilized in VBA code?
Regards, Sorrells
This is a fairly lengthy post. My apologies.
The context for my questions is a form that opens with a listbox full of some 60 room/household area names and a subform where a user can place names selected from the listbox in descending order of selection. Names are added to the subform by double-clicking the listbox name or selecting it and then clicking on a "Add Room" command button. The subform's display capacity is 20 selected rooms.
Rooms are added from the parent form. Each time, the underlying table of the subform must have a record appended (this works) and then the subform requeried.
The subform cannot be based directly on a table as the table must filtered. A query currently does this. All of this would be fine except:
When more than 20 records have been added, each additional record causes the subform display to 'jump' slightly and the focus constantly stays on the 1st room entered, thus the user cannot see what they just added without having to scroll down. I cannot figure a way from the parent form to code such that for the additional record over 20 the following occurs:
Records 10-20 are displayed with record 21 at the bottom and current record
And for records 22-30:
The above display is updated by addition of the single new record until the subform again fills
Then finally:
This cycle repeats if needed for each additional set of 10 records.
I've been rooting around forums and Access books looking for information about the above subject but remain unsure of the possibilities. Answers to the following questions might provide significant enlightenment for me, so I ask that knowledgeable folks please respond.
Given an unbound form and subform
1) Can a recordset be assigned to a subform?
2) Can it be assigned in the subform? If so, in what event?
3) More importantly, can it be assigned in the parent form? If so, in what event?
4) What is the syntax for the above?
Given that a recordset CAN be assigned to a subform
1) If the number of records returned is greater than the subform control, how can the display be set to show only the lowest records? For example 50 records are returned for a subform that can display only 30 records. How would you code so that records 20 thru 30 are displayed with empty space at the bottom of the subform?
2) How would you code so that if the number of records returned is greater than the subform control's capacity AND the display is in descending order AND when adding new records you always want to display at least the last 10 records before additional ones fill up the subform control again
3) If a recordset can be assigned (I certainly have not been successful!), would the RecordsetClone and Bookmarks be of use?
4) Adding records 1 through 20 may work very nicely, but the display starts 'jumping' once the subform displayed is filled. Can this be stabilized in VBA code?
Regards, Sorrells
