How to Copy a record to another table, prior to editiing

[Gregers]

Hello forum,
I am cooking on a quality assurance system. In this i save politics, procedures and instruktions i memo fields. In order to have traceability in the documentation, I would like to save the old text to annoter table before it is edited. Is there any Way this Can be done?

[Robeen]

The short answer is 'Yes, it can be done'.
And there are several ways to do it . . . depending on how you have things set up and how you are currently using your database.

You haven't given any details about the tables you have created - or any other features of your database.
Do you have a Form that you are using to do your Editing? That would probably be the best way to tackle it.

If you can explain the scenario with a little more detail, it would be easier to give you help/suggestions.

[Gregers]

Thank you Robeen,
Yes, i have forms to film in tables. My tables for the qa-documents are 3, One named book-draft, One named book-current, and One named book-backissues. Book-draft has the following fields: id (prim. Key), book-categoty (text), book-ref(text). Book-ref-index(text), book-draft-date(date), book-draft-author(text),book-draft(memo). The text i would like to keep traceable is in the memo field. My idea was to be able to use book-draft as a place for sketching and then upon release, to have the record copied to book-current and the corresponding record in book-current copied to book-backissues. Book-backissues has an Extra field named book-backissues-withdrawaldate (date). This could then be used to differentiate between records, as there will not be more than One update per day.
I recon that there nede to be a chain of actions beginning with a copyright from book-current to book-backissues, but i dó not know how to do it. Maybe i should remove the table book-draft and start an edit with copying the relevant record from book-current to book-backissues and then do the editing in book-current. My Real problem is that i dó not know how to dó this copying of records and how to automate it. I hope you or some other kind person Can help me based on this.

[Robeen]

You could just add another Memo field [Eg: Book-draft-Revised] to the same table and put the revised text in that field.
That would be simpler than writing stuff to a different table.
Would that work?

But - to answer your question about copying a record from one table to another . . .
Here's a little VBA I wrote to get values from fields on a Form - and insert them into a Table:
You will have to replace my Form field names with your own.

Private Sub cmdUpdate_Click()
'Add record to a Table.
'Insert values on a Form into a table.

Dim RCC_ID, CourseDesc, StrSQL As String
Dim i, j, intRCC_ID, Duplications As Integer
Dim StartDate, StartTime As Date
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tblCourses")

DoCmd.SetWarnings False

'______________________________________________________________

'This section gets values from controls on my form.
'You will have to substitute your own form field names.

'This is just a numeric value to insert.
intRCC_ID = 7

'Text Value to insert.
'My field name is CourseDescription . . .
Me.CourseDescription.SetFocus
CourseDesc = Me.CourseDescription.Text

'Date value . . .
'My field name is StartDate . . .
Me.StartDate.SetFocus
StartDate = Me.StartDate.Value

Me.StartTime.SetFocus
StartTime = Me.StartTime.Value

'______________________________________________________________

'The tricky part will be getting this insert SQL statement built correctly.
    StrSQL = "INSERT INTO YourTableNameHere (NumericField, TextField, DateField, Date_TimeField) "
    StrSQL = StrSQL & "VALUES (" & intRCC_ID & ", " & "'" & CourseDesc & "'" & ", #" & StartDate & "#, #" & StartTime & "#); "
        
    'MsgBox "SQL before RunSQL command: " & StrSQL
    
    DoCmd.RunSQL StrSQL

DoCmd.SetWarnings True

End Sub

I hope this helps!

P.S. Here are two helpful web sites:
http://www.baldyweb.com/
http://allenbrowne.com/subquery-01.html

[Gregers]

Again, thank you Robeen,
I fear that having 2 memo fields in the same table will only solve part of my problem.
For a certified QA-system, you have to be able to recreate all the valid issues of the full QA-system for the duration of the certification (typically 5 years for labs). This in practice means that you have one draft which you work on, one valid and official text and tons of back-issues. So, even with 2 memo fields in the table (solving the draft/valid issue problem), the back-issue would still have to be taken care of.
I shall look into your VBA and the suggested links - thank you for your efforts

[Robeen]

Ok. Let me know if you still have problems. All the best!