Dynamic Subform Only Displays One Record (1 Viewer)

[LeeMo127]

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?

 

[theDBguy]

Hi. Perhaps, add a DoCmd.GotoRecord , , acFirst after you have set the subform to Visible? Just a thought...

 

[LeeMo127]

I will give it a try. I'm not sure this will cause ALL the records that are in the query to display however. Or atleast I'm not sure how the DoCmd go to the First Record will help.
I appreciate the the thought. Thanks.

 

[theDBguy]

I will give it a try. I'm not sure this will cause ALL the records that are in the query to display however. Or atleast I'm not sure how the DoCmd go to the First Record will help.
I appreciate the the thought. Thanks.

Hi. You may be right, since I am only guessing and thinking you were actually just seeing the last record scrolled up; hence, my suggestion. However, just to confirm, when you see the subform becomes visible, what is the recordcount you see in the Navigation Buttons? If only one, then no, my suggestion wouldn't work.

 

[The_Doc_Man]

First question: Is the subform a datasheet in continuous mode, or an ordinary subform.

If continuous, I'm not sure, but...

IF it is an ordinary form, Second Question: Do you see navigation controls on the subform?

 

[Pat Hartman]

Make sure that the master/child link properties are not set. That is how you would normally sync a main form with a subform but you appear to be doing something else.

 

[LeeMo127]

Hi. You may be right, since I am only guessing and thinking you were actually just seeing the last record scrolled up; hence, my suggestion. However, just to confirm, when you see the subform becomes visible, what is the recordcount you see in the Navigation Buttons? If only one, then no, my suggestion wouldn't work.

The record count in the subform when applying the SQL statement to the subform form record source is only 1. Even though the subform form (and the query for that form), when opened outside of the form, has all the correct records displayed.

 

[LeeMo127]

First question: Is the subform a datasheet in continuous mode, or an ordinary subform.

If continuous, I'm not sure, but...

IF it is an ordinary form, Second Question: Do you see navigation controls on the subform?

The subform that I use as the source object is in datasheet view. I'm not sure what you mean by datasheet in continuous mode. Perhaps there is a property I have not set, which is likely. I do not like the look of the ordinary continous form view. I just changed the it to continuous form view in tabular to see if it would work and have the same datasheet look but it was still just 1 record.

I do see the navigation controls (they show 1 record). When I get it working, I will remove the navigation controls but in theory if it's working there will be no need for the nav controls.

Thanks for the input. I was hoping tabular continuous was going to work.

 

[LeeMo127]

Make sure that the master/child link properties are not set. That is how you would normally sync a main form with a subform but you appear to be doing something else.

The Link Master and Child are not set but maybe I have to do this in the VBA and not the while in design mode on the main form.

 

[LeeMo127]

I've managed to solve my problem so I'll post what I did that solved it and where I went wrong.

