Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005

    Question Unanswered: Automatically add a record to a related table help

    I would like to automatically add a new record to a table when a record in a related table is added.

    I have a form which displays data from a contact details table. When a new contact is added I would like to automatically add an additional new record to a related table which stores the contact type. The contacts table is linked from a central SQL database so for this particular Access database link I would like the group type [GroupTypeID] field to always default to a certain value.

    The Contact type table is linked to the Contacts table through the contactID field. The full field list is:
    [TypeID] – Primary Key, automatic number
    [ContactID] – Foreign key

    ContactID can be grabbed from the contacts form and I can set the GroupTypeID to a default value. However I need a record to actually be added to the ContactType table, not just default values displayed in the form. Ideally the record can be added using an on update or on click event.

    Can anyone help me with the code? I have played about with ‘AddNew’ but with no success.


  2. #2
    Join Date
    Apr 2004
    Kingsland, Georgia
    what about in the onclick event running a SQL INSERT statement to insert a new record in the contacttype table and insert the contactID?

    strSQL = "INSERT INTO tblCType (fldContactID) VALUES (" & txtContactID & "')"
    Currentdb.Execute (strSQL)

  3. #3
    Join Date
    Jul 2005
    Nice one, thanks

    Just posted another question but worked it out I was typing it. The code worked fine with one value but I was having problems picking up a second value from the input form. Managed to get it to work with:

    Dim Myvalue As String
    Myvalue = Forms!frmContactsData!ContactID

    strSQL = "insert into tblConsulteeTypeBridge (ContactID, GroupID) values (" & Myvalue & "," & 41 & ")"
    CurrentDb.Execute (strSQL)

    Thanks very much for your pointers, I can go home happy now.
    Last edited by Trigpoint; 06-15-06 at 12:12.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts