Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    9

    Unhappy Unanswered: Writing to other tables from a Form

    Hey all

    I have a small problem with an access 97 database, im not sure whether it can be remedied, but here goes anyway -

    The 'input' form consists of 4 pages, I would like for each page to write to a different table.

    Clearly, this is not as simple as changing the Control Source for each text box, as I have attempted this already only to find that ?#Name appears in the text box.

    Anyone out there have some ideas?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you cannot write to multiple tables directly from fields in a single form unless you are lucky enough to be able to combine all the fields into a single UPDATABLE query and you use that query as the recordsource for your form... whether your query will be updatable depends on the detail of your data structure.

    remaining choices are subforms (will most likely work) and recordsets (will certainly work).

    the general route (in DAO) with recordsets goes something like this:

    Code:
    dim dabs as dao.database
    dim rec1 as dao.recordset
    dim rec2 as dao.recordset 'etc for as many recordsets as you like
    set dabs=currentdb
    set rec1 = dabs.openrecordset("nameOfTable1")
    set rec2 = dabs.openrecordset("nameOfTable2") 'etc
    'you can now use:
    rec1.addnew ' or .edit
    rec1!fieldName = forms!yourForm.TextBox.Value
    rec1.update
    'to add/edit stuff to the recordset (=to the table)
    'or
    forms!yourform.textBox.Value = rec2!FieldName 'to set a text box from the table
    'etc etc etc

    you will find heaps of stuff in this forum on playing with recordsets.


    izy
    Last edited by izyrider; 07-12-03 at 06:34.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and perhaps i should mention that normally the fields on your form that you will use to stuff tables via recordsets should NOT be bound to the table.

    izy

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My vote is for the subforms. Its the simplest solution. You could even put each form on 1 page of a tab control.

    blindman

  5. #5
    Join Date
    May 2003
    Posts
    9

    Thumbs up

    cheers, I took the easy way out and used subforms, and got the result i was looking for.

    Thanks heaps!

Posting Permissions

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