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

On Jul 27, 5:00*pm, sharsy <sharibrough... (AT) gmail (DOT) com> wrote:
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

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

On Jul 28, 11:56*am, sharsy <sharibrough... (AT) gmail (DOT) com> wrote:
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

On Jul 29, 10:01*am, Martin <MHungerf... (AT) groupwise (DOT) swin.edu.au> wrote:
d'Oh! I should have mentioned that tboxMemberID is a bound form on the
parent form, bound to MemberID.

Martin

Hey Martin,

The [MemberID] field is not an autonumber - it is a number field with
no duplicates allowed. The [Reason] field is a text field, but it is a
drop-down with options retrieved from another table called
tblCRMReasons. MemberID is bound control to the table too.

I've added the code to the frmMembers object in the VBA Editor,
however I'm not sure if I've done it correctly as it has come up with
a "Run-time error 3134, Syntax error in INSERT INTO statement" , the
debugger highlights this part "DoCmd.RunSQL strSQL" after the 30 Day
insert bit (so the 1st one).

What do you reckon? Shari

Hi Shari,
Not to "nit-pick" ... but I'd recommend naming subforms with an "sbf" prefix
rather than a "frm". ;-)
As you get deeper into writing VBA code, you'll appreciate the use of
naming conventions to help make things clear.

Now, as far as a Main form / subform approach goes ... if you are using
"MemberID" as both the LinkMaster and LinkChild, and have Referential
Integrity set up at the table relationships level , you should be able to
select or enter the MemberID in the main form, and then enter the related
data directly into the subform.
Access will automatically fill in the MemberID into the subform as soon as
you type something into one of your other fields.

HTH,
Don

"sharsy" <sharibroughton (AT) gmail (DOT) com> wrote