This *should* be easy, right?? (RE: Sub-Forms)

[Bcanfield83]

Hi All,


I'm working on a Project Tracking form in my database. My goal is to have a main form and then a few sub-forms embedded on that form. But only 1 sub-form will be visible at a time.
On the main form is a Combo Box which contains a listing of Projects.
What I'm trying to do is set up the form so that once a Project is selected from the Combo Box, one of the sub-forms will update and contain the information pertinent to that project. But I can't seem to get it to work.. I've tried a couple things and either the sub-form simply does nothing (e.g.: I select a project from the Combo Box, but the related Text Boxes on the Sub-Form remain blank), or I get some type of Primary Key error. So, clearly I must not be linking the correct

Main Form is bound to tblProjects. The Combo Box contains the fields below in this table, though only the PROJECT_NAME field is actually visible in the Combo Box. Column 1 (PROJECT_ID) is the bound column. I have a hidden text box on the form that the PROJECT_ID # is bound to.

tblProjects:
PROJECT_ID (Auto-Number) - Primary Key
PROJECT_NAME (Text)

One of the Sub-Forms is bound to tblProject_Tasks. The PT_PROJECT_ID field corresponds with the PROJECT_ID field in the above table. Thus - in the Sub-Form properties, I'm using PROJECT_ID under "Link Master Fields", and PT_PROJECT_ID under "Link Child Fields".
In theory, this should work.. right?
All I'm looking to do is have the Sub-Form contain the listing of Project Tasks (Text Boxes) upon that Project being selected from the Combo Box on the Main Form.
Now obviously there's probably a much easier way to do this on a single form as opposed to using main form/sub-form, but I also am planning on having another Sub-Form for the purpose of entering a New Project, another one with stakeholder/project team info, etc.

tblProject_Tasks:
PT_TASK_ID (Auto-Number) - Primary Key
PT_PROJECT_ID (Number) --> joins to PROJECT_ID in tblProjects (Many to One relationship).
PT_TASK1 (Text)
PT_TASK2 (Text)
PT_TASK3 (Text)

[ranman256]

tblProject_Tasks table is not normalized.
you only need 1 PT_TASK (Text) field.
cause 1 project can have many tasks.

[June7]

The combobox must be UNBOUND (no ControlSource) otherwise you are trying to change data in record. What code is used to filter or GoTo the selected project?

Don't use a subform for entering new project. Go to new record row on the main form.

[Bcanfield83]

tblProject_Tasks table is not normalized.
you only need 1 PT_TASK (Text) field.
cause 1 project can have many tasks.

Duh - good point. Not sure what I was thinking there. The sad thing is I actually had 10 "PT_TASK" fields but I only listed 3...

[Bcanfield83]

I was able to get most of this setup as intended. My record source on the Main Form is pulling from tblProjects, where PROJECT_ID is equal to [Forms]![frmProject_Management_Main].[lstProjects]
[lstProjects] is the List Box control - now unbound. When you click on the Project from this list, it loads the related Task Info from tblProject_Tasks in a sub-form (linking PROJECT_ID from the main form to PT_PROJECT_ID in the sub-form).
I still can't for the life of me figure out how to ADD a new Project via the Main Form.. so I added a button which opens up a separate form (frmNew_Project).
The user enters the Project Name and Description in this form, then clicks "Submit" button. The Submit Button has the following code which inserts that new project into the table. I'm sure there's a better/smarter way to do this though.

Private Sub btnAddProj_Click()


Dim strProjName As String
Dim strProjDesc As String
Dim strSQL As String
Me.txtProjName.SetFocus: strProjName = Me.txtProjName.Value 'Project Name
Me.txtProjDesc.SetFocus: strProjDesc = Me.txtProjDesc.Value 'Project Description




strSQL = "INSERT INTO tblProjects (PROJECT_NAME, PROJECT_DESC)" & _
        "VALUES ('" & strProjName & "', '" & strProjDesc & "')"


DoCmd.RunSQL strSQL


DoCmd.Close acForm, "frmNew_Project", acSaveNo


Forms![frmProject_Management_Main]![lstProjects].Requery


MsgBox "Project Has Been Added"

[Bcanfield83]

Oops, sorry for the duplicate post.