Udate to move a record to another table

[jcardullo]

Can I write code so that when a field is updated from a form, the old data is replaced and moved to another table?

I am using Access 2000

 

[faradhi]

You Could just copy what is in the field to be updated OnEnter.

 

[jcardullo]

What is the syntax for copy? I can't find anything in Help.

 

[doulostheou]

To copy you use DoCmd.RunCommand acCmdCopy. However, you probably won't want to copy exactly.

The solution you choose will partly depend on what the table looks like where you are moving the data. Does it contain all the fields from your record or is it just storing the FieldName and OldValue?

Also, there is an OnChange Event which would probably be better suited to your needs.

 

[jcardullo]

I am interested in anything better suited. What is the On Change event I need?

Sorry about the elementary subject matter, but I am new to databases and for some reason none of the ADO topics in my Help file will display.

Thanks for any assistance.

 

[doulostheou]

Before explaining what I would do with the OnChange event, I need to know whether your table is storing the entire record when changed or certain fields when changed. The OnChange (or the on enter) event will not work if you are wishing to save the whole record.

 

[jcardullo]

the whole record

I wish to save the entire record. Is there any way to do that?
I need to enter a record from a form and if certain fields already match it in the table, I need the matching record in the table to move to another table. My table names would be tblCurrentComponents and tblComponentHistory. All fields would remain identical except two that are integer.

Thanks for any help.

 

[doulostheou]

I'm assuming you want the history table to contain a history of all changes (not just the most recent one). If you copy and paste the following in the beforeUpdate event of the form it will detect if a change has been made and ask the user if he wishes to save it. If he answers yes, it will append the data to the tblComponentHistory table. If he answers no, it will undo the change. I have added a field to that table called DateTime. Make this a Date/Time field and set it to General Date. This will store the date and time that the change was made. With more coding you can get the name of the user who made the change as well. Make sure your ID field is not the primary key in your history table and that it is not an AutoNumber field (make it a number field instead). In the code you will need to replace ID with the name of your PrimaryKey field wherever it appears. I have also assumed that your form has a similar name to the table it was built off of (frmCurrentComponents). If this is not the case, you will need to change that as well:

[COLOR=royalblue]
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim YesNo As String
If Me.Dirty = True Then
    YesNo = MsgBox("You have made changes to this record.  Do you want to save them?", vbYesNo)
    If YesNo = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO tblComponentHistory ( [DateTime] ) " _
        & "SELECT tblCurrentComponents.*, Now() AS [DateTime] " _
        & "FROM tblCurrentComponents " _
        & "WHERE (((tblCurrentComponents.ID)=[Forms]![[Color=Red]frmCurrentComponents[/Color]]![ID]));"
        DoCmd.SetWarnings True
    Else
        ID.SetFocus
        Dim ctlTextbox As Control
        For Each ctlTextbox In Me.Controls
            If ctlTextbox.ControlType = acTextBox And Not ctlTextbox.Name = "ID" Then
                ctlTextbox.Value = ctlTextbox.OldValue
            End If
        Next ctlTextbox
    End If
End If
End Sub
[/COLOR]

 

[jcardullo]

Thank you thank you thank you thank you. I was about to tank this whole project because it's entire reason for living was to track history and current lives of components. Thank you for giving my dbase a purpose.

By the way, on a general note, this forum has been the most useful resource to a newbie like me. I find answers to most of my perdicaments by just browsing threads.

"Standing on the shoulders of giants leaves me cold" but I am guilty of it anyway.

 

[doulostheou]

You don't need to thank me, just the forum. How do you think I learned how to write code?