Synchronizing subforms

Hello,

Finally my office has upgraded to MS Access 2007. Now, I am having a bit of
a problem synchronizing subforms on a tab. Let me begin with what I have set
up; then explain my two objectives.

On a particular tab, I have:

The first subform created from a query in a datasheet view. The query is
based on a table consisting of many employees with respective personnel ID
numbers (PersID). Let's call it Subform1.
Subform1 contains a combo list with a select of a PersID (Long Integer). The
Control Source = PersID and the Row Source = the selection of the PersID from
the query. Upon selection the remaining associated fields fill in the record
line. In essence, I may have 100 employees in the table, but select three to
five employees from the query in the subform.

The second subform is created from the same query in a single form view. It
refelcts the single form view of the same records as Subform 1.

Objectives:

Objective 1: After selecting a record from Subform1, I need Subform2 to
reflect the same record. At present, after adding a new record in Subform1,
the new record does not appear in Subform2, in fact the expected record is
blank. I must close and reopen the tab so that the new record will appear in
Subform2. Objective: I need the next record to appear after selecting a new
record in Subform1.

Objective 2: Whenever the focus is on a particular record in Subform1 (let's
say I simply click the field of the PersID without selecting a record, or for
that matter simply click on the employee's last name field, I would like the
associated single form view in Subform2 to reflect the associated record from
Subform1. The event doesn't matter whether it is a focus or click of a mouse
on any particular field of a record in Subform1.

Back in Access 2003, I was able to do this by opening a separate form without
using tabs by employing a macro that synchronized the two forms and
incorporated an On Current condition to synchronize the two form.

But with Access 2007, I am a bit confused.

Any assistance will be greatly appreciated.

Thanks,

Scotty

--
Message posted via http://www.accessmonster.com

scottypws wrote:
Hello,

Finally my office has upgraded to MS Access 2007. Now, I am having a bit of
a problem synchronizing subforms on a tab. Let me begin with what I have set
up; then explain my two objectives.

On a particular tab, I have:

The first subform created from a query in a datasheet view. The query is
based on a table consisting of many employees with respective personnel ID
numbers (PersID). Let's call it Subform1.
Subform1 contains a combo list with a select of a PersID (Long Integer). The
Control Source = PersID and the Row Source = the selection of the PersID from
the query. Upon selection the remaining associated fields fill in the record
line. In essence, I may have 100 employees in the table, but select three to
five employees from the query in the subform.

The second subform is created from the same query in a single form view. It
refelcts the single form view of the same records as Subform 1.

Objectives:

Objective 1: After selecting a record from Subform1, I need Subform2 to
reflect the same record. At present, after adding a new record in Subform1,
the new record does not appear in Subform2, in fact the expected record is
blank. I must close and reopen the tab so that the new record will appear in
Subform2. Objective: I need the next record to appear after selecting a new
record in Subform1.

Objective 2: Whenever the focus is on a particular record in Subform1 (let's
say I simply click the field of the PersID without selecting a record, or for
that matter simply click on the employee's last name field, I would like the
associated single form view in Subform2 to reflect the associated record from
Subform1. The event doesn't matter whether it is a focus or click of a mouse
on any particular field of a record in Subform1.

Back in Access 2003, I was able to do this by opening a separate form without
using tabs by employing a macro that synchronized the two forms and
incorporated an On Current condition to synchronize the two form.

But with Access 2007, I am a bit confused.

Any assistance will be greatly appreciated.

Thanks,

Scotty


Hi, this is Scotty again. I have managed to get part way to Objective 1. I
reverted back to my Access 2003 thinking and set up two separate forms where
Subforms 1 and 2 are now two stand-alone forms which are not superimposed on
a tab page. I used a macro that opens form 2 whereby the two PersIDs are
equal on both forms. I placed the macro in the double-click event on form 1
and it opens form 2 exactly to the corresponding record on form 2.

Now when I copy form 1 on a tab page, the macro generates a parameter request
for the PersID from form 1. When I type the PersID in the parameter text box
and execute, form 2 will open up to the correct record.

It appears that I must inform the macro that I am executing form 1 on a tab
page; but I am unable to find any reference to this situation.

Your insight will be appreciated.

Scotty

--
Message posted via http://www.accessmonster.com