How Can You Make Sure Users Save Subform Records Correctly?

[Nick]

Hi Programmers,
Users are sometimes clicking a save button in a subform more than
once, attaching the same record(s) from a subform combobox > 1 time in
the underlying join table.

On the flip side, users are sometimes forgetting to click the save
button to save the record(s) in the subform and are, instead, clicking
some other button on the main form. Therefore, the subform record(s)
never get joined.

So, my question is how to help automate the subform save process.
This whole dbase is about recording student misbehavior incidents or
interventions, where 1 intervention has 1 student, 1 or > 1 staff
involved, and 1 or > 1 misbehaviors involved.

How can access detect the following?

Thanks for any and all help.

===============================================

Here is background info on the main and subforms:

SUBFORM #1 = sfrmStaffAndInts

SELECT jtblStaffAndInts.StaffAndIntsID,
jtblStaffAndInts.InterventionID, jtblStaffAndInts.StaffID,
jtblStaffAndInts.RoleID, Staff.StaffLastName, Staff.StaffFirstName,
Staff.StaffExtension, IIf(IsNull([StaffFirstName]),[StaffLastName],
[StaffLastName] & ", " & Left([StaffFirstName],1) & ".") AS txtStaff,
Concatenate("Select qryStaffAndInts_2.txtStaff from qryStaffAndInts_2
where InterventionID = " & jtblStaffAndInts.InterventionID) AS
txtStaffs
FROM Staff INNER JOIN jtblStaffAndInts ON Staff.StaffID =
jtblStaffAndInts.StaffID;


SUBFORM #2 = sfrmMisbehaviorsAndInts

SELECT jtblMisbehaviorsAndInts.MisbehaviorID,
jtblMisbehaviorsAndInts.InterventionID,
jtblMisbehaviorsAndInts.MisbehaviorsAndIntsID,
Misbehaviors.Misbehavior, Misbehaviors.Misbehavior AS txtMisbehavior,
Concatenate("Select qryMisbehaviorsAndInts_2.txtMisbehavior from
qryMisbehaviorsAndInts_2 where InterventionID =" &
jtblMisbehaviorsAndInts.InterventionID) AS txtMisbehaviors
FROM Misbehaviors INNER JOIN jtblMisbehaviorsAndInts ON
Misbehaviors.MisbehaviorID = jtblMisbehaviorsAndInts.MisbehaviorID;

MAINFORM = frmInterventions

SELECT Clients.ClientID, Clients.ClientLname, Clients.ClientFname,
Clients.ClientOname, Clients.DormID, Dorms.DormName, Dorms.DormPhone,
Clients.Active_Inactive
FROM Dorms INNER JOIN Clients ON Dorms.DormID = Clients.DormID
WHERE (((Clients.Active_Inactive)=0))
ORDER BY Clients.ClientLname, Clients.ClientFname;

 

[Keith Wilby]

Users are sometimes clicking a save button in a subform more than
once, attaching the same record(s) from a subform combobox > 1 time in
the underlying join table.

Why do you need a save button? If your form/sub-form are correctly linked
then the problem you're experiencing won't occur and records will be saved
as the user navigates between records.

Keith.
www.keithwilby.co.uk

 

[BruceM via AccessMonster.com]

I assume there is an Intervention table in which you store the StudentID,
with related tables for Staff and Misbehaviors. I would think there is a
Staff table and a Students table. Maybe something like this:

tblStudent
StudentID (primary key, or PK)
FirstName
LastName
etc.

tblStaff
StaffID (PK)
FirstName
LastName
etc.

tblIncident
IncidentID (PK)
StudentID
IncidentDate
etc.

tblIncidentStaff
IncStaffID (PK)
IncidentID
StaffID

tblMisbehavior
MisbehaviorID (PK)
Description

Each ID field links to its namesake in another table (e.g. StudentID in
tblStudent to StudentID in tblIncident). Use the Relationships window to set
up the relationships by dragging (in this case) StudentID from one table to
another. Click Enforce Referential Integrity when prompted.

There is a tblIncidentStaff table joined to tblIncident and tblStaff because
each Incident may have more than one Staff member involved, and one staff
member may be involved in many incidents.

You would have a main form based on tblIncident, with a combo box for
selecting the student (store the StudentID, but display the name in the combo
box -- the wizard can get you started). There is a (continuous) subform for
Staff, with a combo box based on tblStaff bound to StaffID, same as with
StudentID in the main Incident table. There is another subform for Incidents.


Each subform is in a "box" known as a subform control. The Link Child and
Link Master properties of the subform control need to be set to the linking
field. In this way the user need only enter a record, and the linking field
is entered automatically into the linking field in the child table. No code
is needed to assocate a Misbehavior with an Incident, for instance. There is
no need to save the record explicitly. If the users want a Save button (some
do), just add a Save command button with the code:
Me.Dirty = False
in its Click event. It won't change anything in most cases, as the record is
saved automatically when you move to a new record, close the form, and a
number of other actions.

I took some guesses here, as your question is very general. When posting a
question it is best to provide some information about the database structure.
Questions about code should include the code.

Hi Programmers,
Users are sometimes clicking a save button in a subform more than
once, attaching the same record(s) from a subform combobox > 1 time in
the underlying join table.

On the flip side, users are sometimes forgetting to click the save
button to save the record(s) in the subform and are, instead, clicking
some other button on the main form. Therefore, the subform record(s)
never get joined.

So, my question is how to help automate the subform save process.
This whole dbase is about recording student misbehavior incidents or
interventions, where 1 intervention has 1 student, 1 or > 1 staff
involved, and 1 or > 1 misbehaviors involved.

How can access detect the following?

Thanks for any and all help.

===============================================

Here is background info on the main and subforms:

SUBFORM #1 = sfrmStaffAndInts

SELECT jtblStaffAndInts.StaffAndIntsID,
jtblStaffAndInts.InterventionID, jtblStaffAndInts.StaffID,
jtblStaffAndInts.RoleID, Staff.StaffLastName, Staff.StaffFirstName,
Staff.StaffExtension, IIf(IsNull([StaffFirstName]),[StaffLastName],
[StaffLastName] & ", " & Left([StaffFirstName],1) & ".") AS txtStaff,
Concatenate("Select qryStaffAndInts_2.txtStaff from qryStaffAndInts_2
where InterventionID = " & jtblStaffAndInts.InterventionID) AS
txtStaffs
FROM Staff INNER JOIN jtblStaffAndInts ON Staff.StaffID =
jtblStaffAndInts.StaffID;

SUBFORM #2 = sfrmMisbehaviorsAndInts

SELECT jtblMisbehaviorsAndInts.MisbehaviorID,
jtblMisbehaviorsAndInts.InterventionID,
jtblMisbehaviorsAndInts.MisbehaviorsAndIntsID,
Misbehaviors.Misbehavior, Misbehaviors.Misbehavior AS txtMisbehavior,
Concatenate("Select qryMisbehaviorsAndInts_2.txtMisbehavior from
qryMisbehaviorsAndInts_2 where InterventionID =" &
jtblMisbehaviorsAndInts.InterventionID) AS txtMisbehaviors
FROM Misbehaviors INNER JOIN jtblMisbehaviorsAndInts ON
Misbehaviors.MisbehaviorID = jtblMisbehaviorsAndInts.MisbehaviorID;

MAINFORM = frmInterventions

SELECT Clients.ClientID, Clients.ClientLname, Clients.ClientFname,
Clients.ClientOname, Clients.DormID, Dorms.DormName, Dorms.DormPhone,
Clients.Active_Inactive
FROM Dorms INNER JOIN Clients ON Dorms.DormID = Clients.DormID
WHERE (((Clients.Active_Inactive)=0))
ORDER BY Clients.ClientLname, Clients.ClientFname;

 

[Nick]

Okay, here is the larger picture:

I was hoping that this would be able to be understood just on the info
provided.
There are actually 6 subforms on this mainform, only 4 of which
pertain in this case. It was a trick to get around Access's
limitations of 3 nested forms.

Here is the functionality:

When a user selects a client on the mainform, named frmInterventions,
the focus goes to a subform named subIntRecent. ClientID is the parent/
child link. On clicking a save button here, a new record is added to
another subform, sfrmIntsPast, which was placed adjacent to
subIntRecent (for sorting purposes), and the focus goes to another
subform...

sfrmStaffAndInts, where the link child field is InterventionID and the
link master field is [sfrmIntsPast].Form![InterventionID]. Here, the
user can select 1 or more staff involved. When pressing the save
button in this subform, the focus goes to another subform...

sfrmMisbehaviorsAndInts, where the link child field is InterventionID
and the link master field is [sfrmIntsPast].Form![InterventionID].
Here, the user can select 1 or more misbehaviors associated with the
incident.

 

[BruceM via AccessMonster.com]

You have written about the interface, with some information about linking
fields, but the structure is still unclear. It doesn't sound as if you are
nesting six levels of subform, but rather that you have a number of subforms
at the same level.

It also sounds as if you may have past interventions and recent interventions,
which presumably are from the same table, but with different date criteria.
But maybe not, as you are adding a new record to (I assume) the table
underlying sfrmIntsPast. There is just no way to know what is going on
without a description of the structure, and maybe some explanation of the
real-world situation behind the subforms.

Okay, here is the larger picture:

I was hoping that this would be able to be understood just on the info
provided.
There are actually 6 subforms on this mainform, only 4 of which
pertain in this case. It was a trick to get around Access's
limitations of 3 nested forms.

Here is the functionality:

When a user selects a client on the mainform, named frmInterventions,
the focus goes to a subform named subIntRecent. ClientID is the parent/
child link. On clicking a save button here, a new record is added to
another subform, sfrmIntsPast, which was placed adjacent to
subIntRecent (for sorting purposes), and the focus goes to another
subform...

sfrmStaffAndInts, where the link child field is InterventionID and the
link master field is [sfrmIntsPast].Form![InterventionID]. Here, the
user can select 1 or more staff involved. When pressing the save
button in this subform, the focus goes to another subform...

sfrmMisbehaviorsAndInts, where the link child field is InterventionID
and the link master field is [sfrmIntsPast].Form![InterventionID].
Here, the user can select 1 or more misbehaviors associated with the
incident.

 

[Nick]

It's been about a year since I've messed with this dbase; am having
trouble describe my basic question.

These subforms are continuous forms and cycle only within the
subforms--this is good because the users can immediately see what
they've already entered. They can keep addding staff and keep adding
misbehaviors in the subforms. Therefore, they must click a cmdSave
when they're done entering data in the subforms (clicking cmdSave then
changes the focus).

Well, the users are sometimes forgetting to click cmdSave in the last
form. I know that records are typically auto saved when going to a
next record, but, in this case, I want the form to detect if a new
subform record has been created, and if so, NOT allow the focus
anywhere else on the main form until the user clicks the cmdSave on
this subform.

Is this more clear?

 

[BruceM via AccessMonster.com]

It is not much clearer. I posted a sample table structure, and asked you to
do the same. I don't know what you mean by "cycle only within the subforms",
unlsess you mean the focus goes from one subform to the next in all cases.
Maybe you could use the Exit event of the subform control to set the focus to
the next subform. Alternatively, you could use code in the Save button's
Click event to set the focus.

A way I approached what may be somewhat similar, but with just one subform,
was to hide the subform when the main form is at a new record. When required
fields are filled in on the main form, unhide the subform control and set the
focus to it. In the subform control's Exit event, check the recordcount of
subform records related to the main record. If it is 0, cancel the Exit, and
notify the user an entry is needed.