Display Subform Data in Form When Subform is Closed (1 Viewer)

[MakingItUpAsIGo]

I have a form with a subform. Data selected from the subform does not show in the form until the form view is switched to layout view then back to form view or design view. I'd like the subform data to display on the form when the subform is closed. Can you help me?

 

[Uncle Gizmo]

Welcome to the forum...

I'm not clear on what your problem is.

I'm probably misunderstanding your question but it seems to me that you wouldn't expect a closed form to display information, so there's something missing in the explanation I reckon...

 

[MakingItUpAsIGo]

I open the subform to select data to add from the subform to display in the form. Once I select the data from the subform I wish to add, I then close the subform. What I select from the subform (like a dropdown list of defined options), is then tied to the member and displays on the form. What pulls up the dropdown list is a button with an embedded macro that opens the subform. There is a button with an embedded macro that closes the selection portion of the subform.

I hope that helps make it clearer. It is likely me not using correct MS Access lingo.

 

[Pat Hartman]

Maybe we're still having a language problem. Subforms are forms that are embedded in a control on another form. Therefore, "subforms" are never opened directly except perhaps in design view if you want to make changes.

As Uncle pointed out, once a form is closed, any data that it displayed is no longer available to any other form. Tables store data. Forms display data and allow you to update what you see but they do not store data.

If you can post a picture of the form, that might clarify what you are trying to do.

PS, welcome aboard

PPS, I also moved the thread to a more appropriate forum.

 

[MakingItUpAsIGo]

Maybe we're still having a language problem. Subforms are forms that are embedded in a control on another form. Therefore, "subforms" are never opened directly except perhaps in design view if you want to make changes.

As Uncle pointed out, once a form is closed, any data that it displayed is no longer available to any other form. Tables store data. Forms display data and allow you to update what you see but they do not store data.

If you can post a picture of the form, that might clarify what you are trying to do.

PS, welcome aboard

PPS, I also moved the thread to a more appropriate forum.

 

[theDBguy]

Pardon me for jumping in, but it looks like you may be referring to a popup form instead of a subform, which if you open in Dialog mode, you should be able to Requery the main form after it closes.

 

[Pat Hartman]

OK, that's better but I'm still confused. Are you using the same form as a subform (embedded on another form) and as a regular form opened by the button?

When you have an embedded subform, you wouldn't be using an "add" button to open the subform, it would already be open and visible on the main form.

When you have a form or subform that is showing data and you open another form to show that same set of data, and you change/add data using the second form, when you close the second form, you will NOT see the changes you made even though they have been saved to the table. Why? When Access opens a form and loads the bound RecordSource table/query, it keeps the data in memory. So, the mainform or subform is showing the data that was loaded into memory when the form opened. When you now open a popup form, a NEW recordset is opened using the bound table/query. You now have TWO instances of the same data loaded into memory. One is old and one has been modified. One doesn't automagically update the other, although, Access does have a refresh option so that eventually, if you wait long enough, updated data will show on the original form but NOT newly added records. Why, well again we go back to the "load the recordset into memory" issue. If the recordset contains 5 records, you will eventually see changes made to any of those records or you might even see ###deleted#### if the record got deleted but you will not see any newly added records. The form must rerun the query to select the data again in order to see the new records.

I think your workflow is incorrect but I'm going to take a leap of faith and tell you how to fix the problem even though I would prefer that you change the workflow. In the button code, change the argument in the OpenForm method to Dialog. That stops the code in the first form at after the new form opens up. The new form does its thing and closes. Then execution picks up at the next instruction. That instruction should be ---- Me.Requery --- that tells Access to rerun the query and it will pick up all the updates as well as new records and also drop any deleted records.

 

[Uncle Gizmo]

I have a form with a subform. Data selected from the subform does not show in the form until the form view is switched to layout view then back to form view or design view. I'd like the subform data to display on the form when the subform is closed. Can you help me?

Well I'm still missing something. You have been providing excellent quality feedback, except I'm just not understanding it. I'm not sure if it's the feedback or me?

Reviewing your first post:-

>>>I have a form with a subform. Data selected from the subform does not show in the form until the form view is switched to layout view then back to form view or design view. I'd like the subform data to display on the form when the subform is closed. Can you help me?<<<

The only thing that springs to mind is there is an issue in MS Access where what you see displayed on a form in certain views is not actually data. It's like MS Access is presenting "a look", it gives you an indication of what your form will look like but it's not using your actual data.

I have no idea if this is the issue, but this is the only sort of historical issue that crops up from time to time that seems to relate to your question.

 

[MakingItUpAsIGo]

Well I'm still missing something. You have been providing excellent quality feedback, except I'm just not understanding it. I'm not sure if it's the feedback or me?

Reviewing your first post:-

>>>I have a form with a subform. Data selected from the subform does not show in the form until the form view is switched to layout view then back to form view or design view. I'd like the subform data to display on the form when the subform is closed. Can you help me?<<<

