Using one-to-one relationship

If the subform is to be restricted to the phase 1 milestones only then, yes, include a WHERE clause:

WHERE MilestoneTypePhase = 1

but I'd suggest also following this with an ORDER BY clause to sort the rows by date as and when the dates are entered.  If you want the entered dates last in the list in ascending date order, with the as yet unentered dates first:

ORDER  BY ProjectMilestoneDate

If you want dates in descending order, with the unentered dates last, you'd use:

ORDER  ProjectMilestoneDate DESC

If on the other hand you want the dates in ascending order, but with the unentered dates last you need to use a bit if trickery:

ORDER  BY NZ(ProjectMilestoneDate,#2100-01-01#)

or similarly if you want the dates in descending order, but with the unentered dates first:

ORDER  BY NZ(ProjectMilestoneDate,#2100-01-01#) DESC

The NZ function returns the end of century date in place of a Null, causing it to sort after (or before in descending order) the current dates.

To force the subform to reorder the rows immediately after a date is entered put Me.Requery in the ProjectMilestoneDate control's AfterUpdate event procedure.

You don't need to do anything to pair the milestone type and date; that's already inherent in the data structures.  The MilestoneTypeID which you've inserted via the SQL statement maps to the primary key of the tluMilestones table, so each row will map to a separate milestone type value in that table, and the date entered in the referencing row will be associated with that milestone type.

As the subform is already populated with the relevant rows you can set its AllowAdditions property to False (No).  This will remove the unnecessary empty row at the bottom of the subform.

Ken Sheridan, Stafford, England

Thanks for the replies.

I admit that I'm getting a little frustrated with myself, mostly for not being able to ask the correct question.

I do understand that "When you run the Append query, it adds 5 SEPARATE records to the table."  And, "You don't need to do anything to pair the milestone type and date; that's already inherent in the data structures."

However, I do need to be able to pick out a specific record from one of the five records, to either display on a form or a report, rather than as a list of the five records and/or a continuous form.  The user data entry subform will be a tab control.  The dates will be spread across different tabs, not all in one tab.  The way to distinguish amongst them is through the MilestoneType field. 

So, I guess what I'm asking is:  On a subform, set up as we've been discussing, where x new milestone records are added via the main form, each distinguished by a MilestoneType value ranging from 1 -> x (actually, the MilestoneTypeID), and ignoring the need to display the paired milestone description (which I don't need to do; I can use hard coded labels), how do I bind a single control to asingle milestone record by specifying the record's MilestoneType value?

In other words, I want to be able to do what Ken suggested in the following, but on a record by record (individual control) basis:

"If you don't want to be able to edit the MileStoneType, but merely enter the date then, as the subform's      RecordSource, return the MilestoneType column in the query rather than the MilestoneTypeID:

SELECT ProjectMilestoneID, ProjectID, MilestoneType, ProjectMilestoneDate FROM tblProjectMilestones INNER JOIN tluMilestonesON tblProjectMilestones.MilestoneTypeID = tluMilestones.MilestoneTypeIDORDER BY MilestoneTypePhase;

In this scenario add two text boxes to the form with MilestoneType and ProjectMilestoneDate as their ControlSource properties.  Set the Enabled property of the MilestoneType text box to False (No) and its Locked property to True (Ye) to make it read only.  Link the subform to the parent form on ProjectID as above,"

Scott - you seem to be saying that I can't pick out or set the value of a single milestone record without using an unbound form.   Consequently, I'm going to have to get up to speed in understanding how to use unbound forms, else I can't implement the project as I need to.  I'm more than willing to put in the study time to accomplish this if you could cite some appropriate references for me.  There's certainly nothing like this in the standard Access reference books that I've consulted.  (For what its worth, I've ordered a copy of "Access Cookbook", which Roger suggested.  Perhaps there's some guidance in there?)

I still don't know if my question is clear.  In my simplistic view, what I need to do is be able to bind a single subForm control to a single milestone record for display or data entry purposes, following the scheme that we've been discussing.

Mike

At the risk of taking this further afield, I would have thought that I could use a query tied to a control in the subForm to pick out a single Milestone record.

For instance, it would seem that something like this should work:

SELECT tblProjectMilestones.ProjectMilestoneDate

FROM Project INNER JOIN tblProjectMilestones ON Project.ProjectID = tblProjectMilestones.ProjectID

WHERE (((tblProjectMilestones.MilestoneTypeID)=1) AND ((tblProjectMilestones.ProjectID)=[Project].[ProjectID]));

By varying the value for tblProjectMilestones.MilestoneTypeID it should pick out each of the records in turn.

I tried this on the subForm.  What I got was an error message saying:  "At most one record can be returned by this subquery."  Which, of course, is exactly what I want.  But when I acknowledge the error, the main form opens but not the subForm.

Is this heading in the direction of using unbound forms?

Mike