Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    27

    Unanswered: Automatically add record when another table has new record

    Hope this makes sense:

    When I add a new record in table X, is there a way I can automatically add a new record in table Y where one of the fields (foreign key) uses the autonumber(primary key) assigned to the new record in table X.

    Cheers Dan

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Use DAO or ADO in VB code to do this

    S-

  3. #3
    Join Date
    Oct 2003
    Posts
    27
    Thats what I am doing, its just annoying when I'm trying to add dummy data direct to the database for testing purposes, guess i'll just have to live with it

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Automatically add record when another table has new record

    Originally posted by danthomas
    Hope this makes sense:

    When I add a new record in table X, is there a way I can automatically add a new record in table Y where one of the fields (foreign key) uses the autonumber(primary key) assigned to the new record in table X.

    Cheers Dan
    It's not dificult to do. If you use code and a recordset based on table Y.

    Dim lngTableXValue as long, db as Database, rs as Recordet

    lngTableXValue = Me.TableXKey (use what you are calling this field in your form)

    Set db = CurrentDb
    Set rs = db.OpenRecordset("TableY", dbOpenDynaset)

    If you are adding new records:

    With rs
    .AddNew
    .Fields("TableYKey") = lngTableXValue
    .Fields("AnotherField") = Something else
    .Update
    End With

    Set rs = Nothing
    Set db = Nothing

    If you want to edit a row that already contains the currently displayed TableX value then:

    With rs
    .FindFirst "TableYKey = " & lngTableXValue
    If Not .NoMatch Then
    .Edit
    .Fields("AnotherField") = Something else
    .Update
    End If
    End With

    etc, etc...

    Hope this helps. Code may not be perfect but it should be close.

    Gregg

Posting Permissions

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