The only thing that springs to mind is there is an issue in MS Access where what you see displayed on a form in certain views is not actually data. It's like MS Access is presenting "a look", it gives you an indication of what your form will look like but it's not using your actual data.

I have no idea if this is the issue, but this is the only sort of historical issue that crops up from time to time that seems to relate to your question.

Thanks for trying. The responses are informational, and I am sure provide someone with a hardy head full of access knowhow helpful direction. This is not me. Thanks for giving it a go.

 

[MakingItUpAsIGo]

OK, that's better but I'm still confused. Are you using the same form as a subform (embedded on another form) and as a regular form opened by the button?

When you have an embedded subform, you wouldn't be using an "add" button to open the subform, it would already be open and visible on the main form.

When you have a form or subform that is showing data and you open another form to show that same set of data, and you change/add data using the second form, when you close the second form, you will NOT see the changes you made even though they have been saved to the table. Why? When Access opens a form and loads the bound RecordSource table/query, it keeps the data in memory. So, the mainform or subform is showing the data that was loaded into memory when the form opened. When you now open a popup form, a NEW recordset is opened using the bound table/query. You now have TWO instances of the same data loaded into memory. One is old and one has been modified. One doesn't automagically update the other, although, Access does have a refresh option so that eventually, if you wait long enough, updated data will show on the original form but NOT newly added records. Why, well again we go back to the "load the recordset into memory" issue. If the recordset contains 5 records, you will eventually see changes made to any of those records or you might even see ###deleted#### if the record got deleted but you will not see any newly added records. The form must rerun the query to select the data again in order to see the new records.

I think your workflow is incorrect but I'm going to take a leap of faith and tell you how to fix the problem even though I would prefer that you change the workflow. In the button code, change the argument in the OpenForm method to Dialog. That stops the code in the first form at after the new form opens up. The new form does its thing and closes. Then execution picks up at the next instruction. That instruction should be ---- Me.Requery --- that tells Access to rerun the query and it will pick up all the updates as well as new records and also drop any deleted records.

Thanks for trying. The responses are informational, and I am sure provide someone with a hardy head full of access knowhow helpful direction. This is not me. Thanks for giving it a go.

 

[MakingItUpAsIGo]

OK, that's better but I'm still confused. Are you using the same form as a subform (embedded on another form) and as a regular form opened by the button?

When you have an embedded subform, you wouldn't be using an "add" button to open the subform, it would already be open and visible on the main form.

When you have a form or subform that is showing data and you open another form to show that same set of data, and you change/add data using the second form, when you close the second form, you will NOT see the changes you made even though they have been saved to the table. Why? When Access opens a form and loads the bound RecordSource table/query, it keeps the data in memory. So, the mainform or subform is showing the data that was loaded into memory when the form opened. When you now open a popup form, a NEW recordset is opened using the bound table/query. You now have TWO instances of the same data loaded into memory. One is old and one has been modified. One doesn't automagically update the other, although, Access does have a refresh option so that eventually, if you wait long enough, updated data will show on the original form but NOT newly added records. Why, well again we go back to the "load the recordset into memory" issue. If the recordset contains 5 records, you will eventually see changes made to any of those records or you might even see ###deleted#### if the record got deleted but you will not see any newly added records. The form must rerun the query to select the data again in order to see the new records.

I think your workflow is incorrect but I'm going to take a leap of faith and tell you how to fix the problem even though I would prefer that you change the workflow. In the button code, change the argument in the OpenForm method to Dialog. That stops the code in the first form at after the new form opens up. The new form does its thing and closes. Then execution picks up at the next instruction. That instruction should be ---- Me.Requery --- that tells Access to rerun the query and it will pick up all the updates as well as new records and also drop any deleted records.

Thanks for trying. The responses are informational, and I am sure provide someone with a hardy head full of access knowhow helpful direction. This is not me. Thanks for giving it a go.

 

[MakingItUpAsIGo]

Pardon me for jumping in, but it looks like you may be referring to a popup form instead of a subform, which if you open in Dialog mode, you should be able to Requery the main form after it closes.

Thanks for trying. The responses are informational, and I am sure provide someone with a hardy head full of access knowhow helpful direction. This is not me. Thanks for giving it a go.

 

[theDBguy]

Thanks for trying. The responses are informational, and I am sure provide someone with a hardy head full of access knowhow helpful direction. This is not me. Thanks for giving it a go.

In that case, may I suggest consider posting a sample db, so we can show you how to do it?

 

[Pat Hartman]

@MakingItUpAsIGo did you understand the difference between a form that is embedded on another form is a subform and works slightly differently from a form that is opened and is separate from the form that opened it? That seems to be the foundation of the confusion based on your original description. But as @theDBguy said, if you post your DB, someone can probably fix the code and explain the changes so you understand them.