Neville.
Perfect sense.
This is the sort of problem that can give you a very bad headache, but there
is a simple solution, which I think works best. The following solution
simply makes a warning label visible when there are no records in the
subform. Here is a three-step procedure:
1. On the main form, create a label named "lblWarning" and set its text
to, say, like "Warning! SubForm RecordCount is zero!". Set the label's
font to something eye-catching like red, bold text. Set the label's Visible
property to "No".
2. In the Properties sheet for the SubForm, click the Events tab and
select [Event Procedure] for the following events:
On Current
AfterInsert
AfterDelConfirm
On Open
On Close
When you finish pasting in the code (next paragraph), you might double-check
that the events are still set to fire.
3. Paste the following code into the class module for the SubForm. Notice
I've declared the variable "mobjRST" as a DAO recordset, so, in the VBA
editor, you will need to open the Tools menu and select References and set a
reference to Microsoft DAO 3.6.
Option Compare Database
Option Explicit
Private mobjRST As DAO.Recordset
Private mfIsSubForm As Boolean
Private mlngRecordCount As Long
Private Sub Form_Close()
If Not mobjRST Is Nothing Then mobjRST.Close
Set mobjRST = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
' Clone this form's recordset:
Set mobjRST = Me.RecordsetClone
' Check if this form has been opened as a SubForm:
mfIsSubForm = IsSubForm()
' Ensure these events fire:
Me.AfterDelConfirm = "[Event Procedure]"
Me.AfterInsert = "[Event Procedure]"
Me.OnCurrent = "[Event Procedure]"
End Sub
Private Function IsSubForm() As Boolean
Dim strParentName As String
On Error Resume Next
strParentName = Me.Parent.Name
IsSubForm = (Err.Number = 0)
End Function
Private Sub Form_AfterDelConfirm(Status As Integer)
' This event fires if a Delete event is not canceled.
Call CalculateRecordsInSubForm
End Sub
Private Sub Form_AfterInsert()
' This event fires after a new record
' is inserted in the SubForm:
Call CalculateRecordsInSubForm
End Sub
Private Sub Form_Current()
' This event fires when the record changes in the
' SubForm AND when the record changes in the
' parent form. The purpose of the next code line
' is to recalculate the record count when the
' record changes in the MainForm:
Call CalculateRecordsInSubForm
End Sub
Private Sub CalculateRecordsInSubForm()
' See if form is running as SubForm:
If Not mfIsSubForm Then GoTo Bye
' Get current record count:
mlngRecordCount = mobjRST.RecordCount
' Show/hide the Warning label:
Me.Parent.lblWarning.Visible = (mlngRecordCount = 0)
Bye:
Exit Sub
End Sub
That's it! I think it works well because, as you move from record to record
in the Main Form, the Warning label appears whenever there are no records in
the SubForm. This happens regardless of whether the Main Form is dirty, so
it's a good reminder to the user as they poll through records. Also, you
don't have to fuss about canceling events or moving backwards and forwards
in the recordset or knowing whether the user clicked the SubForm.
The warning label disappears when the first new record is saved in the
SubForm for any given record in the Main Form - specifically, when the
Pencil icon changes to an Arrow icon in the vertical record-selector bar on
the left side of the SubForm (assuming you're showing the record-selector
bar). What's more, if you delete the only record in the SubForm for any
given Main-Form record, the warning label becomes visible.
Good luck with your project.
Regards
Geoff
NevilleT said:
Hi Geoff
Just to put it into context, main form is action items. These actions
may,
or may not relate to projects. If they relate to projects, the subform
record stores the project information. E.g. Action item 1 relates to
project
6 and 9. There is an Action/Project table that stores ActionNo and
ProjectNo.
So one action can have no Action/Project records, one Action/Project
record
or many Action/Project records. For 90% of actions there will be at least
one Action/Project record - hence the warning message.
Hope that makes sense.
Neville
GeoffG said:
Neville,
You are right of course. Just to be clear, do you want at least one
complete
record in the subform for each record in the main form? It would seem
more
usual to allow records in the main form without necessarily having any
records in the subform - but, if a new record is started in the subform,
then it should be relatively easy to ensure that all its compulsory
fields
must be completed (or all its edits abandoned). If you must have at least
one record in the subform for each record in the main form, then I
suppose
I'm a bit puzzled why you'd need this. Just wanted to clarify before
thinking about this further.
Geoff
Hi Geoff
I was thinking along the same lines. Problem is the trigger will also
fire
when they move to the subform. That is I fill in the main form, then
click
on the subform to update the data on the subform. Up pops a message.
If
I
could somehow identify that they were on the subform, I could stop the
message.
Neville
:
I've not tried this but it seems you should write code in the
BeforeUpdate
event of the main form. Use something like:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If ... [no data in subform]... then
[maybe use MsgBox here]
Cancel = True
Exit Sub
End If
End Sub
The above code would work as follows. If:-
1. the main form is dirty (ie there are edits waiting to be
written)
2. the user tries to move to a different record in the main form,
and
3. you've selected [Event Procedure] in the form's BeforeUpdate
property
then the main form's BeforeUpdate event will fire and the above event
procedure will run.
By setting the event's Cancel argument to True (as above), the record
won't
be updated, the AfterUpdate event won't run, and the user will be
prevented
from moving to a different record in the main form.
You might want display a message telling the user what's going on.
You
could advise the user to press the ESC key twice to undo any edits if
they
simply want to abandon edits.
It sounds like you know how to access the field controls in the
subform
to
determine if they're empty, ie by referencing the Name of the subform
control, then its Form property and then its Controls collection. The
following convoluted syntax can be compressed:
Me.MySubFormControlName.Form("MySubFormName").Controls("MyControlName")
which can be compressed to:
Me.MySubFormControlName("MyControlName")
I've not worked through your case scenario in detail, so I don't know
what
you'll find when you access the field controls in the subform if it's
not
yet been edited (presumably nothing in all fields). You may want to
consider writing code for the BeforeUpdate event of the subform to
ensure
all its data has been completed. There may be a better way to do all
this,
but this approach occurs to me just now.
Geoff
I have a form which has a subform. If a new record is created (or an
existing record edited) on the main form I want to check that
information
was
also completed on the subform before moving to another main form
record.
If
not, I display a warning.
For example, new record created in tblMainForm. Unless a record is
created
in tblSubForm you should not be able to move to the next record in
tblMainForm.
My problem is that I cannot work out what event, or combination of
events
to
use. If I use after update on the main form, and change focus to
the
subform, the warning is displayed as the main form is saved. Is
there
some
way to identify that the focus is now on the subform and supress the
warning?
What I actually want is:
If a new record is about to be displayed on the main form, check the
subform. If there is no record in the subform, display a warning
and
stop
the move to another main form record.
