Requery unbound listbox on master forces requery of subforms?

[M Wells]

Hi All,

I am developing an Access 2003 project application with the back end
in SQL Server 2003.

I have a master form that tracks projects, and several subforms on it
that track various aspects of the project. On the masterform I have
two unbound listboxes that I populate with information regarding the
current record in one of the subforms.

The subform tracks which employees have been assigned to the project
in question, and one listbox shows the assets that have been assigned
to the current employee, and the other listbox shows the assets that
are still available to be assigned to that employee.

Double clicking on an entry in the listbox that shows the assets that
have yet to be assigned to the employee inserts a record into an
associative table that the other listbox draws from to indicate that
the asset has now been assigned to the employee. When I requery the
2nd listbox, as part of the double-click event on the first listbox,
to show now the newly assigned asset, for some reason Access is
forcing a requery of all the subforms on the form, sending them all
back to the first record in their recordsets.

I can't figure out why this is the case, and I'm now wondering if
there's a way of refreshing a listbox's contents without requerying
the subforms?

Any help immensely appreciated!

Much warmth,

Murray

[Wayne Gillespie]

On Sun, 09 May 2004 20:02:04 -0400, M Wells <pl**********@p lanetthoughtful .org> wrote:

Hi All,

I am developing an Access 2003 project application with the back end
in SQL Server 2003.

I have a master form that tracks projects, and several subforms on it
that track various aspects of the project. On the masterform I have
two unbound listboxes that I populate with information regarding the
current record in one of the subforms.

The subform tracks which employees have been assigned to the project
in question, and one listbox shows the assets that have been assigned
to the current employee, and the other listbox shows the assets that
are still available to be assigned to that employee.

Double clicking on an entry in the listbox that shows the assets that
have yet to be assigned to the employee inserts a record into an
associative table that the other listbox draws from to indicate that
the asset has now been assigned to the employee. When I requery the
2nd listbox, as part of the double-click event on the first listbox,
to show now the newly assigned asset, for some reason Access is
forcing a requery of all the subforms on the form, sending them all
back to the first record in their recordsets.

I can't figure out why this is the case, and I'm now wondering if
there's a way of refreshing a listbox's contents without requerying
the subforms?

Any help immensely appreciated!

Much warmth,

Murray

Instead of using the LinkChild and LinkMaster properties of the subform controls to sync the subforms with the main
form, add a reference to the relevant control on the main form, in the criteria line of the subforms recordsource.
Say for example you have EmployeeID set in the LinKChild / Master properties of a subform, delete these properties and
in the criteria line of the subforms recordsource under EmployeeID put something like Forms!frmMyMain Form!EmployeeID .

You will have to do the requerying of the subforms in code (Me.frmMySubfor m1.Form.Requery ) when ever you need the
subform contents to be updated, (OnCurrent etc) but the subforms will stay put until you decide to requery them.

If you use the LinkChild and LinkMaster properties, Access decides when to requery the subforms which may not always be
when you want them requeried, especially if you have multiple subforms. I rarely use the LinkChild and LinkMaster
properties anymore because I find this behaviour a real PITA.
Wayne Gillespie
Gosford NSW Australia