I have a subform that is a continuous form(PK ProjectID).  The record source
is a query that limits the field called KeyMilestonesSubID to a choice of 15
or 22.  

Ther fields are ProjectID, KeyMilestonesSubID, Unit, ActualDt and OrigDt.

How do I make it a requirement that the user enter KeyMilestonesSubID and
ActualDt for both selections- 15 and 22?

They must select KeyMilestonesSubID 15 and ActualDt and they must also
select KeyMilestonesSubID 22 and the ActualDt
--
deb
--
deb

in the before update event you could do something like this...

if len(KeyMilestonesSubID ) <> 15 and len(KeyMilestonesSubID ) <> 22 then
   msgbox "invalid id"
   cancel = true
endif

Tried it in before update of the form and also before update of the field
KeyMilestonesSubID
did now work - nothing happened
wondering why use len and why <>?

There is only two choices in the drop down 15 and 22.
I want to make it where the  must select 15 from dropdown KeyMilestonesSubID
and enter data in ActualDt
then they must also
select 22 from dropdown KeyMilestonesSubID and enter data in ActualDt

They must enter both in the continuous form or they get an msg box saying to
enter both

--
deb

sorry.  I misread your post to mean the id had to be either 15 or 22 digits.

You an do most of the enforcement at table level.

The first thing you should do is create a unique index on the ProjectID and
KeyMilestonesSubID columns in the subform's underlying table.  The easiest
way of doing this is by making the two columns the table's composite primary
key, which you do in table design view by Ctrl-clicking on each field, making
sure you click on the field selector (the little grey rectangle to the left
of the field name), then right-click and select 'Primary key' from the
shortcut menu.

If you are already using another column as the primary key you can index the
fields uniquely by selecting indexes from the View menu.  Enter a suitable
index name in one row of the left column, then enter the column names on two
rows of the Field Name column.  With the first row (the one with the index
name) selected enter Yes as the 'Unique' property.

You should also give the KeyMilestonesSubID column a validation rule limiting
its values to 15 or 22.

With the unique index the user cannot enter the same KeyMilestonesSubID value
twice for the same ProjectID.  Setting the Required property of the ActualDt
column to True (Yes) will force the user to enter a value for that.

The difficulty is in ensuring that the user enters two rows in the subform.
One way would be to automatically insert the rows using code when a new
record is inserted in the main form, but to do that you'd have to insert
legitimate default values into the ActualDt column, so there would still be
no way you'd guarantee that the user updates these to the true values.

As the parent record is saved once you move focus to the subform you cannot
at that stage check that two related records have been created, so you need
to be more circumspect.  Off the top of my head something like this might
work, but it would need testing:

1.  In the parent form's module declare a variable in the module's
<declarations> area:

    Dim lngProjectID as Long

2.  In the parent form's AfterInsert event procedure assign the current
ProjectID to the variable:

    lngProjectID = Me.projectID

3.  In the form's Current event procedure check to see if there are two rows
in the subform's underlying table for the ProjectID value in the variable.
If not inform the user and move the parent form to the record in question:

    Const MESSAGETEXT  =
         "Missing subrecord(s).  Please enter two."

    Dim rst As Object
    Dim strCriteria as String

    If lngProjectID > 0 Then
        strCriteria = "ProjectID = " & lngProjectID
        If DCount("*", "YourSubTable", strCriteria) < 2 Then
            Set rst = Me.Recordset.Clone
            With rst
               .FindFirst "ProjectID = " & lngProjectID
                If Not .NoMatch Then
                    Me.Bookmark = .Bookmark
                    MsgBox MESSAGETEXT, vbExclamation, "Warning"
                End If
            End With
        End If
    End If

4.  While the above should check whether both records have been entered into
the subform if the user navigates to another record in the parent form, it
won't do so if the user attempts to close the parent form, so you'd need to
do something similar in the parent form's Unload event procedure:

    Const MESSAGETEXT  =
         "Missing subrecord(s).  Please enter two."

    Dim rst As Object
    Dim strCriteria as String

    If lngProjectID > 0 Then
        strCriteria = "ProjectID = " & lngProjectID
        If DCount("*", "YourSubTable", strCriteria) < 2 Then
            Cancel = True
            Set rst = Me.Recordset.Clone
            With rst
               .FindFirst "ProjectID = " & lngProjectID
                If Not .NoMatch Then
                    Me.Bookmark = .Bookmark
                    MsgBox MESSAGETEXT, vbExclamation, "Warning"
                End If
            End With
        End If
    End If

The only other situation I can envisage is if the user deliberately deletes a
record from the subform, which would not be detected by the above.  You can
prevent this by setting the subform's AllowDeletions property to False (No).

I stress again that the above is completely untested.  I'd be surprised if
some debugging isn't required.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200911/1

If you  haven't gotten your problem solved yet, be sure to specify what
version of Access you are using, and explain what you are doing:
Is this all about when you are trying to edit a record in the subform? Add a
new record? What is going on in the main form?

If it is for editing, one approach might be:
NameofSubform_BeforeUpdate(Cancel as integer)
   if me.KeyMilestonesSubID = 15 and not isnull(me.actualDT) then exit sub
   if me.KeyMilestonesSubID = 22 and not isnull(me.actualDT) then exit sub
   Cancel = true
   msgbox "You must enter either 15 or 22 in keyMilestone, AND the actual
date"
end sub

--
Frank H
Rockford, IL