Create a static table

[Alexandre Cote]

Hello,



I think what I am trying to do is relatively simple, but I have a few gaps in my understanding and I was wondering if someone could help me out.

I have a query that pulls together information upon a record.

When a record is created, I want this information to be stored in a seperate table so that it will be consultable in the future.

Example (simplified example of what I am trying to do):

TABLE 1
pkID: Record1
pkDateCreated : 13/09/2010
Attribute1: Candy
Attribute2: LowFat


Query 1 linking TABLE 1 to TBL_DESCRIPTION

Record1 13/09/2010 Candy Candy is very good. LowFat 10 mg per candy.


After the creation of a record in Table 1, I want the query to be runned and copy/pasted into TBL_HISTOFCANDY so that we can track the records that were created in the past.

The reason why I am doing this is that the text associated to the different attributes is subject to change, and in the future it has to be consulted. For example, if tonight someone in my team decides to change "Candy is very good." for "Chewbacca gum for all of your chewing needs.", upon creation of a new record, this is what would be the content of the table TBL_HISTOFCANDY :

Record1 13/09/2010 Candy Candy is very good. LowFat 10 mg per candy.
Record2 14/09/2010 Candy Chewbacca gum for all of your chewing needs. LowFat 10 mg per candy.

I hope I have been clear in my description. If not, tell me what is not clear and I will respond as quick as possible (at ligthspeed)..... yeah... lol... But seriously, any help would be appreciated

Thanks in advance,
Alex

[ajetrumpet]

this is a pretty good plan I think. what you will want to do is run some append code after the query is created. the way to go about it really depends on the order in which you are performing the tasks that have to run (one at a time), but say for example that you are looking at the bound form and you are on record ONE (id field). after the form is saved for the first time (e.g. - record is entered), you could put something like the following behind your save button:

currentdb.execute "INSERT INTO tblArchive " & _
"SELECT tblWithRecords.* " & _
"FROM tblWithRecords " & _
"WHERE [recordID] = forms!OpenForm!IDfield"

that'll archive the record after the insertion. you'll have to change the event name though if you want to do it behind subsequent changes too.

HTH.

[Alexandre Cote]

Thank you for the reply AjeTrumpet,

I had suspicions I could go that way. I will test it out and report back.