Creating Records in tables automatically

I have two tables, volunteers and volunteeractions. The user interacts with
them via forms. I want to create a record in volunteer actions whenever a
new record is created by a form based on Volunteers table.

Whenever a volunteer action record is marked as completed I want to
auto,matically create the next action record based on a predetermined
sequence of actions.

How would I do this?

I am a newcomer to Access, but had a number of years experience with Oracle
and would have used commit time triggers for this. Is there a similar
mechanism available in Access?

--
Regards

Peter G.

First, if you are using a access ADP project, then you do in fact have
triggers, and have stored procedures run when you update a table. (ADP
projects have been available for the last 3 versions of ms-access, and with
them you can use triggers.

However, if you are not using a adp project, then just use the forms after
update event, and you can add the child record.


I have two tables, volunteers and volunteeractions. The user interacts with
them via forms. I want to create a record in volunteer actions whenever a
new record is created by a form based on Volunteers table.

I don't think you actually need, or want to create a actions record when you
add a volunteer. I would think that you WAIT until the user actually needs
to enter some actions data. It sounds silly to have action records strewn
all over the place until you actually need to do this. All reports and
queries can still return volunteers and joined actions, and volunteers will
STILL appear in reports even when there is no child records (assuming you
want to them to appear, and assuming you set your queries as left joins.
Note that about 90% or more of your quires will in fact be left join).

I am only pointing the above out, since 9 out of 10 times, people want to
create a child record to solve the join problem, and not the fact that they
need an actual child record. You should only add the child record WHEN data
needs to be entered for the child record (in your case the actions table). I
could also be very wrong in your requirements also, but I just wanted to
point the above out, and try to save you some pain. I guess I am saying that
you don't want to add a actions record until you actually reach the time
where you (your users) are going to enter some information into this actions
record. To add the record before it is needed tends to result in a record
that is incomplete, and often not needed. Further, reports that do counts of
active actions, and other forms/reports that retrieve the "last" action
record are going to be rather messy in this case, since you will as a
general rule have un-competed records all over the place.

Whenever a volunteer action record is marked as completed I want to
auto,matically create the next action record based on a predetermined
sequence of actions.

How, or when do you mark a action record as completed? (perhaps, you just
check a box? Why not add the code to the check box after update event?.
Further, you will have some issues as to this being a new record, or a old
record being edited, and in that case, you do might want to add another
child record, do you? So, you do open up a few issues here to deal with if
you are auto add this record.

And, you might want to note that just allowing the user to navigate on the
sub-form will auto create the record IF the user types something into the
record. As a normal approach, the one to many, and the adding of child
records in ms-access is done with a sub-form, and it is automatic affair.

When you actually start typing in a sub-form record, you will notice that
*instantly* right below the record when using a continues form, that a new
space/blank record appears for you automatically to enter additional
information (but, that reocrd is NOT added untill you actually type into
it).

Is there a similar
mechanism available in Access?

As mentioned, in a ADP project, you do have table triggers.

However, with forms, just use the controls after update event, or use the
forms after update event if you want to operate on a record level. This
essentially gives you the same function as a trigger.

Since mere navigation in a sub-form will automatically create the action
record, you might be conceptually approaching this solution in the wrong
way. When you come form VB to ms-access, then a mind set change has to
occur. And, when you come from FoxPro to ms-access, then again what was
standard fair and was a normal approach in Fox has to be changed. And, if
you come from oracle, then again, a conceptual change has to occur. And, to
be fair, the reverse is also true. In other words, the conceptual designs
and approaches use will change for the given tool you use.

Much of what you seem to be asking occurs rather naturally when you work
with the way that ms-access works.

It is also not clear if you are using a sub-form arrangement here to add
those actions recodes or not. I talk about sub-forms he

http://www.members.shaw.ca/AlbertKal...000000005.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

There is actually a need to create an action when the volunteer record is
created since a number of administrative procedures have to be followed,
including sending out information packs, arranging information sessions etc.

If I understand your reply correctly then the form used to create the
volunteer record will create the first action record using a form event.
(presumably the afterinsert event). The new record in the action table will
include the volunteer Id (created automatically when the record was saved),
the predefined action code, the creation date(todays date). The remaining
fields will be empty. Note that the action will have its own autocreated ID.

How do I get the event to create such a record? Can I use Sql, do I need a
macro?
Please advise me.

Obviously once I have the volunteer form working correctly the same
technique can be used to create the subsequent action records.
--
Regards

Peter G.


"Albert D.Kallal" wrote:

First, if you are using a access ADP project, then you do in fact have
triggers, and have stored procedures run when you update a table. (ADP
projects have been available for the last 3 versions of ms-access, and with
them you can use triggers.

However, if you are not using a adp project, then just use the forms after
update event, and you can add the child record.


I have two tables, volunteers and volunteeractions. The user interacts with
them via forms. I want to create a record in volunteer actions whenever a
new record is created by a form based on Volunteers table.

I don't think you actually need, or want to create a actions record when you
add a volunteer. I would think that you WAIT until the user actually needs
to enter some actions data. It sounds silly to have action records strewn
all over the place until you actually need to do this. All reports and
queries can still return volunteers and joined actions, and volunteers will
STILL appear in reports even when there is no child records (assuming you
want to them to appear, and assuming you set your queries as left joins.
Note that about 90% or more of your quires will in fact be left join).

I am only pointing the above out, since 9 out of 10 times, people want to
create a child record to solve the join problem, and not the fact that they
need an actual child record. You should only add the child record WHEN data
needs to be entered for the child record (in your case the actions table). I
could also be very wrong in your requirements also, but I just wanted to
point the above out, and try to save you some pain. I guess I am saying that
you don't want to add a actions record until you actually reach the time
where you (your users) are going to enter some information into this actions
record. To add the record before it is needed tends to result in a record
that is incomplete, and often not needed. Further, reports that do counts of
active actions, and other forms/reports that retrieve the "last" action
record are going to be rather messy in this case, since you will as a
general rule have un-competed records all over the place.

Whenever a volunteer action record is marked as completed I want to
auto,matically create the next action record based on a predetermined
sequence of actions.

How, or when do you mark a action record as completed? (perhaps, you just
check a box? Why not add the code to the check box after update event?.
Further, you will have some issues as to this being a new record, or a old
record being edited, and in that case, you do might want to add another
child record, do you? So, you do open up a few issues here to deal with if
you are auto add this record.

And, you might want to note that just allowing the user to navigate on the
sub-form will auto create the record IF the user types something into the
record. As a normal approach, the one to many, and the adding of child
records in ms-access is done with a sub-form, and it is automatic affair.

When you actually start typing in a sub-form record, you will notice that
*instantly* right below the record when using a continues form, that a new
space/blank record appears for you automatically to enter additional
information (but, that reocrd is NOT added untill you actually type into
it).

Is there a similar
mechanism available in Access?

As mentioned, in a ADP project, you do have table triggers.

However, with forms, just use the controls after update event, or use the
forms after update event if you want to operate on a record level. This
essentially gives you the same function as a trigger.

Since mere navigation in a sub-form will automatically create the action
record, you might be conceptually approaching this solution in the wrong
way. When you come form VB to ms-access, then a mind set change has to
occur. And, when you come from FoxPro to ms-access, then again what was
standard fair and was a normal approach in Fox has to be changed. And, if
you come from oracle, then again, a conceptual change has to occur. And, to
be fair, the reverse is also true. In other words, the conceptual designs
and approaches use will change for the given tool you use.

Much of what you seem to be asking occurs rather naturally when you work
with the way that ms-access works.

It is also not clear if you are using a sub-form arrangement here to add
those actions recodes or not. I talk about sub-forms he

http://www.members.shaw.ca/AlbertKal...000000005.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

I think have make my point well enough about adding these incomplete
records, and trying to retrieve the 'last' quote, or get counts of records
for a given volunteer is going to be messy, and difficult with this
approach.

Further, even if you had triggers, having it fire when you insert a
volunteer action would requite MUCH caution, since this would cause a
cascade of additions (each event would as its self trigger another insert
based on the fact that you want a insert).

So, while I grduddilgy agree with you adding a new volunteer action record
when you add a new volunteer, adding a new action record for each new action
record is asking for trouble.

My spider sense tells me that this is not really a preferred way to design
this application, and my bets are that this application will problematic..
You are fighting against the natural way that records should be added here.

Ok, lets move on and try some code give the above caveats.

For adding the first child record when you add the main record, using the
on-insert event would do the trick. You can use sql, or use a VBA reocrdset.
Which approach to use is much going to depend on your design, and if you are
in fact using a sub-form. Assuming no sub-form here (since, as I mentioned,
this whole thing is automatic if you use a sub-form, and NO code is needed).
Ok, so, based on this assumption, then lets just use some sql...


Dim strSql As String

strSql = "insert into contactChild (contact_id) " & _
"values (" & Me.ContactID & ")"

CurrentDb.Execute strSql


Note that you can use the same approach for adding action records because
the ON INSERT event does NOT fire when you add the records via code (but, if
you had used a trigger, or if on insert did fire when you add a record via
code...you can quickly see that this would be a runaway addition process).

So, you can use the above approach for your actions table also. (however, it
is not clear how the actual editing process is to work).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

The sequence for creating action records is:
Initial record created when client record is created.

Once the first action record has been completed and updated with completion
date etc the update action will create the second action record in the
sequence. Oncet this action has been completed the third action record will
be created and so on until the end of the sequence.

As a newcomer to Access I am not certain where i place the SQL. Presumably
for the first action record the the AfterInsert event on the main record will
be used.


For the subsequent action records I envisaged using the AfterUpdate event on
the completion date/flag field.

Since the only actions the user will be able to edit are those that are not
completed, once the action has been completed it will not normally be
possible to create another action record from it. Since each action can only
be followed by a particular action,e.g action 4 is always followed by action
8 and action 8 can only follow action 4 then may be it would be possible to
check the existance of the following record so that if it exists another
cannot be created.



My remit is to make sure the application supports the process and to ensure
all steps are followed in the correct sequence.

--
Regards

Peter G.


"Albert D.Kallal" wrote:

I think have make my point well enough about adding these incomplete
records, and trying to retrieve the 'last' quote, or get counts of records
for a given volunteer is going to be messy, and difficult with this
approach.

Further, even if you had triggers, having it fire when you insert a
volunteer action would requite MUCH caution, since this would cause a
cascade of additions (each event would as its self trigger another insert
based on the fact that you want a insert).

So, while I grduddilgy agree with you adding a new volunteer action record
when you add a new volunteer, adding a new action record for each new action
record is asking for trouble.

My spider sense tells me that this is not really a preferred way to design
this application, and my bets are that this application will problematic..
You are fighting against the natural way that records should be added here.

Ok, lets move on and try some code give the above caveats.

For adding the first child record when you add the main record, using the
on-insert event would do the trick. You can use sql, or use a VBA reocrdset.
Which approach to use is much going to depend on your design, and if you are
in fact using a sub-form. Assuming no sub-form here (since, as I mentioned,
this whole thing is automatic if you use a sub-form, and NO code is needed).
Ok, so, based on this assumption, then lets just use some sql...


Dim strSql As String

strSql = "insert into contactChild (contact_id) " & _
"values (" & Me.ContactID & ")"

CurrentDb.Execute strSql


Note that you can use the same approach for adding action records because
the ON INSERT event does NOT fire when you add the records via code (but, if
you had used a trigger, or if on insert did fire when you add a record via
code...you can quickly see that this would be a runaway addition process).

So, you can use the above approach for your actions table also. (however, it
is not clear how the actual editing process is to work).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

As a newcomer to Access I am not certain where i place the SQL. Presumably
for the first action record the the AfterInsert event on the main record
will
be used.

Yes....the above is a good event to use...as it will ONLY fire when the
record is created (and, the autonumber id is available at this point). So,
this code goes in the main form that is bound to your main table.


For the subsequent action records I envisaged using the AfterUpdate event
on
the completion date/flag field.

Yes, the above sounds good. I would probably brew a cup of coffee here, and
consider using the after update event of the form also. (it is a toss up
here, but a user could still edit, change their mind if your code runs in
the after update event. If you run your code in the after update event of
the completion data/flag, your record is not yet written to disk. (you can
force it by going me.refresh in the code if you want). So, either event
would be a good start, but just be aware that the record does not get
written to disk in the fields after update events.

, once the action has been completed it will not normally be
possible to create another action record from it.

Sounds ok, if the code checks a few things...then you will not get the run
away problem I talked about.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Thanks for your help. I can now create a record but the syntax of multiple
fields eludes me. If I have fields a, b, c, d, e, f in my form how do I
string them together in the sql statement?

i.e. I want an sql which is
insert into contactChild (a, b, c, d, e) " & _
"values (" & Me.a, Me.b, Me.c, Me.d, Me.e & ")"

I cannot find any examples os such code so any help greatly appreciated



--
Regards

Peter G.


"Albert D.Kallal" wrote:


As a newcomer to Access I am not certain where i place the SQL. Presumably
for the first action record the the AfterInsert event on the main record
will
be used.

Yes....the above is a good event to use...as it will ONLY fire when the
record is created (and, the autonumber id is available at this point). So,
this code goes in the main form that is bound to your main table.


For the subsequent action records I envisaged using the AfterUpdate event
on
the completion date/flag field.

Yes, the above sounds good. I would probably brew a cup of coffee here, and
consider using the after update event of the form also. (it is a toss up
here, but a user could still edit, change their mind if your code runs in
the after update event. If you run your code in the after update event of
the completion data/flag, your record is not yet written to disk. (you can
force it by going me.refresh in the code if you want). So, either event
would be a good start, but just be aware that the record does not get
written to disk in the fields after update events.

, once the action has been completed it will not normally be
possible to create another action record from it.

Sounds ok, if the code checks a few things...then you will not get the run
away problem I talked about.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

"peterg290935" wrote in message
...

strSql = "insert into contactChild (a, b, c, d, e) " & _
" values (" & Me.a & "," & Me.b & "," & Me.c & "," & Me.d & "," & Me.e &
")"

msgbox strSql

You can remove the msgbox once you get the whole thing working....

In fact, since got quite a few fields, and text fields SHOULD be surrounded
by quotes, then you might need


" values ('" & Me.a & "','" & Me.b & "'," & Me.c & "," ........

In the above example, a, and b are text fields, and thus surrounded by
quotes. Normally, you are supposed to use double quotes ", but single are
much easer from a syntax point of view.

If some of those fields are going to be null, then I think I would use a
recordset, and not use a sql update.

dim rst as dao.RecordSet

set rst = currentdb.OpenReocrdSet("tblContactChild")

rst.AddNew

rst!LastName = me!LastName
rst!Date = date() ' todays date
rst!contactID = lngContactID
etc.
etc.
rst.Update
rst.Close
set rst = nothing

The decision to use a reocrdset in place of sql is a tradeoff. You have to
write a bit more code, but you don't deal with a fairly messy string that
can be hard to make. Further, the reocrdset code will be easier if you are
going deal with nulls.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Thank you very much for your patience and help. As one gets older grasping
new techniques becomes increasingly harder.


--
Regards

Peter G.


"Albert D.Kallal" wrote:

"peterg290935" wrote in message
...

strSql = "insert into contactChild (a, b, c, d, e) " & _
" values (" & Me.a & "," & Me.b & "," & Me.c & "," & Me.d & "," & Me.e &
")"

msgbox strSql

You can remove the msgbox once you get the whole thing working....

In fact, since got quite a few fields, and text fields SHOULD be surrounded
by quotes, then you might need


" values ('" & Me.a & "','" & Me.b & "'," & Me.c & "," ........

In the above example, a, and b are text fields, and thus surrounded by
quotes. Normally, you are supposed to use double quotes ", but single are
much easer from a syntax point of view.

If some of those fields are going to be null, then I think I would use a
recordset, and not use a sql update.

dim rst as dao.RecordSet

set rst = currentdb.OpenReocrdSet("tblContactChild")

rst.AddNew

rst!LastName = me!LastName
rst!Date = date() ' todays date
rst!contactID = lngContactID
etc.
etc.
rst.Update
rst.Close
set rst = nothing

The decision to use a reocrdset in place of sql is a tradeoff. You have to
write a bit more code, but you don't deal with a fairly messy string that
can be hard to make. Further, the reocrdset code will be easier if you are
going deal with nulls.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal