Providing Dynamic Pop-Ups

[Peter Hallett]

Some of the members listed in tbl_Members belong to groups, designated ‘A’,
‘B’, ‘C’, etc. These members are listed in tbl_Group_Members by group
designator and ID. A form, bound to these tables, joined by ID, is used to
display group members by name. All the group designators, together with
their associated properties, are listed, for each member, a check box
identifying to which group, or groups, the currently-displayed member
belongs. Group membership can be changed, or augmented, by checking an
alternative, or additional, check box. Group members are displayed in turn
by means of the navigation buttons. This all works well.

A new requirement is to provide an option button, alongside each group
designator, which, when clicked, will overlay the currently-displayed form
with a pop-up continuous form, listing the current members of the associated
group. The problem then arises that the pop-up form needs to be bound to the
same tables (tbl_Members & tbl_Group_Members) as the current form. When any
option button is clicked, a run-time error 3008 results, complaining that
multi-user access is being sought to the bound tables, which may thereby be
programmatically altered, even though the pop-up is opened in read-only mode.

The obvious solutions all seem to involve difficulties. One is to
substitute a sub-form for the pop-up. Unfortunately, however, the
form/sub-form link is the group designator, which is not defined until the
option button is clicked to select the group for which the membership list is
required. I think I am right in stating that form/sub-form links cannot be
created/recreated dynamically.

Another option is to provide a sub-form for each group, hiding all of them
on opening the main form and then displaying the appropriate sub-form when
the selector button is clicked. With seven sub-forms currently involved, and
maybe more to follow, that looks like the use of a sledgehammer to crack the
proverbial nut.

Other ideas might be to close the main form before opening a pop-up, or make
copies of the tables to which the pop-ups are bound but none of these options
appear attractive. Can anyone suggest a better approach?

 

[Peter Hallett]

Thanks, Erez. That was the sort of answer I was hoping for. Redesigning the
application to circumvent the run-time error was clearly going to be a pain.
If changing a setting or two will enable me to use the original design, that
will save an awful lot of bother.

 

[Peter Hallett]

Sorry, Erez, I have to come back on this one. Having checked Tools > Options

Advanced > Default Open Mode, I discovered that it was already set to

‘Shared’. I removed any possible record locks and tried again but with the
same result. I get run-time error 3008 which states, “The table,
‘tbl_Members’ is already opened exclusively by another user or it is open
through the user interface and cannot be manipulated programmatically.â€
Having double-checked the settings, then closed and re-opened the database,
the problem still remains. Unless you can suggest another approach, it looks
as if I am going to have to copy ‘tbl_Members’ and bind the pop-up form to
the copy. I am running Access 2003.

 

[Peter Hallett]

Erez,

Many thanks for your kind offer to pursue the problem I encountered with
multi-user access to a table. I have assessed the building of a test
sub-database, comprising just the essential forms and tables, as suggested,
but the task looks pretty daunting. The parent database is fairly big –
although that depends on what you regard as ‘big’. The front-end is around 7
Mbyte whilst the back-end is approaching 11 Mbyte, with a relationship
diagram that resembles a huge spider’s web. It soon became apparent that
Access was not going to be at all co-operative in prizing the latter apart.

Were the need pressing, I would certainly take up your offer but the problem
has proved relatively easy to bypass through the use of copy tables. I
therefore wonder whether the expenditure of the considerable time –
particularly yours – that would be needed to extend the investigation would
be justified. I nevertheless remain very grateful for your suggestion.

In the course of investigating the original difficulty, a possibly related
problem arose. In copying the aforementioned tables, the use of CopyObject
produced another run-time error, complaining that the object table could not
be found – hardly surprisingly, since I wanted VBA to create it, where it did
not exist. The example provided in the syntax suggests that this is the way
that the CopyObject method normally works. Indeed, the database already
contains numerous instances of this in my end-of-year archiving module, which
has operated faultlessly for the last five years, so I am left wondering what
has changed. Have there been any updates to Access 2003 which have served to
alter the rules?

hello again
still, since it's something to do with the specific design, i cant think of
another way to help you, unless you can send the file itself, which is ok
with me, you can make an emty mdb file with only the form, pop-up form and
related tables and send to (e-mail address removed), and i'll see if i can find the
problem

Erez

Sorry, Erez, I have to come back on this one. Having checked Tools > Options

Advanced > Default Open Mode, I discovered that it was already set to

‘Shared’. I removed any possible record locks and tried again but with the
same result. I get run-time error 3008 which states, “The table,
‘tbl_Members’ is already opened exclusively by another user or it is open
through the user interface and cannot be manipulated programmatically.â€
Having double-checked the settings, then closed and re-opened the database,
the problem still remains. Unless you can suggest another approach, it looks
as if I am going to have to copy ‘tbl_Members’ and bind the pop-up form to
the copy. I am running Access 2003.

hi
there's no reason whatsoever why 2 (or more) forms show the same data from

[quoted text clipped - 46 lines]

copies of the tables to which the pop-ups are bound but none of these options
appear attractive. Can anyone suggest a better approach?