Automatically adding records to a table (1 Viewer)

[cyd00]

Hello everyone,

Is there a way to automatically add a record to a table when a record is added to another table?

For example: I have a table [contacts] and a table [calls].

The [contacts] table contains prospect demographics (name, address, phone number etc.), the primary key is an autonumber.

The [calls] table contains the fields: CallID, ContactID, CallDate, CallTime, Subject, Notes. This table is used to record information every time a prospect is contacted.

The relationship is 1:n. bound by the autonumber field ContactID.

When a new record is added to the [contacts] table I would like a note to be automatically added to the [calls] table. Actually I want a date stamp and the subject line to read "Prospect added", I dont actually need a note in the notes field.

Is there a way to do this?

 

[bradcccs]

Search this formum (and access help) for working with "RecordSets".

If you have some VBA skills, I think you should be able to achieve what you wish to achieve.

Brad.

 

[joebater]

As Brad points out this will be covered in Access VBA books. I'd recommend reading about Data Access Objects which covers how to create records, tables, relationships, even databases at runtime using code.

For your question I'd try something like the following to create a new entry in the Contacts table.

Dim rec as Recordset
Set rec = CurrentDb.OpenRecordset("Contacts", dbopendynaset)
with rec
.addnew
!name = ... (information pulled from a textbox etc)
!phonenumber = ...
!address = ...
.update
end with
set rec = nothing

Then to get the ContactID which is generated as an autonumber by the calls table you're going to have to use "Seek" to get the ID and store this as a local long integer variable.

You can then create entries in the Calls recordset in fundamentally the same way (obviously changing the names of the tables and fields etc), and having "Prospect added" in the subject.

I'd be inclined to put these into subs or functions in isolation and write a 'super' function to do both when a new contact is made detailing both the Contact and the Call.

This all might be very a very complex way of doing it - the tricky point to my understanding is the use of an autonumber in [Contacts] - but I'm fairly new to this also.

Good luck
j.