Using one-to-one relationship

Mike,

If you are bound on using unbound forms (pun intended) then you need to do a lot more research on how to do them. If you just want to display data, you could use DLookups (as Ken said you can't use a query as the Controlsource of a control). You could use a temp table and other methods. If you want interactivity (to be able to enter and edit data, Unbound forms become even more complex to do. You need to have a fuller understanding of VBA and database strucutre before you do unbound forms.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

I agree that my query makes little sense.  In my fumbling way I'm looking for a way to pick out a single record.

I wish I could insert a screen shot in here of the form I need.  It's a tab control.  On the first tab, there are three required dates in different locations, a number of other pieces of necessary information, and a number of "evaluation criteria" (which I wanted to treat in the same way as the milestones).  There are four other tabs with similar interleaving of milestones, criteria and other information.  The form models the chronological business process in that the user won't enter any milestones residing on later tabs (or evaluation criteria) until the earlier tabs are complete.  For the user's benefit, I'd like to preserve the chronological sequence of the process.  And, all this is just for Phase One of the process.  There are six phases total with similar interleaving of milestones/criteria/other info.

So, yes, I'd like to learn how to reinvent the wheel rather than require the user to only view milestones in one subForm, criteria in another subForm, action items in a third subForm, etc.  Unless they see a milestone in the context of (for instance) a "site investigation tab" (which is a subset of Phase One) along with related site investigation information, it will likely be a confusing subform without extensive descriptions attached to each milestone/evaluation critera/etc.

To the extent I follow your code, it looks analogous to what you suggest I do for milestones.  But, PM tasks are repetitive and only need, at most, a start/finish date.  Each project I'm dealing with spans a year or more with absolutely no repetition within a single project, has about 70 different dates spread out through different phases, and even within a phase are broken up into subphases.  Some of the dates are inapplicable depending on what evaluation criteria are selected.  There may only be a total of 3-4 projects in a year.  I've been simplifying this all along.  There are six phases in each project.  In addition to about 70 milestone dates, there are about 65 different evaluation criteria, each of which have a unique list to choose only acceptable entries for the criterion (they're all different), and a notes field.  Interspersed with all this are internal personnel choices and assignments, external firms (lawyers, surveyors, environmental assessors, etc.) to be retained and assigned tasks, and many other pieces of information necessary to retain.   It just doesn't make sense to simply present the user with continuous subforms dealing with only milestones, or evaluation criteria, even if they are  divided up into phases.  I suppose I could get elaborate and divide each phase into subphases into subsubphases to the point where only one record is chosen on the subForm (or subphases tied to only the milestones/criteria/etc on a single tab) but that's getting ridiculous and defeats the purpose of being able to easily add a new milestone without extensive modification.

The project also requires printing numnerous forms/reports in a prescribed format that intersperses milestones/criteria/etc. at different locations in the reports.  I don't see how I can accomplish that if I can't directly access a particular milestone record.

I guess my frustration is showing.

Ken - I understand how to do what you've suggested.  I've created a main and continuous subform that does exactly what you prescribe and it works fine.  I just don't think that approach lends itself to a long, linear business process as opposed to a repetitive task.

Probably at this point y'all are getting weary of my questions and resistance to using continuous subforms.  I am willing to learn, and if given appropriate references, I can figure it out.

So, why don't we leave it at this?  Could someone provide me with the references I need to learn how to deal with unbound forms so I can connect a single control with a single milestone/criteria?

I very much appreciate all the information you, Scott and Roger have given me.  It's been very helpful in understanding why I can't do something and/or what constraints exist, but other than using continuous subForms to enter a single category of data, I don't have the positive side of that.  You know the old saying:  it's better to teach a man how to fish (give him a reference source) than to give him a fish (a piece of code).  At this point, I need to learn how to fish, else my only choice is to go back to one large project table, which I can make work as kludgy as it may be.

Please tell me where to look to understand what I need to do. 

Thanks

Mike

Your query makes little sense as you are repeating the join criterion in the JOIN and WHERE clause.  More to the point queries are used as the RecordSource of a form not the ControlSource of a Control.

Just use the built in functionality of a bound subform in the way I described.  No point in trying to reinvent the wheel. 

To give you an idea of how this sort of thing works here is the code behind a button on a form in a database which I once developed for scheduling preventative maintenance tasks (PMs) on engineering structures.  It inserts rows into a table and then requeries a bound subform to show the new rows.  The details of how the SQL statement is built are not really relevant, but in essence all it does is execute an SQL statement to insert rows into a StructurePMJobs table, which is analogous to your SQL stement to insert rows into the tblProjectMilestones table, and then requery the  sfcStructurePMJobs subform control within the parent form.  It also uses ADO rather than DAO as in your case, but that's also beside then point.  The subform in this case is in continuous forms view and based on the following query:

SELECT StructurePMJobs.* FROM StructurePMJobs ORDER BY StructurePMJobs.DateDue;

and is linked to the parent form on two pairs of fields, StructureNumber and PMCode, so it shows the schedule for jobs to be undertaken on the current   structure and PM task in the parent form.  In your case the link would be on ProjectID to show the Phase 1 milestones for the current project.

Private Sub cmdConfirm_Click()

    On Error GoTo Err_Handler

    Const MESSAGETEXT = _

        "A structure, PM and number of jobs to be added must be selected."

    Dim strCriteria As String

    Dim dtmNewdate As Date

    Dim varLastdate As Variant

    Dim strSQL As String

    Dim n As Integer

    Dim cmd As ADODB.Command

    ' first confirm all necessary controls have values

    If Not IsNull(Me.cboStructure) _

        And Not IsNull(Me.cboPM) _

        And Not IsNull(Me.txtNumberOfJobs) Then

        ' loop number of times entered in the form

        ' and insert a row at each itereation of the loop

        For n = 1 To Me.txtNumberOfJobs

            ' build criteria for finding last due date

            ' for selected structure/PM

            strCriteria = "StructureNumber = """ & _

                Me.cboStructure & """ And PMCode = """ & _

                Me.cboPM & """"

            ' get last due date

            varLastdate = DMax("DateDue", "StructurePMJobs", strCriteria)

            ' call NextDueDate function to get next

            ' due date for selected PM

            dtmNewdate = NextDueDate(Me.cboPM, varLastdate)

            Set cmd = New ADODB.Command

            cmd.ActiveConnection = CurrentProject.Connection

            cmd.CommandType = adCmdText

            ' build and execute SQL statement to insert row into table

            strSQL = "INSERT INTO StructurePMJobs" & _

                "(StructureNumber,PMCode,DateDue) " & _

                "VALUES(""" & Me.cboStructure & """,""" & _

                Me.cboPM & """,#" & Format(dtmNewdate, "yyyy-mm-dd") & "#)"

            cmd.CommandText = strSQL

            cmd.Execute

            ' requery subform to show new rows

            Me.sfcStructurePMJobs.Requery

        Next n

    Else

        MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"

    End If

Exit_Here:

    Exit Sub

Err_Handler:

    MsgBox Err.Description

    Resume Exit_Here

End Sub

Ken Sheridan, Stafford, England