Updating tables through forms

[cpgoose]

Hey guys...any help would be great...I'm stuck...

I just created a very basic TABLE with two fields: Name and Address.

Then I created a basic FORM to enter the data into the table. It works fine. If you close everything out, and then open up the FORM again, it starts with the information from the first row in the table. If you "scroll", it goes to the second, and so on. If you update any of this information, it updates it in the table. This is exactly what I want to happen (I want to be able to update the table by scrolling through it's contents in a FORM).

Unfortunately, this was just a test case. I have a more in-depth setup, with multiple tables and things like that. If I set the FORM to just one table, then it does what I want (above). If I have fields from multiple tables in the FORM, it doesn't do it. What gives?

Any ideas?

Thanks!

 

[willir]

Well, for some basics...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer to design and relationships...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

And a really good article on queries
Harnessing the Power of Updatable Queries

Using forms to update / insert data in tables is an extremely common way of accomplish this task.

Aside from a single form, a common approach is to use a main form and a subform. For one-to-many, the subform can be made as a continous form to display all related records.

Hint:
Define your relationships first. From the menu, "View" -> "Relationships. If you do this step first, then Access will automatically link the main form to the subform.

Also look at the use of combo and list boxes. The server two purposes -- link a record on the "many" side to a record on the "one" side; apply a filter or a record when used as an "unbound control".

Realize that you can use visual basic code and queries to improve functionaility.

Lastly, review Access help. Sometimes it actually does. And review the demo databases examples such as NorthWind.

Richard

 

[TheAceMan1]

How are ya cpgoose . . . . .

Basically your has to do with the relationships of those tables.

[blue]You [purple]can't add/edit[/purple] and save a record in a Child Table [purple]unless there's a corresponding ID[/purple] (usually the primary key) in the Parent Table.[/blue]

See Ya! . . . . . .

 

[cpgoose]

Thanks for the help guys!

I currently have 5 tables, each one has an ID as the first field, which is also the primary key.

As for the relationships, I have the primary key of the first table related (one-to-one) to the primary key of the second. Then the primary key of the second related to the third and so on and so forth.

Are there problems with this?

 

[fkegley]

You also need to examine the rules regarding which recordsets are updatable and which are not.

From Access Help:

When can I update data from a query?
In some cases, you can edit data in query Datasheet view to change the data in the underlying table. In other cases, you can't. The following information shows whether a query's results can be updated, and if not, whether there is an alternative.

Data is updatable

You can update a query or query field in the following cases:

A query based on one table
A query based on tables with a one-to-one relationship
The query's results contain a Memo, Hyperlink, or OLE Object
Data is updatable under certain conditions

If a query is based on tables with a one-to-many relationship, you might not be able to edit the data for the following query fields.

Query field Solution
Join field from the "one" side Enable cascading updates between the two tables.
New records, if the "many" side join field doesn't appear in the datasheet Add the join field from the "many" side to your query to allow adding new records.
Join field from the "many" side, after you've updated data on the "one" side Save the record; then you'll be able to make changes to the "many" side join field.
Blank field from the table on the "one" side of a one-to-many relationship where an outer join exists Enter values in fields from the table on the "many" side, but only if the joined field from the "one" side contains a value for that record.
New records, if entire unique key of ODBC table isn't output Select all primary key fields of ODBC tables to allow inserts into them.

Data can be deleted but not updated

Query or query field Solution
Query (or underlying table) for which Update Data permission isn't granted To modify data, permissions must be assigned.
Query (or underlying table) for which Delete Data permission isn't granted To delete data, permissions must be assigned.

Data can't be updated

Query or query field Solution
Query based on three or more tables in which there is a many-to-one-to-many relationship Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
Crosstab query None
SQL pass-through query None
Query that calculates a sum, average, count or other type of total on the values in a field, or an update query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
Union query None
Query whose UniqueValues property is set to Yes None
Query that includes a linked ODBC table with no unique index, or a Paradox table without a primary key None
Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view You must join the tables properly in order to update them.
Calculated field None
Field is read-only; the database was opened as read-only or is located on a read-only drive None
Field in record that has been deleted or locked by another user A locked record should be updatable as soon as it is unlocked.





Frank kegley