If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
"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 |
#9
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Attn Sprinks- Not duplicate insert records | babs | Using Forms | 1 | December 13th, 2004 06:25 PM |
Creating drop-down options from fields in other tables? | Adrian cornwall | Running & Setting Up Queries | 1 | September 7th, 2004 08:07 PM |
New records can't be seen | rleblanc | Using Forms | 6 | August 14th, 2004 02:43 PM |
Generating unique records from tables | Peter Jay Salzman | Running & Setting Up Queries | 1 | July 7th, 2004 12:50 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |