Copying record from one table to another

[mskitty]

In table 1, information is entered using a form. Table 2 has information stored on a temporarily basis where table 1 is perminate and contains years of data. What I am trying to do is have the information in the form copied into the temporary table 2 when it meets a condition and still enter the information into the perminate table 1. How do I do this? Still learning about macros.

 

[sneuberg]

You could create an append query that would insert the form information into table2 something like:

INSERT INTO Table2 ( Field1, Field2 )
SELECT [Forms]![FormName]![Field1] AS Expr1, [Forms]![FormName]![Field2] AS Expr2
WHERE [I]the condition[/I]

In the query designer this looks like. Note that you can right click on the Field in the grid and use the Expression builder to create the form references. It's usually better to have the form open when you do this.

 

[Minty]

Even if it is just a temporary table, normally having to do this indicates a poor design. if I have understood you correctly you want to update a subset of the main information.

Normally you would create a form that only loaded the records you were interested in from your main table, and simply edit them as required.

 

[Galaxiom]

Requirements like this are often driven by some misguided presumption that a Save button should be included so that multiple edits can be saved or rejected by the user as a batch.

However, using temporary tables and updating with queries is not a simple as many imagine. It is easy to lose edits of one user because another user updated from a stale temporary table. Databases manage conflicting updates automatically and it is not a simple alternative to code it.

If you really must have the facility to update or reject multiple records, then use a Transacted Bound Form.