Adding a list of tasks (at once) in a form

[mooseisloose]

Hello,



I'm intermediate with ms access and currently have a database to organize all the tasks we do per every project we have. Now there is many projects in the database and i have noticed that most of the tasks for every project are the same. so now when i open the form that lets me first pick the project number. When i do so, it shows me the task list for that particular project, lets me add/edit/delete the tasks. Now i want to create a button in that form that lets me automatically add a particular list of tasks all at once (about 30 or so) FOR THAT PARTICULAR PROJECT. I'm sure there is a way to do this.

I'm basically looking for someone to show me the code of how to add at least TWO tasks thru visual basic using a BUTTON and also let me know if it's the same for adding 3+ tasks. thank you so much in advance and remember i'm trying to add the task list JUST FOR THAT PICKED PROJECT =)

Moose

[jzwp11]

Welcome to the forum!

I assume that you have a table that holds a list of all possible tasks, some of those are your 30 common tasks. I'll assume you have a query that can pull those 30 common tasks. What you basically need to do is to create a recordset that has the tasks and loop through them. Everytime through that loop you would run an append query that appends a new record to the table that holds the tasks for a project (in the example below the table is tblProjectTasks). You would have to pull the project's primary key value from the form where the button is located. The code would look something like this (air code, not tested).

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
 
'set up the recordset for the tasks
Dim myRSTask As New ADODB.Recordset
myRSTask.ActiveConnection = cnn1
 
'set up the record set for the project-task table
Dim myRSProjTask as New ADODB.Recordset
myRSProjTask.ActiveConnection=cnn1
 
'open the project-task recordset
 
myRSProjTask.Open "tblProjectTasks", , adOpenDynamic, adLockOptimistic
 
 
'open the task record set
myRSTask.Open "qryCommonTasks", , adOpenDynamic, adLockOptimistic
 
Do until myRSTask.EOF
   With myRSProjTask
      .AddNew
      !fkProjectID=me.projectID  'pulls project key from form where the button is located
      !fkTaskID= myRSTask!taskID  'pull current task from task recordset
      .Update
    End With
myRSTask.MoveNext
Loop
 
myRSTask.close
myRSProjTask.close
 
Set myRSTask=Nothing
Set myRSProjTask=Nothing

I'm not sure what your table structure looks like, but I'm guessing something like this


tblProjects
-pkProjectID primary key, autonumber
-txtProjectName
-fkClientID foreign key to tblClients

tblTasks
-pkTaskID primary key, autonumber
-txtTaskName
-logCommonTask (yes/no field if yes, it is a common task)

tblProjectTasks
-pkProjTaskID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkTaskID foreign key to tblTasks

[mooseisloose]

thank you and sorry i'm not very good with vb code, usually just copy/past code that i need...so few questions. Why do i need to loop for? just wondering, also here is more info about the database so hopefully you can put it in easier way for me to understand what to do.

I have a table that has list of tasks
tbltasklist // this is the table with task list, * are the fields in that table
*engineer
*task
*status
*duedate
Tblmain // table that will save the job number with all their tasks
*Project ID //primary key
*JobNumber // will be many of job numbers depending on amount of tasks
*engineer
*task
*status
*duedate

So now basically i have a form called "frmADDTasks", with subform "subfrmaddtasks"

When i open up "frmaddtasks", i have a combo box to select first by "engineer, than by "JobNumber", after that i get a list of tasks in the "subformaddtasks" that can be edited, deleted or add new tasks.

what i want to do is basically add a button on the "frmaddtasks" that when clicked, will add the entire list of data that is in "tbltasklist" to that "jobnumber" and "engineer". Remember "projectID" is primary key but there will be a new "projectID" number for every task added. Hope that makes sense, thank you for your help. I will try and use ur code as you suggested, please see if you can make it easier for me to understand thru my explanation

[mooseisloose]

sorry "tblTaskList" only contains one field *task

[jzwp11]

Your table structure does not appear to be correct/normalized. Please see this site for more on normalization.

I need to understand more about your application in order to help correct the table structure.

Does a project consist of multiple jobs?

Does a job consist of multiple tasks?

How are the engineers assigned--to the tasks, the jobs or the project or a combination thereof?

[mooseisloose]

no one project is basically one jobnumber and yes a jobnumber consists of multiple task, i'm going to attach the database to the reply, let me know if you can understand it better...basically when you open the database, click on "ADD/EDIT TASKS" select Chris Taylor, and than select "27258" (since that has tasks in it, now you will basically see the list of tasks assigned for the jobnumber. What i want to do is add a button in that "frmaddtasks" that allows me to add the list of tasks that are in "tbltasklist" to THAT particular "jobnumber". Hope this helps =) thanx again for your time and help.

[jzwp11]

With a few minor modifications the code will work. I did forget a couple of lines of code. I've added the code to the button on your form; the modified DB is attached.

[mooseisloose]

wow it didn't seem that you forgot anything, it works perfect!!! all i did is add a me.refresh at the end of the code!! thank you sooo much! i really appreciate it =)

[jzwp11]

You're welcome. Good luck with your project.