How I solved this issue for me.
Issue: Only one record displaying on a dynamic subform.
Solve: I had to declare the linked master/Child fields in the VBA as empty.
To be clear these were visibly clear in the design view and selected multiple times as no field BUT the linked master/child fields must become linked to the ID in the VBA by default. This would be where I went wrong (thinking that since it was Null on the form that it remained Null.

I added the following to the code:
Me.subfrmName.LinkedMasterFields = ""
Me.subfrmName.LinkedChildFields = ""

So to do this yourself:
Create the form that will become the subform with the fields you need.
Create form that will contain the subform.
Place unbound subform control on the main form.

The the generic VBA is as follows:
Dim strSQL As String
Dim strWhere As String (I am dynamically changing the X,Y,Z in "WHERE tblMain.ID In(X,Y,Z)
Dim TrialID As String (The field that I need to determine the X,Y,X...In my case the ID goes to a function that returns the desired X,Y,Z)

TrialID = CStr(Me!ID) (I convert the ID to a string to pass to the function)
strSQL = "You SQL statement here such as ...SELECT [Field], [Field2] FROM tblYourTable"
strWhere = MyFunction(TrialID) (ID is passed to function and the function returns the X,Y,Z in the correct format to add to the SQL statement)

Me.subfrmName.SourceObject = "Form.frmForSubForm" (This set the unbound subform to the form that is to be used)
Me.subfrmName.Form.RecordSource = strSQL & "WHERE tblMain.ID In(" & strWhere & ");" (This applied the SQL statement to the Record Source of the subform that is used in the form)
Me.subfrmName.LinkMasterFields = "" (This ensures that the Link Master Field remains Null; without this the Linked Master Field becomes what the form thinks it should be, in this case the ID)
Me.subfrmName.LinkChildFields = "" (Same as above)
Me.subfrmName.Visible = True (This sets the subform as visible; I also have an If statement that makes it not visible if the returned string from the function is Null but that is not generic enough to be applied to everyone)

I hope this helps someone else.

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out and thanks for sharing your solution. Good luck with your project.

 

[miweiser]

I've managed to solve my problem so I'll post what I did that solved it and where I went wrong.

How I solved this issue for me.
Issue: Only one record displaying on a dynamic subform.
Solve: I had to declare the linked master/Child fields in the VBA as empty.
To be clear these were visibly clear in the design view and selected multiple times as no field BUT the linked master/child fields must become linked to the ID in the VBA by default. This would be where I went wrong (thinking that since it was Null on the form that it remained Null.

I added the following to the code:
Me.subfrmName.LinkedMasterFields = ""
Me.subfrmName.LinkedChildFields = ""

So to do this yourself:
Create the form that will become the subform with the fields you need.
Create form that will contain the subform.
Place unbound subform control on the main form.

The the generic VBA is as follows:
Dim strSQL As String
Dim strWhere As String (I am dynamically changing the X,Y,Z in "WHERE tblMain.ID In(X,Y,Z)
Dim TrialID As String (The field that I need to determine the X,Y,X...In my case the ID goes to a function that returns the desired X,Y,Z)

TrialID = CStr(Me!ID) (I convert the ID to a string to pass to the function)
strSQL = "You SQL statement here such as ...SELECT [Field], [Field2] FROM tblYourTable"
strWhere = MyFunction(TrialID) (ID is passed to function and the function returns the X,Y,Z in the correct format to add to the SQL statement)

Me.subfrmName.SourceObject = "Form.frmForSubForm" (This set the unbound subform to the form that is to be used)
Me.subfrmName.Form.RecordSource = strSQL & "WHERE tblMain.ID In(" & strWhere & ");" (This applied the SQL statement to the Record Source of the subform that is used in the form)
Me.subfrmName.LinkMasterFields = "" (This ensures that the Link Master Field remains Null; without this the Linked Master Field becomes what the form thinks it should be, in this case the ID)
Me.subfrmName.LinkChildFields = "" (Same as above)
Me.subfrmName.Visible = True (This sets the subform as visible; I also have an If statement that makes it not visible if the returned string from the function is Null but that is not generic enough to be applied to everyone)

I hope this helps someone else.

Absolutely helped me today! I signed up JUST to thank you!!
After a week trying to find a solution to this exact issue that is NOT Google-able!
I have my forms setting RecordSource based on dropdown table name selection and all was OK once I figured out to set in SubForm datasheet first, but only showed the first record was so frustrating...
Cheers!

 

[theDBguy]

Absolutely helped me today! I signed up JUST to thank you!!
After a week trying to find a solution to this exact issue that is NOT Google-able!
I have my forms setting RecordSource based on dropdown table name selection and all was OK once I figured out to set in SubForm datasheet first, but only showed the first record was so frustrating...
Cheers!

Hi. Welcome to AWF!

 

[omarkhansqa]

Signed up to thank you as well! Was driving me crazy!

 

[omarkhansqa]

Absolutely helped me today! I signed up JUST to thank you!!
After a week trying to find a solution to this exact issue that is NOT Google-able!
I have my forms setting RecordSource based on dropdown table name selection and all was OK once I figured out to set in SubForm datasheet first, but only showed the first record was so frustrating...
Cheers!

Me too!

 

[Pat Hartman]

The Link Master and Child are not set but maybe I have to do this in the VBA and not the while in design mode on the main form.

You do this in design mode for the subform control on the main form.

 

[theDBguy]

Signed up to thank you as well! Was driving me crazy!

Hi. Welcome to AWF!