Subform datasource

[JimS]

I have a table that needs to be populated with a set of "tasks". Each row in
the table will have the task name and the "goal" hours for that task. I've
created another table with standard sets of tasks from which the user may
choose, and add his or her own additional tasks.

I thought I might set up a subform to allow the user to do it all at once.
It hinges on being able to set the subform's data source to a subset of the
standard task table, a nd allow the user to fill in the "goal" hours and add
additional tasks.

Can I create a form to get the basic input from the user, then set a
subform's data source to (preferably ado, but I can live with dao) a
recordset constructed by the main form? This would allow me to have a column
for "goal", and to allow addition of new "tasks". Then, when the user is
done, the main form could process the recordset out to its final resting
place.

Does this make sense? Better ideas?

 

[KARL DEWEY]

You need a table Projects with a one-to-many relationship to table ProjTask
having hours field.
Then in the form/subform set the Master/Child link on the Project primary
key and ProjTask foreign key.
Use a combo or list box to select task for the project.

 

[JimS]

Yeah, I knew all that, but the combo box doesn't let me allow the user to
enter the hours in one step. It requires them to select the task, then go
down to the subform and enter the hours. I have all the tables in place
already, structured as you said. I think when the user wants to create a new
set of tasks, I'll put all of 'em in the table, then when the user is done
putting in hours, I'll delete the rows with no hours. That should work fairly
easily.

 

[KARL DEWEY]

It requires them to select the task, then go down to the subform and enter
the hours.
Task needs to be selected in the subform.It does not sound like you have Project table and ProjTask tables set up.

 

[JimS]

Tough to be clear in this type of forum. It's an existing app, 20 or 30
tables. I've set up a new table (WOTasks):
....TaskID (autonumber)
....WorkorderID (refers to the workorder table, and from there to the
Project, Equipment, and Discipline)
....WOTaskName (Name of the task to be accomplished -- string)
....WOTaskGoal (Targetted number of Hours for this task)

Then, there's the WOTaskHrs table, which carries only the date and number of
hours worked on that date:

WOTaskHrsID (Primary Key)
WOTaskID (Refers back to the parent above)
WOTaskDate (Date on which the hours were worked)
WOTaskHrs (Number of Hours worked on that date_)

---

I plan to set up another table which identifies, for a given discipline, the
"standard" tasks. The user decides which "standard" tasks to use, and may add
additional tasks that suite the particular equipment...e.g. most equipment
won't need to have a scrubber installed, but some few will.

For a new Workorder target, I'd ask the user to identify the workorder
number (from the workorder table, already in existance). Then, I'd present
the user with a choice of tasks to add, and for each, a target number of
hours. Those data go in the WOTasks table. The other table is for entering
actuals, and I don't have any problem with that table or its forms.

In my last comment, I said I'd just insert the entire "standard" set of
tasks into the WOTasks table, identified by that work order, then present
them for entry of "goal" hours. When the user goes to save the data, I'll
simply delete any entry that carries a zero or null in the "goal" hours.
It'll be transparent to the user.

In the latest scenario, I don't even need a subform.

What I wanted to do was use an in-memory recordset to present exactly the
same structure, but only insert those rows that were populated with a goal. I
think a recordset must be associated with an external data source, though, so
I'm likely barking up the wrong tree.

Thanks for listening.