My objective is to display rows of data on a form in the 'page' format, with each 'page' within the form showing a fixed number of rows, just like a Google seach page for example (or this forum). (If anyone has seen this done successfully in Access then please let me know!)
Mostly I need to do this because I have a grid of combo boxes each of which has its own recordsource, and this would not work in a continuous form - or at least only with clumsy workarounds that mean the form is almost useless when there is a large number of records. (If anyone needs a further explanation of this I can provide chapter and verse.)
In ACC2000 using recordset.PageSize etc I can get this to work as a main form (hooray!), but I need it as a subform (boo!). I have a conceptual problem that a wise head will be able to straighten out for me in 2secs flat. So, here goes ...
The main form is bound to a parent table. A subform would normally be bound to the child table and be linked by the parent ID field. In my case the subform has to be unbound but its controls are based on a recordset whose source is SELECT * FROM [tblChild] WHERE [tblChild].[parentID] = {parameter} where {parameter} = Forms![frmMain].[parentID]. That's equivalent to the situation when a subform is bound to tblChild in the usual way.
Obviously the recordset used by the subform changes when the user navigates through records on the main form, but note that this recordset then underlies all the pages that the subform can display for that parent record. (There are probably ways to limit the recordset to the PageSize number of records only, but I don't have so many records that I need to do this - and I haven't got that far yet anyway!)
My stumbling block right now is where to declare the recordset. I think I should declare it on the main form OnCurrent event so that it will then reflect the parent record as the user navigates through the records. But will it then also be available to the subform? And given that the subform opens first, how can I avoid 'not found' errors when the form opens?
In other words, how do you open a recordset so that it is available to more than one form?.
(and if I am re-inventing a wheel please direct me to the original wheel.)
Mostly I need to do this because I have a grid of combo boxes each of which has its own recordsource, and this would not work in a continuous form - or at least only with clumsy workarounds that mean the form is almost useless when there is a large number of records. (If anyone needs a further explanation of this I can provide chapter and verse.)
In ACC2000 using recordset.PageSize etc I can get this to work as a main form (hooray!), but I need it as a subform (boo!). I have a conceptual problem that a wise head will be able to straighten out for me in 2secs flat. So, here goes ...
The main form is bound to a parent table. A subform would normally be bound to the child table and be linked by the parent ID field. In my case the subform has to be unbound but its controls are based on a recordset whose source is SELECT * FROM [tblChild] WHERE [tblChild].[parentID] = {parameter} where {parameter} = Forms![frmMain].[parentID]. That's equivalent to the situation when a subform is bound to tblChild in the usual way.
Obviously the recordset used by the subform changes when the user navigates through records on the main form, but note that this recordset then underlies all the pages that the subform can display for that parent record. (There are probably ways to limit the recordset to the PageSize number of records only, but I don't have so many records that I need to do this - and I haven't got that far yet anyway!)
My stumbling block right now is where to declare the recordset. I think I should declare it on the main form OnCurrent event so that it will then reflect the parent record as the user navigates through the records. But will it then also be available to the subform? And given that the subform opens first, how can I avoid 'not found' errors when the form opens?
In other words, how do you open a recordset so that it is available to more than one form?.
(and if I am re-inventing a wheel please direct me to the original wheel.)
Last edited:
