Mainform & subform with same record

[Allen Davidson]

Hi - I have a mainform with a subform. Mainform & subform access the same
record. I.E it is not a one to many.
Subform holds a nested tab control displaying fierld from the same record
as ther mainform.

Mainform is iopened with a docmd openform with either acFormEdit or acFormAdd.

I have tried to sync the two forms setting the subform recordset to the
mainform recordset which works when displaying but when I try to enter or
change data in the subform I get the error "Update or CancelUpdate without
AddNew or Edit"

Any idea what I am doing wrong.

Thanks
Allen

 

[John W. Vinson]

Hi - I have a mainform with a subform. Mainform & subform access the same
record. I.E it is not a one to many.
Subform holds a nested tab control displaying fierld from the same record
as ther mainform.

Mainform is iopened with a docmd openform with either acFormEdit or acFormAdd.

I have tried to sync the two forms setting the subform recordset to the
mainform recordset which works when displaying but when I try to enter or
change data in the subform I get the error "Update or CancelUpdate without
AddNew or Edit"

Any idea what I am doing wrong.

Probably a couple of things. For one, why use a subform at all, rather than
just a tab control to display different fields? For another, having the same
record open from two different forms simultaneously will make it difficult at
best to edit data in the table; you'll get an error "record already open by
another user" - that's *YOU* on the other form. And probably most of concern,
though it's hard to tell from your post: if you have so many fields that you
need multiple tab pages to display all of them, your table may not be
correctly normalized!

What is the table? Why the subform at all, and (if you must use a subform) why
not just use the unique ID as the master/child link field, rather than using
code to set its recordset?

John W. Vinson [MVP]

 

[Allen Davidson]

Hi John,

Thanks for the interest in my question.

What I am tryin to do is record details of a market research case, tblCase,
a case has a date, subject, clientID moderatorID and many selection criteria
(about 40). The selection criteria are Marital Status, Type of Employment,
Social Class, Education, Ethnic background, Children. When a case has been
set up I want to deilpay a list of responents (people) that match the
selection criteria.

How I'm tryiong to do it is with a main form displaying subject, date,
ClientName & ModeratorName at the top with a tab control below. On one page
of the tab contol I want to enter the selectin criterria and on the second
page haved a subform displaying a list of respondents that match the
selection criteria. Some of these may be invited and would be entered into
tblCaseRespondent(CaseID, RespondentID). All OK so far. Because there are so
many selection criteria, too many to fit onto one page, I decided to put them
on a second tab contol and nest this on the first page of the main tab
control. I think the way to nest tab controls is by puttin them on a subform.
I sync the main form with the nested tab control and data is browsable OK
however if I try to enter data in the selection criteria I imediately get the
error message (like the form or recordset does not know it's in edit/add mode.

I think I might be trying to swim upstream and not working _with_ access. I
dont even get to the stage of 'another user changed the record'. I'm happy
with my DB design , it's 3NF and I'm very comfortable with relational design
& set theory (I code T-SQL for a living) but I'm struggling with design of
the interface. Prompted by your post re: 'another user changed ..' I'm
considering spliting tblCase in two (tblCase and tblSelectionCriteria with a
one to one relationship between them. I dont normally use 1 to 1 unless row
size becomes an isssue. It seems like changing the DB design to 'match/work
with' the user interface.

Any further comments are very welome.

Allen

 

[John W. Vinson]

How I'm tryiong to do it is with a main form displaying subject, date,
ClientName & ModeratorName at the top with a tab control below. On one page
of the tab contol I want to enter the selectin criterria and on the second
page haved a subform displaying a list of respondents that match the
selection criteria. Some of these may be invited and would be entered into
tblCaseRespondent(CaseID, RespondentID). All OK so far. Because there are so
many selection criteria, too many to fit onto one page, I decided to put them
on a second tab contol and nest this on the first page of the main tab
control. I think the way to nest tab controls is by puttin them on a subform.
I sync the main form with the nested tab control and data is browsable OK
however if I try to enter data in the selection criteria I imediately get the
error message (like the form or recordset does not know it's in edit/add mode.

If the controls on the mainform exist only to provide selection criteria, then
make the mainform (and all of those controls) *unbound* - nothing in the
Recordsource, nothing in the Control Source. Otherwise you'll *overwrite* the
first record with the values being selected as criteria!

John W. Vinson [MVP]

 

[Allen Davidson]

If the controls on the mainform exist only to provide selection criteria, then
make the mainform (and all of those controls) *unbound* - nothing in the
Recordsource, nothing in the Control Source. Otherwise you'll *overwrite* the
first record with the values being selected as criteria!

John W. Vinson [MVP]

Hi John,

The seslection criteria need to be saved as they form the brief for the
Case. I'm not sure what you mean about overwriting the first record with the
criteria values?

 

[Allen Davidson]

PS - Does my idea about storing the selection criteria in a seperate table
one to one with tblCase sound sensible?

 

[John W. Vinson]

The seslection criteria need to be saved as they form the brief for the
Case. I'm not sure what you mean about overwriting the first record with the
criteria values?

If the main form with the selection criteria is bound to the same table as the
subform to display the data, then when you open the form, by default the first
record in the form's Recordsource query will be on display, and displayed in
all the combo boxes.

If you select a different value from one of those combo boxes, *it will write
that value into that field of the currently selected record*, overwriting what
was there originally.

If you do need to save the selection criteria, then yes, you need another
table to do so. I'm dubious about a one to one relationship unless you are
comfortable saying that each record in your table will be searched for once,
and only once, never again during the existance of the database... in
implausible scenario in my mind!!!

John W. Vinson [MVP]

 

[Allen Davidson]

If the main form with the selection criteria is bound to the same table as the
subform to display the data, then when you open the form, by default the first
record in the form's Recordsource query will be on display, and displayed in
all the combo boxes.

If you select a different value from one of those combo boxes, *it will write
that value into that field of the currently selected record*, overwriting what
was there originally.

If you do need to save the selection criteria, then yes, you need another
table to do so. I'm dubious about a one to one relationship unless you are
comfortable saying that each record in your table will be searched for once,
and only once, never again during the existance of the database... in
implausible scenario in my mind!!!

John W. Vinson [MVP]

Hi John,

Thanks. probably a case of my not being clear about the design (or possible
design). The selection criteria are a part of the case EG the case could be
'focus group for power tools' the selection criteria might be 'male - married
or cohabiting - 20 to 40 years old', The title of the case would be 'Focus
group for power tools' and this would be saved along with the seletion
criteria.

The selection criteria would filter a list of respondents (people) - no one
to many link yet because the list of respondents is just possible candidates
for the case. One a respondent is invited to the case a one to many table
(CaseID, InvitedResponedntID) would be populated.

When viewing past cases it is neccessary to see the selerction criteria used
as this was part of the case brief.

The selection criteria provide a fixed search for that case. A new case
would have a different setr of criteria.

Allen