Creating a Sub-Form to record Sub-Projects

  • Thread starter Thread starter jammatmil
  • Start date Start date
J

jammatmil

I have a form that I use to track projects. Each project has a four digit
number (####). Now, some projects have sub-projects under them which in turn
have a sub-project number (####.##).

I would like to be able to have the form show the main project number and
the sub-projects on the same form with the ability to add subprojects without
leaving the form.

I hope this makes sense because I don't know how better to explain it.
 
Got to know something about the tables tied to the forms. Do you have
projects in one table and subprojects in another? Or do you have bothe in
the same table?
What are the table and field names and the datatypes?
Post some sample data.
 
That's a great question. Right now there is only one table, and I'd like to
keep it that way if possible.

Heres an example:
 
Use a form and subform - Project/Subprojects.
Query for form --
SELECT YourTable.*
FROM YourTable
WHERE Len([Project Number]) = 4;

Query for form --
SELECT YourTable.*, Left([Project Number], 4) AS ProjNum, Right([Project
Number], 2) AS SubProj
FROM YourTable
WHERE Len([Project Number]) > 4;

In the main form set the Master/Child links on [Project Number] to SubProj
of the subform.

Include [Project Number] in the subform but set visible property to No.

In the Subform field SubProj have AfterUpdate call macro that SetValue of
[Forms]![YourFormName]![SubFormName]![Project Number] =
[Forms]![YourFormName]![Project Number] & "." &
[Forms]![YourFormName]![SubFormName]![SubProj]

To add a subproject type in the 2 digits and press ENTER. The macro will
concatenate the Project Number from the main form with a period and the 2
digits to create the complete Project Number with subproject.

--
Build a little, test a little.


jammatmil said:
That's a great question. Right now there is only one table, and I'd like to
keep it that way if possible.

Heres an example:
----------------------
Project Number: 0004
Project Title: Training Ranges
[then there would be more info]
----------------------
0004.01 Rifle Range
0004.02 Pistol Range
----------------------

It's hard to explain how I want this to work and may be too complex for this
forum. I think I can get away with just adding a few fields to the bottom of
the form but I would like it to be a little more dynamic.

KARL DEWEY said:
Got to know something about the tables tied to the forms. Do you have
projects in one table and subprojects in another? Or do you have bothe in
the same table?
What are the table and field names and the datatypes?
Post some sample data.
Click to expand...
Click to expand...
 
Back
Top