2 Beginner questions

[Willump]

I have a simple database with 2 tables, students and progress. I need to set up my database so that when I create a new record for a student (using a form I've created) it automatically creates 4 new entries in the progress table using the ID I have generated in the form and a task number (1-4) for each of these entries.

Additionally, once all tasks are set to complete = true, I need to set the field "all tasks complete" to true. I'd like to do this all without vba if possible, Thanks.

[June7]

Without VBA? I would find VBA easier.

Only way I can think of is to have a table that contains 4 records as a template for creating records in the progress table. Then use an INSERT SELECT action query. Build the sql statement in SQL View of query designer. Would look like:

INSERT INTO Progress SELECT [Forms]![formname]![ID] As ID, Template.* FROM Template;


Suggest the 'all tasks complete' field is unnecessary. This information can be determined by querying the Complete field. If any task is Complete = False then all tasks are not completed.

[ssanfu]

So you have three options:
1) do everything manually
2) use macros (scripting)
3) use VBA (programming)

#1 is out - it is labor intensive - the computer can do it faster
Sounds like #3 is out - you don't want programming
That leaves #2 - Macros. I don't use macros. But the logic is the same.

The macro would have to fire when a new record is created in the student table.

The steps would be:
- insert a new record into the progress table
- get the (new) student Id and update the record just added in the progress table
- update the record with a task number
- increment the task number variable

Then do that 3 more times.

[Willump]

Thanks for you help, but how do you add a record using a macro? I can only see the options for Save and Delete record.

[June7]

The macro would execute saved query object. I also don't use macros and don't know if can have looping code. The table and query structure I suggested would not require looping. All 4 records would be created in one action.

[ssanfu]

I think that June's method might be better.

but how do you add a record using a macro

The command is "GoToRecord"

Then, at the dialog box at the bottom,
Object Type is "Table"
Object Name is "Progress"
record is NEW

(That is way more than I wan to know about macros... )

[June7]

That will take user to a new record in table and then user can manually enter values into fields.

Automating creation of 4 new records means the macro would open/run INSERT query.

[ssanfu]

June,
It was just an idea (since VBA wasn't involved).
Without looping, there would have to be 4 "set" of commands - one "set" for each of the records. After each insert, maybe the "set value" could set the value of the field to 1, 2, 3, 4. Another "set value" command to add the PK.....??

To me, very difficult using macros.

I like your query solution better than the macro idea.

[June7]

My suggestion combines query and code (VBA or macro). Build query and then code in some event (AfterUpdate or Click) executes the query when needed. My preference would be a VBA procedure executing an sql action, not running a saved Access query object.

The real trick is making sure the records aren't inserted multiple times.

[ssanfu]

...................................