Add query

[Superguppie]

Hey there,

I have an Add Query that adds all records from the query to a table. now each time i run the query it adds the records again. what the query must do is, only add new records to the table AND old records but only when the field

 has changed.

Is this possible?
im open for suggestions


Greetz Superguppie

 

[Remou]

Where is your existing SQL?

 

[Superguppie]

thats the problem i dont know what to fill in by SQL

im new to SQL so.

What i want is:

Table1:
Productname
Location

A form that can change the location.
when the location is changed, Before update, the record in table1 must be copied to another table. so you can see a history where the device has been. so if the record is going to change, the old record must be copied to the table, also when the second change comes, he must copy it to the table but no to delete the first record in the table....

is this possible?

Greetz

 

[Remou]

You say:

I have an Add Query that adds all records from the query to a table

It will have SQL. Choose SQL view from the query design window.

 

[Superguppie]

srry about that. i found it

here it is:

INSERT INTO tblTEST ( Borentcode, Filliaalnummer )
SELECT tblApparaten.Borentcode, tblApparaten.Filliaalnummer
FROM tblApparaten;

 

[Remou]

I suggest you run some SQL in the After Update event of your form, for example:

'Assumes numeric tblApparaten.Borentcode
strSQL="INSERT INTO tblTEST ( Borentcode, " _
& "Filliaalnummer ) " _
& "SELECT tblApparaten.Borentcode, " _
& "tblApparaten.Filliaalnummer " _
& "FROM tblApparaten " _
& "WHERE tblApparaten.Borentcode = " & Me.Borentcode

CurrentDB.Execute strSQL, dbFailOnError

You will need to check to see if the update should be saved.

 

[Superguppie]

Thanks it works