Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001
    Posts
    2

    Question Unanswered: Add records - pick from multiple tables

    Hi there all,

    I have a database which contains aircraft information. I have however got a lot of new information from the manufacturers which I want to store in a new table. I do not want to copy the forms and queries into another database and I was wondering if there was any way I can use the existing forms but pick which table I want the record to be added into and also with the viewing of the data have the option to pick the table I want to use.

    Thanks in advance

    Rohith

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Before answering your question important to know whether your old and new tables have the same list of fields (structure) or not.

    If yes, then when you open your form just pass it the table name:

    DoCmd.OpenForm "frmForm", , , , , , "tblNewTable"

    or

    DoCmd.OpenForm "frmForm", , , , , , "tblOldTable"

    Then in open event of frmForm put the following code:

    Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "Select * From " & Me.OpenArgs & ";"
    End Sub

    This is just an example. You can elaborate it, may be using some selection criteria in Where clause:

    Me.RecordSource = "Select * From " & Me.OpenArgs & " Where ... ;"


    HTH

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You could also continue the single-table pardigm and simply append the new records into the old records table. By adding a field to that table, you could distinguish between the old and new records and use that field as a criteria in the source query for the form.

    You could also add a pair of toggle buttons in an option group to your form, each indicating an association to a different table. In the AfterUpdate property of the option group you can switch the record source of the form.

    Either method, IGelin's, or any of these methods will work fine.

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Hi PracticalProgram,

    I like your method. It is very nice working solution.

  5. #5
    Join Date
    Sep 2001
    Posts
    2
    Thanks for the help guys.

    I have gone with putting a new field in the old table option. So far so good.

    Rohith

Posting Permissions

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