Populate table/subform from VBA

[degras]

I have a form for workers and it contains a subform for scores representing a row from a workers skills matrix. These scores are stored in a separate table, one record per skill per worker. When a new worker record is created I would like to populate the scores table with default skills (with a zero score) for that worker - so that I don't have to manually create the same 50 or so skills for each worker. How from VBA can I add rows to the subform and set the skill field for each row?

[pbaldy]

I wouldn't think about populating the subform, I'd think about populating the underlying table. You could use an append query that got the worker value from the form and the skills from their table:

INSERT INTO TableName(Worker, Skill, Score)
SELECT Forms!FormName.Worker, Skill, 0
FROM SkillsTable

Then requery the subform and they should show up.

[degras]

I wouldn't think about populating the subform, I'd think about populating the underlying table. You could use an append query that got the worker value from the form and the skills from their table:

INSERT INTO TableName(Worker, Skill, Score)
SELECT Forms!FormName.Worker, Skill, 0
FROM SkillsTable

Then requery the subform and they should show up.

Wow I never knew you could put a SELECT statement in a INSERT statement.

Thanks.

[pbaldy]

Happy to help. I typically use the VALUES clause to insert a single value, the SELECT clause for multiple values like this.