Maintain select record in multiple subforms

I have a main form with two subforms (both in datasheet view), neither
of which are linked to the main form. The main form is based on a
query that uses the bound column of a combobox on the main form as the
criteria for the query. The combobox is based on a query that
retrieves the name and record id of the customers. In the afterUpdate
event of the combobox on the main form and both subforms are
requeried. The first subform is based on a query that retrieves
records based on the bound column of the combobox on the main form.
The OnClick event of the first subform forces a requery of the second
subform. The second subform is based on a query that uses the record
selected in the first subform to show data for the record selected in
the first subform. I have an edit button on the main form that opens
another form that presents the data in both subforms for editing.
This edit form is based on a query that retrieves the data based on
the record selected in the firsr subform. I use queries everywhere to
keep down the amount of data that is being pulled across the network
as this is a multiuser app. When I save the changes on the edit form
I requery both subforms on the main form to reflect the changes made
on the edit form. My problem is I need to have the first subform be
requeried then have the same record selected that was originally
selected when the user click on the edit button to open the edit
form. I can then requery the second subform to have it show the
records for the item selected in the first subform. I have tried
using bookmark and gotorecord but I lost here so how can I do this???

I hope my explanation is clear enough...
TD

Re: Maintain select record in multiple subforms

Anybody ? I know bookmark will not work since I requery the subforms;
refresh will not work because subform1 lists dates in desc order.
Any thoughts ???

TD

Re: Maintain select record in multiple subforms

"TD" wrote

> Anybody ? I know bookmark will not work since
> I requery the subforms; refresh will not work because
> subform1 lists dates in desc order. Any thoughts ???

You really ought to include your whole question in the big white space and
not rely on the Subject line to be assumed as part of the question. We
really won't find it a big burden if you have to repeat some text that's in
the Subject Line, too. Without detail, it is really difficult for us to
help you... in your question, you do not give detail on the data in the
subforms, the relationship between the main and subforms, whether you are
using the LinkMasterFields and LinkChildFields of the Subform Control to
synchronize the data displayed, etc..

Unless your main form and subforms are completely unrelated, what assurance
do you have that the same record as shown previously will, in fact, be in
the RecordSource of the Subform, and not filtered out?

If it is still available, then you will need to execute code or a macro to
re-find and re-select it. There is no "option" to "keep the same record
selected in the subforms when I move to another record on the main form".

Larry Linson
Microsoft Office Access MVP

Re: Maintain select record in multiple subforms

On Apr 1, 3:42=A0pm, "Larry Linson" wrote:
> "TD" wrote
>
> =A0> Anybody ? =A0I know bookmark will not work since
> =A0> I requery the subforms; refresh will not work because
> =A0> subform1 lists dates in desc order. Any thoughts ???
>
> You really ought to include your whole question in the big white space and=

> not rely on the Subject line to be assumed as part of the question. =A0We
> really won't find it a big burden if you have to repeat some text that's i=
n
> the Subject Line, too. =A0Without detail, it is really difficult for us to=

> help you... in your question, you do not give detail on the data in the
> subforms, the relationship between the main and subforms, whether you are
> using the LinkMasterFields and LinkChildFields of the Subform Control to
> synchronize the data displayed, etc..

=46rom previous post - "neither of which are linked to the main form"
meaning I am not using the LinkMasterFields, LinkChieldFields. One
correction to what I post originally posted is the first subform is in
datasheet view, the second is in single form view.

>
> Unless your main form and subforms are completely unrelated, what assuranc=
e
> do you have that the same record as shown previously will, in fact, be in
> the RecordSource of the Subform, and not filtered out?
>
> If it is still available, then you will need to execute code or a macro to=

> re-find and re-select it. =A0There is no "option" to "keep the same record=

> selected in the subforms when I move to another record on the main form".
>
> =A0Larry Linson
> =A0Microsoft Office Access MVP

As for details about what the forms are used for ... the main form has
the information such as address, phone, etc for customers. The
combobox on this form is used to select the customer you wish to view
the information for. The subforms display events that are scheduled
for this customer. The first subform lists the dates of the events
and the second subform lists the rest of the information for that
event. I used two subforms because there is too much information to
display in a subform in datasheet view and I don't want to use a
subform in continuouse form view.

Maybe I am mistaken all together in my approach. Keep in mind that at
in the near future I will be using an sql server backend instead of
an .mdb file. I am trying to design the application for multiple
users and my thinking is that form record sources based on a query
that returns a single record will make the application perform
better. Or, should I just go ahead and bind the forms to tables and
use the LinkMasterFields, LinkChieldFields?

