Hi All,
I've been having no luck with my subform display all records.
I have a main form with an unbound subform.
In the VBA for the Form_Current event, a field (ID) from the main form is used in a function that determines the dependencies (all children) of that record.
This is then used to build a SQL statement, such as "SELECT tblMain.ID, tblMain.Title FROM tblMain WHERE tblMain.ID In ("returned value from function")".
If the function returns as empty the subform is set as not visible. [This functions correctly]
Else,
Me.subfrmname.sourceobject = "frmname"
Me.subfrmname.form.recordsource = SQL statement
Me.subfrmname.Visible =True
This works only so far as it displays the first record.
When I open the form that the subform uses as a source, it has all of the records.
I have the subform source as a datasheet default view. There is no filtering, the subform on the main form is unbound so there is no Link Master and Child.
I did have it working slightly better at one point (if only I can get back to that!) by applying Query definitions. In that instance the form would load with all the correct records BUT the main form would not update on the next record selected even though the query was displaying the correct record.
My question(s) is(are):
Is there a property that I haven't tried that will display all records?
If I get the query def VBA working again, am I missing something to have it populate on every form record correctly?
Or am I completely out in left field?
I've been having no luck with my subform display all records.
I have a main form with an unbound subform.
In the VBA for the Form_Current event, a field (ID) from the main form is used in a function that determines the dependencies (all children) of that record.
This is then used to build a SQL statement, such as "SELECT tblMain.ID, tblMain.Title FROM tblMain WHERE tblMain.ID In ("returned value from function")".
If the function returns as empty the subform is set as not visible. [This functions correctly]
Else,
Me.subfrmname.sourceobject = "frmname"
Me.subfrmname.form.recordsource = SQL statement
Me.subfrmname.Visible =True
This works only so far as it displays the first record.
When I open the form that the subform uses as a source, it has all of the records.
I have the subform source as a datasheet default view. There is no filtering, the subform on the main form is unbound so there is no Link Master and Child.
I did have it working slightly better at one point (if only I can get back to that!) by applying Query definitions. In that instance the form would load with all the correct records BUT the main form would not update on the next record selected even though the query was displaying the correct record.
My question(s) is(are):
Is there a property that I haven't tried that will display all records?
If I get the query def VBA working again, am I missing something to have it populate on every form record correctly?
Or am I completely out in left field?