Autopopulate (append query) new table with subform (1 Viewer)

[tcraton]

I have a project development task database, which includes a top-level project number/description table, then several other tables for tasks, deliverables, notes, etc. These are all represented by sub-forms. The list of tasks (approximately 80) are pre-defined and categorized (ie: Phase 00, Concept Approval, ....), but can have additional information added to them on a per-project basis. I currently has the "template" task list in a separate table.

Here's my question:

How do I pull the template task list to auto-populate the working task list table AND establish a new relationship with the top-level project ID? (The tasks, again, are technically entered in a project subform.)

Any help would be greatly appreciated.
Thanks.

 

[Pat Hartman]

Use an append query.
1. Add the template table to the QBE grid and select all the columns you need.
2. Change the query type to Append and choose the WorkingTask table. Assuming that the columns have identical names, Access will populate the "append to" cell. If it doesn't, you can do it manually.
3. Add the projectID column to the grid. Select it in the "append to" combo and type it in the "from" cell as:
NewId:12345
You can replace the hard-coded number with a variable prompt or a form field reference.

 

[tcraton]

Thanks! It worked. One question - I have set up a command button on my form to automate this process. However, when I run the macro (the append query) the dialog box with the message "... do you want to paste XX records" prompts for an answer. Is there any way I can automate the answer so my end users do not get confused?

Thanks so much for the help!

 

[Agism]

I believe you will have to run your query from a macro. Begin the macro with "SetWarnings". When this is set to no, those messages will not show up.

You need to be careful though, because it does the same thing as if you just hit enter and not yes or no. I suggest you run your query and use only the enter key to be sure that it will run properly when the warnings are set to off. I think append queries are fine with this, but make table queries are not.

 

[tcraton]

It worked! Thanks so much for the help. I love this website.