HTH, please let me know if I need to provide more information.

TD

Re: Maintain select record in multiple subforms

On Apr 1, 3:05=A0pm, TD wrote:
> Anybody ? =A0I know bookmark will not work since I requery the subforms;
> refresh will not work because subform1 lists dates in desc order.
> Any thoughts ???
>
> TD

Maybe:

http://groups.google.com/group/comp.databases.ms-access/msg/ 05867edd322d0bf4=


James A. Fortune
CDMAPoster@FortuneJames.com

Re: Maintain select record in multiple subforms

Let me summarize what I understand:

Main Form displays information about a customer.

The first Subform Control, contains a Form in Datasheet view, that displays
summary information about multiple events for that customer.

The second Subform, which displays a Form in single-form view, with the
detail information about the event selected in the first Subform Control, is
also a Subform Control on the main form, not a Subform Control on the Form
in the First Subform Control.

Now, a comment:

If you change the record (customer) in the main form, the new customer will
have their own events, so I don't understand a question about "maintain
select record...".

You don't say how you are populating the Subforms with the information you
describe, except to say they are not linked to the main form by Link Master
Fields and Link Child Fields. Somehow you must repopulate them.

* Is it true that, in the OnCurrent event of the Main form, you
create SQL for the Form embedded in the First Subform that
uses the currently-displayed customer's identification as a
Criteria to select that customer's events, and then replace the
RecordSource of the Form embedded in that first Subform
Control?

If you are displaying a Form in Datasheet View in the First Subform, you
will have events and therefore can, in the On Current event, create SQL for
the Form embedded in the Second Subform, and replace the RecordSource of the
Form embedded in the Second Subform Control.

It would be interesting to see the performance difference between this
approach and using Link Master Fields and Link Child Fields, and making the
second Subform a Subform of the Form in the First Subform Control... that
will mean, I believe, that you'd have to have both Subforms in single-form
view. I suspect you will see little, if any difference, unless you have a
large number of records.

Please post back with specific questions or details, if I have either
misunderstood the situation or not answered the question you want answered.

Now I have a question: why is it that you "don't want to use a continuous
forms view Form"? Do you think there is something inherently bad about
them?

Larry Linson
Microsoft Office Access MVP

"TD" wrote in message
news:d59cb5f7-1846-4de8-a7ed-c8fa17973324@m71g2000hse.google groups.com...
On Apr 1, 3:42 pm, "Larry Linson" wrote:
> "TD" wrote
>
> > Anybody ? I know bookmark will not work since
> > I requery the subforms; refresh will not work because
> > subform1 lists dates in desc order. Any thoughts ???
>
> You really ought to include your whole question in the big white space and
> not rely on the Subject line to be assumed as part of the question. We
> really won't find it a big burden if you have to repeat some text that's
> in
> the Subject Line, too. Without detail, it is really difficult for us to
> help you... in your question, you do not give detail on the data in the
> subforms, the relationship between the main and subforms, whether you are
> using the LinkMasterFields and LinkChildFields of the Subform Control to
> synchronize the data displayed, etc..

From previous post - "neither of which are linked to the main form"
meaning I am not using the LinkMasterFields, LinkChieldFields. One
correction to what I post originally posted is the first subform is in
datasheet view, the second is in single form view.

>
> Unless your main form and subforms are completely unrelated, what
> assurance
> do you have that the same record as shown previously will, in fact, be in
> the RecordSource of the Subform, and not filtered out?
>
> If it is still available, then you will need to execute code or a macro to
> re-find and re-select it. There is no "option" to "keep the same record
> selected in the subforms when I move to another record on the main form".
>
> Larry Linson
> Microsoft Office Access MVP

As for details about what the forms are used for ... the main form has
the information such as address, phone, etc for customers. The
combobox on this form is used to select the customer you wish to view
the information for. The subforms display events that are scheduled
for this customer. The first subform lists the dates of the events
and the second subform lists the rest of the information for that
event. I used two subforms because there is too much information to
display in a subform in datasheet view and I don't want to use a
subform in continuouse form view.

Maybe I am mistaken all together in my approach. Keep in mind that at
in the near future I will be using an sql server backend instead of
an .mdb file. I am trying to design the application for multiple
users and my thinking is that form record sources based on a query
that returns a single record will make the application perform
better. Or, should I just go ahead and bind the forms to tables and
use the LinkMasterFields, LinkChieldFields?

HTH, please let me know if I need to provide more information.

TD