auto create records

[lockstock]

hi,

this is a part of my db:

tblTask
*TaskID (primary key)
Task


....

tblEvaluation
*Tasks (primary key)
**Pupil (primary key)
Evaluation
...

tblPupil
**PupilID (primary key)
Name
...

When a record in tblTask is created, i want to create automatically a record in tblEvaluation for each pupil (evaluation left blank)
Has anyone an idea how this can be done? Can this be done through use of macros or will it need VBA?

Thanks!

[June7]

An INSERT SELECT action is one method.

A macro would have to run a query object. VBA could run the SQL statement, like:

CurrentDb.Execute "INSERT INTO tblEvaluation (Tasks, Pupil) SELECT " & Me.tbxTask & " AS TaskID, PupilID FROM tblPupil"

Do you need to restrict which students are captured?

Other methods involve VBA and recordset objects and looping code structure.

[lockstock]

thanks,
no just every pupil in the table should do the trick

i will try to insert the statement somewhere but i'm kind of a newbie if it comes to programming

[June7]

Yes, the real trick is figuring out what event(s) to put code into.

Perhaps the AfterUpdate event of the task control - (is it a combobox?) or a button Click.

[lockstock]

the task records are created in a datasheet (sub)form

[June7]

What do you mean by 'task' records - records in tblEvaluation? What is this form/subform arrangement? I envisioned main form bound to tblTask and subform bound to tblEvaluation. Create task record on main form and code will create associated records for tblEvaluation. Refresh/requery the subform to view new records.

[lockstock]

maybe this pic will clarify.
I create the taskIDs in a subform of tblTaskGroups
every time a TaskID is created i want a record in the tblEvaluation for every PupilID

[June7]

You mean main form bound to tblTaskgoups and subform bound to tblTask? Okay, then code behind the tblTasks subform. You just don't need to do requery/refresh because the tblEvaluation records are not displayed.

[lockstock]

i entered
CurrentDb.Execute "INSERT INTO tblEvaluation (Tasks, Pupil) SELECT" & Me.tblTask & "AS TaskID, PupilID FROM tblPupil"
in the afterupdate of the subform but i get an error '.tblTask' (from Me.tblTask) is not found?

[June7]

My example is Me.tbxTask. This is an example of referencing a form's textbox control.

Also, don't drop the spaces I show in the example SQL statement - one is after SELECT and another before AS - within the quote marks.

[lockstock]

so i named the textbox where i type the tasks 'tbxTask' (is that correct?)
and put this line in the afterupdate of that textbox:
CurrentDb.Execute "INSERT INTO tblEvaluation (Tasks, Pupil) SELECT " & Me.tbxTask & " AS TaskID, PupilID FROM tblPupil"
now i get:

[June7]

You might have to first commit the task record to table. Record is committed when move to another record, close form, or run code.

One method:

DoCmd.RunCommand acCmdSaveRecord

But tbxTask should be bound to TaskID field. Is this an autonumber PK field? If it is, user should not be able to input a value and therefore the AfterUpdate event should not trigger.

If you want to provide db for analysis, follow instructions at bottom of my post.

[lockstock]

no its not a autonumber pk field

the form i'm referring to is the NieuweEvaluatie form

auto test - Copy.zip

oh sorry TaskID is indeed auto PK, i bound tbxTask to 'Task' not 'TaskID'

[June7]

The only field in the subform is Task which is a descriptive text field. You need the TaskID field for the INSERT action. TaskID is an autonumber field and record must be committed before the TaskID is available and before records can be committed to tblEvaluation (for relational data integrity).

Private Sub tbxTask_AfterUpdate()
CurrentDb.Execute "INSERT INTO tblEvaluation (Tasks, Pupil) SELECT " & Me!TaskID & " AS TaskID, PupilID FROM tblPupil"
End Sub

I am not sure textbox AfterUpdate will work because focus shifts immediately to a new record since there is nowhere else to go as there is only the one control on the form.

What is nieuw in NieuweEvaluatie load event? Why does existing record not show on main form?

You have lookups with alias built in tables - I NEVER do this. I prefer to see actual values in fields when viewing tables.

[lockstock]

i added a taskID field to the subform, named it tbxTask and placed the code in it.
now when i create a record in tblTasks I don't get any errors but nothing happend either.

'nieuw' was a macro, I renamed it MacroNieuw

auto test.zip