Add Several Records to Table When New Project Added 3

[mkallover]

What I would like to do is add several records to a milestones table (tblMilestones) whenever a user creates a new project. There are milestones like beginning design, going to coding, entering QC that are consistent for every project. What I would like to do is create those records on the fly whenever a user creates a new IT project in the database.

What would be the best way to do that? Would I just have to create several INSERT queries in my VB code or is there a better way?

 

[Remou]

You could maintain small milestones table and append that.

 

[mkallover]

Yes, that's what I have in the tblMilestones. Milestones are then linked to the project by the ProjectID. Every project has the same set of milestones so I'd like a way for the business owners to get those in quickly.

So I take it that using several INSERT queries triggered by VBA is probably the best way to do that?

 

[fneily]

You could create Insert queries. Another way is one I used to update tables in a school database. When a student enrolled, his info had to go into 7 different tables, so (I only show two tables for example)

Dim db As Dao.Database
Dim rs As Dao.Recordset
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Attendance", dbOpenDynaset)
rs.AddNew
rs![ID] = Me![ID]
rs![FirstName] = Me![FirstName]
rs![LastName] = Me![LastName]
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Set db = CurrentDb()
Set rs = db.OpenRecordset("Bus Information", dbOpenDynaset)
rs.AddNew
rs![ID] = Me![ID]
rs![FirstName] = Me![FirstName]
rs![LastName] = Me![LastName]
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Place it on the OnClick event of a Save button.

 

[PHV]

fneily, you really had 2 tables (Attendance,Bus Information) with 3 very same fields (ID,FirstName,LastName) ?
Please, have a look here:

http://r937.com/relational.html

 

[fneily]

Godfrey Daniels! Someone caught that. A teacher created the tables and I pointed that out to her. Normalization violation and all that. She just stared blankly at me.
Didn't think my own medicine tasted so bad...
Good eyes, PHV. Don't you have holiday shopping to do?

 

[Remou]

I failed to explain myself.

tblAppendMilestones
Milestone1
Milestone2
Milestone3

INSERT INTO tblMilestones (ProjectID, Milestone)
SELECT Forms!frmForm!ProjectID, Milestone FROM tblAppenMilestones

 

[mkallover]

Thanks all, great stuff. I'll tinker around with these techniques.