Updating tables

[BobNTN]

When inputting information into multiple tables, I assume, which I hate to do, the best way is to use append query. If this is correct, is there a limit to how many tables can be appended to from an append query in a single form ?

 

[vbaInet]

How many tables at one go? It would depend on the joins. If the joins are ambiguous then the query will become read-only/un-updateable.

You can use a Docmd.RunSql "UPDATE ... " statement and write as many of those as you wish.

 

[PNGBill]

Append Queries are not in a form but they can be called on from a form.

You could have many Controls on one form and each of these (fields) could accept data that is Appended to a table (any table) and this action could happen many times on a single form.

The simplest way to do this is to have SubForms on your form and each of these SubForms are bound to a table. A subform can have just one control (field) although this may well be an Update rather then append action.

Your Form can have Unbound Controls that allow you to enter data and either an event or a macro (eg On Update) will append the data to a table of your choice.

I noticed a thread recently referring to nearly 800 controls being the limit on a form - I think. If so, then you have a great number of opportunities to append records while on just one form.

 

[BobNTN]

just working on a program for my brother-in-law.
Needs a general info table, a parts table, and a work hour table at least as the 3 major tables. I thought about using 3 forms for inputs but that would be a little clumsy so I was thinking of an append query and doing all from one form.

With the sub-form way, wouldn't the main form have to be on a table with static info which I wouldn't normally have until quite a bit of info is in and then work would be repetitive on the same equipment.

In other words, I'm not starting with a customer database, have to input that as it goes.

 

[PNGBill]

You can have your form re queried / refreshed as you go along either when you update certain controls or by a command button.

The form doesn't have to "make sence" in regard to the subforms even being about the same issue.

If space allows, your main form could just be a header, not related to any tables.
Two, three or 4 or more, subforms are placed on your form and each relates to their own table and you input data and refresh the form/s as you go along.

 

[PNGBill]

Say one subform allows you to enter data on cars in the car sales yard. Another subform allows you to control the daily cleaning and polishing of the cars.

As you add a car to the database the other subform can show you the situation on the cleaning of the cars. This form can be re queried when ever you enter a new car on the other form, or you can just refresh this form as you wish.