Copy specified record and related subs - add in combo box choice (1 Viewer)

Marlene

Registered User.
Local time
Tomorrow, 06:49
Joined
Aug 15, 2013
Messages
33
I have a list of schools for which we manage property projects. Each project has the same default set of related tasks. I want to be able to start a new project for a school (chosen from a combo box) and have set of tasks attached to it.
  • tblSchool Primary Key= EID
  • tblProject Primary Key = ProjectID. Contains also EID to relate to school
  • tblTasks Primary Key = TaskID. Contains ProjectID to relate back to project. There are 29 default tasks for any one project.
I have set up a base project (projectid = 1) with the related tasks, so I think my process will be:
  • Choose which school (EID) from a combo box (I have made a form with a combo box in it called ComboSchool)
  • Create the new projectid and insert EID from the combo box into the project EID to relate them
  • Copy the tasks attached to the base project (projectid = 1) and relate them to the new project
I have tried Allen Brownes code but am stuck on putting the value from a dialog combo into the appropriate field in the new project. There are (at the moment) 13 schools. I think I should be putting a select Case in there somewhere but cannot quite work it out. Any help greatfully received.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:49
Joined
Aug 30, 2003
Messages
36,118
This value would be in the second section, the subform records? You'd concatenate the value into the string, like the order ID is in his example. Might help to see your attempt and how it's failing.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:49
Joined
May 7, 2009
Messages
19,169
maybe something like this.
 

Attachments

  • SchoolProject.zip
    57.8 KB · Views: 163

Marlene

Registered User.
Local time
Tomorrow, 06:49
Joined
Aug 15, 2013
Messages
33
Yes - thank you for the example. Thats further towards my goal than I was able to do! I have attached my db for your information. in Tasks - the first 29 tasks have a ProjectID of 1 and I have worked out how to select those with a query. What I then need to do is, after the creation of a new project, to append those selcted 29 records to the task table AND for each record, set the projectID to be the same as the newly created Project to link them together. This is because each task then has to be monitored etc - my draft working projects from also below, to help clarify my explanations. I thought that my process would be:
Create a new project and fill in basic details on the Project Info Form.
Append the list of base tasks - selecting tasks where projectid=1, copy them, and append to the same table, making projectid = the new projectid - perhaps taken from the ProjectInfo Form. The particular part I am stuck on is how to insert the new projectID into the projectID in the tasks table for the new appended records.
 

Attachments

  • SchoolsProjects.accdb
    1.3 MB · Views: 151

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:49
Joined
May 7, 2009
Messages
19,169
I added ProjectID to [Projects Add] form (this is the best place to insert the task).
I create a TaskList (master list of all tasks).
view the AfterUpdate event of this form.
 

Attachments

  • SchoolsProjects.zip
    127.6 KB · Views: 160

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 19, 2002
Messages
42,971
Do you only ever do a single type of project? I would think different project types would require different task lists. If that is the case, you would need a new Project Type table and a child table that holds the tasks for that project type rather than using the "base" entity.

Other problems
1. table level lookups. These cause way more problems than they solve. Lookups that are value lists are not terrible but lookups to other tables are big problems once you try writing code or queries.
2. You have non-conforming names. All object names - tables, columns, queries, forms, reports should be comprised of ONLY letters (A-Z and a-z), Numbers (0-9) and the underscore (_). Never use embedded spaces or special characters.
 

Marlene

Registered User.
Local time
Tomorrow, 06:49
Joined
Aug 15, 2013
Messages
33
Hi Pat - at the moment they are single types of projects - a user might choose to one or two additional tasks but the base list stands for all projects. The current issue is many projects at different stages and keeping track. I will change the names to conform so as not to give myself further headaches later. Thank you for your guidance.
 
Last edited:

Marlene

Registered User.
Local time
Tomorrow, 06:49
Joined
Aug 15, 2013
Messages
33
I added ProjectID to [Projects Add] form (this is the best place to insert the task).
I create a TaskList (master list of all tasks).
view the AfterUpdate event of this form.
Thank you very much indeed arnelgp - that has helped me enormously and just what I need. Pleased to se the VBA and learn a bit more about it. Very much appreciate the time you have put into this on my behalf.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 19, 2002
Messages
42,971
Reusing "assigned" tasks from a dummy entity is a poor design tactic. It would be much better to create a proper model of three tables to hold the description of the project types and tasks. project type, tasks, and Standard Tasks For Project tables.
 

Users who are viewing this thread

Top Bottom