TechRepublic|Forums|Web Development

Web Development

General discussion

  • Creator
    Topic
  • September 29, 1999 at 3:18 pm #2080301

    Access & updating tables with forms

    Locked

    by jallcorn · about 25 years, 6 months ago

    I am new to MS Access, and need to update multiple tables in Access via a form. Trouble is, when I link my tables then create my form to update fields in more than one table by this one form, the form is blank. I can easily update tables with one form for each table, but there has to be a better way. Ideas appreciated. JA

    Topic is locked This conversation is currently closed to new comments.
  • Creator
    Topic

All Comments

  • Author
    Replies
    • September 29, 1999 at 8:50 pm #3901071

      Access & updating tables with forms

      by toervla · about 25 years, 6 months ago

      In reply to Access & updating tables with forms

      First, you need to make a query, containing the fields you need from all the tables you need. Then make sure your form is based on that query.

    • October 31, 1999 at 9:37 am #3900743

      Access & updating tables with forms

      by jvlittle · about 25 years, 5 months ago

      In reply to Access & updating tables with forms

      make a subform using a query as a recordsoursce for each linked table you wish to update.

    • November 10, 1999 at 12:35 pm #3902322

      Access & updating tables with forms

      by crimsoncub · about 25 years, 5 months ago

      In reply to Access & updating tables with forms

      Try creating a macro that your form calls to update information in each table.

    • June 8, 2000 at 7:02 am #3894581

      Access & updating tables with forms

      by bob sellman · about 24 years, 10 months ago

      In reply to Access & updating tables with forms

      I’m going to assume that your tables are related like tables would be for (as an example) a purchase order, with one table for the heading and a second for the line items (for which there can be multiple rows for each row [purchase order number] in the heading table).

      Using a subform for the details table, linked by the common column (which could be an autonumber column in the header table and the same value as a number column in the details table, and neither number even displayed to the users), you should see all matching data.

      You can easily get into traps with a queries that combine multiple tables. While you still may not be able to update with the query, unless you define the join properties correctly you will not see a row ofdata unless there are matching values in ALL tables. Based on your question, this may be what is happening. Look at your query results directly, rather than through the form. Set your joining properties to include ALL header rows (using the purchase order example) a

    • August 24, 2000 at 2:51 am #3892637

      Access & updating tables with forms

      by donq · about 24 years, 8 months ago

      In reply to Access & updating tables with forms

      Use sub-foem as previously suggested or set your Form’s RecordSource property to the name of your query. One query works fine IF (big IF) each field in the multi-table query comes from the “parent” Table (or it is not the FOREIGN KEY half of the relationship) and the recordset produced by the query (test before attaching to the form) is a Dynaset. ALSO auatmatically Update after any record change or you will cause indexing errors. Don Quigg

    • September 20, 2000 at 7:01 pm #3793287

      Access & updating tables with forms

      by jallcorn · about 24 years, 7 months ago

      In reply to Access & updating tables with forms

      This question was auto closed due to inactivity

  • Author
    Replies
Viewing 5 reply threads