General discussion

Locked

Access & updating tables with forms

By jallcorn ·
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

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access & updating tables with forms

by ToerVla In reply to Access & updating tables ...

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.

Collapse -

Access & updating tables with forms

by jallcorn In reply to Access & updating tables ...

The question was auto-closed by TechRepublic

Collapse -

Access & updating tables with forms

by jvlittle In reply to Access & updating tables ...

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

Collapse -

Access & updating tables with forms

by jallcorn In reply to Access & updating tables ...

The question was auto-closed by TechRepublic

Collapse -

Access & updating tables with forms

by CrimsonCub In reply to Access & updating tables ...

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

Collapse -

Access & updating tables with forms

by jallcorn In reply to Access & updating tables ...

The question was auto-closed by TechRepublic

Collapse -

Access & updating tables with forms

by Bob Sellman In reply to Access & updating tables ...

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

Collapse -

Access & updating tables with forms

by jallcorn In reply to Access & updating tables ...

The question was auto-closed by TechRepublic

Collapse -

Access & updating tables with forms

by donq In reply to Access & updating tables ...

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

Collapse -

Access & updating tables with forms

by jallcorn In reply to Access & updating tables ...

The question was auto-closed by TechRepublic

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Software Forums