How to Populate Subform Automatically
in [Help]
|
From: sharsy on 27 Jul 2010 03:00 I have an Access 2003 database that is used to record details of all of our members. Table 1 [Members] is populated via a form. We also have another Table [CRM Database] to record various scheduled calls we make to our members. The CRM Database appears as a subform on the main form. Each call made to a member has a specific 'reason' (a field which is a drop-down option on the subform). Every member has a unique ID number, which has a one-to-many relationship with the various calls that can be logged on the CRM database. Whenever a new member joins, we are required to setup 4 'blank' calls for them - each call is for a different 'reason': 30 Day, 6 Month, Renewal and 18 Month. What I would like to be able to do is setup a macro so that when a new member is added and their member ID number is entered, that 4 'blank' calls are automatically created for them on the subform/CRM Database. The 4 calls should be populated with 30 Day, 6 Month, Renewal and 18 Month in the reason fields. Does anyone know how I could do this? I'm not the brightest with regards to VBA, but have been able to get by so far :) Thank you very muchly :)
From: Martin on 27 Jul 2010 19:13 On Jul 27, 5:00 pm, sharsy <sharibrough...(a)gmail.com> wrote: > I have an Access 2003 database that is used to record details of all > of our members. Table 1 [Members] is populated via a form. We also > have another Table [CRM Database] to record various scheduled calls we > make to our members. The CRM Database appears as a subform on the > main form. Each call made to a member has a specific 'reason' (a field > which is a drop-down option on the subform). Every member has a unique > ID number, which has a one-to-many relationship with the various calls > that can be logged on the CRM database. > > Whenever a new member joins, we are required to setup 4 'blank' calls > for them - each call is for a different 'reason': 30 Day, 6 Month, > Renewal and 18 Month. > > What I would like to be able to do is setup a macro so that when a new > member is added and their member ID number is entered, that 4 'blank' > calls are automatically created for them on the subform/CRM Database. > The 4 calls should be populated with 30 Day, 6 Month, Renewal and 18 > Month in the reason fields. Does anyone know how I could do this? I'm > not the brightest with regards to VBA, but have been able to get by so > far :) > > Thank you very muchly :) If you look at the "After Insert" event for the main form, you could tell it to INSERT INTO [CRM Database] the four required rows. Without the structure of the tables I cannot supply code - but it is not too tricky. On a related note - how 'married' to your table names are you? [CRM database] is a terrible name for a table! (IMHO). Not only does it have a space in it, but it is misleading. I would be argueing for 'CRM' or "tblCRM" (but its not my db!) regards, Martin
From: sharsy on 27 Jul 2010 21:56 Hello Martin, I am indifferent about my table names - though I can see from a VBA point of view they are somewhat lacking, so I have taken your advise and renamed my tables and forms - the structure of my setup is now as follows: tblMembers = Parent table tblCRM = Child table frmMembers = Parent form frmCRM = Child subform The [MemberID] field is the the linked field between them. A member can have many calls on the CRMDatabase, with the calls on the CRMDatabase being allocated to the unique MemberID on the Members table. Will this help? Shari :)
From: Martin on 28 Jul 2010 20:01 On Jul 28, 11:56 am, sharsy <sharibrough...(a)gmail.com> wrote: > Hello Martin, > > I am indifferent about my table names - though I can see from a VBA > point of view they are somewhat lacking, so I have taken your advise > and renamed my tables and forms - the structure of my setup is now as > follows: > > tblMembers = Parent table > tblCRM = Child table > > frmMembers = Parent form > frmCRM = Child subform > > The [MemberID] field is the the linked field between them. A member > can have many calls on the CRMDatabase, with the calls on the > CRMDatabase being allocated to the unique MemberID on the Members > table. > > Will this help? > > Shari :) Shari, Assuming that your tables have autonumber ids and no other required fields, and that the "Reason" field you describe is called "Reason" and is a text field (which is a risky assumption :), add the following code to the parent form (frmMembers) (and let me know how it goes :): Private Sub Form_AfterInsert() Dim strSQL As String DoCmd.SetWarnings False strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & tboxMemberID & ", '30 Day')" DoCmd.RunSQL strSQL strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & tboxMemberID & ", '6 Month')" DoCmd.RunSQL strSQL strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & tboxMemberID & ", 'Renewal')" DoCmd.RunSQL strSQL strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & tboxMemberID & ", '18 Month')" DoCmd.RunSQL strSQL DoCmd.SetWarnings True Me.Requery End Sub
From: Martin on 29 Jul 2010 18:13 On Jul 29, 10:01 am, Martin <MHungerf...(a)groupwise.swin.edu.au> wrote: > On Jul 28, 11:56 am, sharsy <sharibrough...(a)gmail.com> wrote: > > > > > > > Hello Martin, > > > I am indifferent about my table names - though I can see from a VBA > > point of view they are somewhat lacking, so I have taken your advise > > and renamed my tables and forms - the structure of my setup is now as > > follows: > > > tblMembers = Parent table > > tblCRM = Child table > > > frmMembers = Parent form > > frmCRM = Child subform > > > The [MemberID] field is the the linked field between them. A member > > can have many calls on the CRMDatabase, with the calls on the > > CRMDatabase being allocated to the unique MemberID on the Members > > table. > > > Will this help? > > > Shari :) > > Shari, > Assuming that your tables have autonumber ids and no other > required fields, and that the "Reason" field you describe is called > "Reason" and is a text field (which is a risky assumption :), add the > following code to the parent form (frmMembers) (and let me know how it > goes :): > > Private Sub Form_AfterInsert() > Dim strSQL As String > > DoCmd.SetWarnings False > strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & > tboxMemberID & ", '30 Day')" > DoCmd.RunSQL strSQL > strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & > tboxMemberID & ", '6 Month')" > DoCmd.RunSQL strSQL > strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & > tboxMemberID & ", 'Renewal')" > DoCmd.RunSQL strSQL > strSQL = "INSERT INTO tblCRM(MemberID, Reason) VALUES(" & > tboxMemberID & ", '18 Month')" > DoCmd.RunSQL strSQL > DoCmd.SetWarnings True > > Me.Requery > End Sub- Hide quoted text - > > - Show quoted text - d'Oh! I should have mentioned that tboxMemberID is a bound form on the parent form, bound to MemberID. Martin
|
Next
|
Last
Pages: 1 2 Prev: Can AlbertCo's tables be accessed? Next: Keeping A Citrix Session Alive? |