Multi-level forms, tab controls, and subforms

[bdp]

I have a scheme that looks like:
Form (level 1), which has
Tab controls (level 2), on which are
Subforms (level 3), one of which opens another
Form (level 4), which carries
Tab controls (level 5), on which are
Subforms (level 6), one of which
opens another
Form (level 7)
- only it doesn't! Double-clicking on the record selector of a subform is
intended to open the form "below" it (in the representation above). Down to
level 3, it works fine; a double-click in the level 3 subform opens the level
4 form just as I want, so I replicated the code from levels 1, 2 and 3 to
levels 4, 5 and 6 (with the obvious adjustments for different field names),
and prepared to rejoice. It was not to be.
A single-click on the record selector at level 6 produces the message
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again."
I can't get to level 7. Is this an inherent limitation of Access, or have I
done something improper along the way (and, since I can't see what it is, has
anybody any experience that could point me in the right direction?)
Brian.

 

[Steve Schapel]

Brian,

The information you have given is too general for anyone to be able to
offer explicit help. But maybe a few comments might help clarify.

First of all, the tab controls are irrelevant, and do not constitute a
"level" in the sense you are using it.

Secondly, no you have not come up against a limitation with Access.

With the error message you received, you will need to look at the form
or subform that you are moving *away from* at the point that this
happens, as this will give the clue as to where Access is trying to save
data, but failing to do so. What is the data that is being saved? Why
does Access regard that data already exists with the same value in a
unique index? It does not have to do with the form design/structure as
such, but with the data relationships. So in other words, you need to
look at the tables/queries that your forms/subforms are bound to, and
review how the logic of data entry/editing is happening.

 

[bdp]

Hi, Steve, and thanks for a rapid response. I deliberately held off the
detail because I suspected there'd be a conceptual answer that would point me
in the right direction. You've clarified the significance (or otherwise) of
tab controls, and eliminated Access limitation (which I'd expected would be
the case, but it's always good to have confirmation from those who know these
things).

I'm moving "Away from" the level 6 subform, towards the level 7 form. The
subform is a tabular continuous form bound to the same table as is the form
at level 7; the difference is that the subform lists all the child records of
the parent record shown at level 5, and double-clicking in the subform's
record selector executes a procedure that's intended to pass the PK of the
clicked record at level 6 to the single form at level 7, in which all data
for that record is shown and can be modified (the level 6 subform contains
only sufficient data to allow the user to pick which record s/he wants to
modify). In case it's relevant, the relationship between the level 5 parent
record and the level 6 child records enforces referential integrity and
cascade updates related fields. I notice - to my astonishment, because I
thought I'd covered it - that the relationship between the level 1 parent and
its level 2 children wasn't defined in the database. I've just inserted that
relationship (with the same rules) and the upper level operations aren't
affected - they continue to work as before. That change hasn't had any
effect on the problem I've outlined (which I'd pretty much expect anyway).

A single click in the record selector at level 6 shouldn't have any effect -
there's no click event procedure, only double-click; yet it's on a single
click that the error message appears (I don't get a chance to double-click).
I infer that the single-click event is being interpreted as an instruction to
save a new record, which is in fact an existing record in the level 6 subform,
containing a PK that's already in use.

I assume that the single click doesn't invoke some automatic "add" process;
it doesn't at the levels above, from which I've derived the code behind the
level 6 subform.

Does this add enough detail to home in on the likely problem, or would it be
better to post the code behind appropriate forms - and if so, which ones?

Thanks for looking at this one, and for getting my mind more clearly wrapped
around what matters. Unfortunately, the wrapping process hasn't produced any
answers ... I'd be thoroughly grateful if there are any more suggestions!

Brian.

Brian,

The information you have given is too general for anyone to be able to
offer explicit help. But maybe a few comments might help clarify.

First of all, the tab controls are irrelevant, and do not constitute a
"level" in the sense you are using it.

Secondly, no you have not come up against a limitation with Access.

With the error message you received, you will need to look at the form
or subform that you are moving *away from* at the point that this
happens, as this will give the clue as to where Access is trying to save
data, but failing to do so. What is the data that is being saved? Why
does Access regard that data already exists with the same value in a
unique index? It does not have to do with the form design/structure as
such, but with the data relationships. So in other words, you need to
look at the tables/queries that your forms/subforms are bound to, and
review how the logic of data entry/editing is happening.

I have a scheme that looks like:
Form (level 1), which has

[quoted text clipped - 20 lines]

anybody any experience that could point me in the right direction?)
Brian.

 

[bdp via AccessMonster.com]

As Sherlock Holmes is reputed to have said, "when you've eliminated
everything else, what remains has to be the truth". Sleeping on the problem
produced a partial result, which is that the error message is being triggered
on leaving the level 4 form (a departure that's being recognised when I click
in the level 6 subform). Now all I have to do is prevent the "save" being
triggered when no change has been made at level 4, and when no attempt has
been made to add a new record - but that's a different issue and one I should
be able to handle.

Thanks, Steve, for a penetrating assessment of where the problem originated.

Brian.

Hi, Steve, and thanks for a rapid response. I deliberately held off the
detail because I suspected there'd be a conceptual answer that would point me
in the right direction. You've clarified the significance (or otherwise) of
tab controls, and eliminated Access limitation (which I'd expected would be
the case, but it's always good to have confirmation from those who know these
things).

I'm moving "Away from" the level 6 subform, towards the level 7 form. The
subform is a tabular continuous form bound to the same table as is the form
at level 7; the difference is that the subform lists all the child records of
the parent record shown at level 5, and double-clicking in the subform's
record selector executes a procedure that's intended to pass the PK of the
clicked record at level 6 to the single form at level 7, in which all data
for that record is shown and can be modified (the level 6 subform contains
only sufficient data to allow the user to pick which record s/he wants to
modify). In case it's relevant, the relationship between the level 5 parent
record and the level 6 child records enforces referential integrity and
cascade updates related fields. I notice - to my astonishment, because I
thought I'd covered it - that the relationship between the level 1 parent and
its level 2 children wasn't defined in the database. I've just inserted that
relationship (with the same rules) and the upper level operations aren't
affected - they continue to work as before. That change hasn't had any
effect on the problem I've outlined (which I'd pretty much expect anyway).

A single click in the record selector at level 6 shouldn't have any effect -
there's no click event procedure, only double-click; yet it's on a single
click that the error message appears (I don't get a chance to double-click).
I infer that the single-click event is being interpreted as an instruction to
save a new record, which is in fact an existing record in the level 6 subform,
containing a PK that's already in use.

I assume that the single click doesn't invoke some automatic "add" process;
it doesn't at the levels above, from which I've derived the code behind the
level 6 subform.

Does this add enough detail to home in on the likely problem, or would it be
better to post the code behind appropriate forms - and if so, which ones?

Thanks for looking at this one, and for getting my mind more clearly wrapped
around what matters. Unfortunately, the wrapping process hasn't produced any
answers ... I'd be thoroughly grateful if there are any more suggestions!

Brian.

Brian,

[quoted text clipped - 21 lines]

 

[Steve Schapel]

Brian,

Great to hear of the progress you have made here.

It is puzzling as to why a new record is apparently being created at
"level 4" when none is intended. Something must be causing this (I
don't know, maybe that's also a quote from Sherlock).

 

[bdp via AccessMonster.com]

Steve - and anybody else who comes across a similar problem - I've found the
reason why a new record is being "created" at level 4, at least in Access'
opinion.

The event procedure that opened the form at level 4 (from the level 3 subform)
was forcing data into bound controls in the level 4 form. One of these bound
fields was the PK. Presumably, Access decided that this meant a new record
was being created, though of course the PK was taken from that record in the
level 3 subform and wasn't creating anything new. Anyway, Access decided
that it was, and (reasonably) complained. Once I removed that line of code,
and ensured that I opened the level 4 form with a "WHERE" condition to hit
the right record, the problem vanished in a puff of smoke ... and, for all I
know, mirrors.

Steve, thanks again for your help and insight.
Brian.

Brian,

Great to hear of the progress you have made here.

It is puzzling as to why a new record is apparently being created at
"level 4" when none is intended. Something must be causing this (I
don't know, maybe that's also a quote from Sherlock).

As Sherlock Holmes is reputed to have said, "when you've eliminated
everything else, what remains has to be the truth". Sleeping on the problem

[quoted text clipped - 4 lines]

been made to add a new record - but that's a different issue and one I should
be able to handle.

 

[Steve Schapel]

Brian,

Good to hear it is resolved. Best wishes with the rest of your project.