Active Table and History Table, how to copy on deletion?

[Leopardfist]

My 2nd post, and I am very new to DB and Access. I have a problem that I want to get help on. I want to set up a main form that is used to enter and delete all data for my table. I wish to add either a button or to make it automatically happen when a record is deleted, that it is first copied to a separate table with the same fields, except it also has a closed date that would be the date that the record was copied over. I know zilch about VB, VBA or any other language other than AutoIt, so assume I am what I am, an ignorant beginner.

I did look into the event somethihng like upondeletion or something... while trying to find help on this in the access and VBA parts of Office, but I do not know how to utilize the event with Basic or SQL, which I know none of either.

Any help or examples are very much appreciated.

**EDIT**
I do not require all fields to be recorded to the secondary DB (History), so if someone can just give me an example of how I would move two fields to a separate DB, I can hopefully learn enough from it to do more.

Thanks a Bunch!


EXAMPLE**
Current Loans (Table 1):
CustomerID
Name
Address
City
State
Phone

Customer History (Table 2):
CustomerID
Name
Phone

That gives an example to help understand what I need. I want to store the CustomerID, Name and Phone values of the record being deleted, to the History Table, which I am using as a closed account table for later look up.

 

[Leopardfist]

You can use the .AddNew method to add a row to the history table. You can find a code sample in help.

How do I tell it what values or records to actually add? Will it automatically know that I only mean the single record I am viewing of the other table?

 

[Leopardfist]

Where can I find this sample code? I searched the sample database forum for .addnew and got no hits.

I saw an example of it for a DAO database, which I am not sure I have, mine is access MDB. But it was pretty confusing for me, having no VB experience at all.

Looking at your small example... I am assuming rs is recordset, but what is me?

also, after I write all the rs.addnew all I have to do in the function is list each field then end the function, or do I have to do the rs.addnew for each field? And after that, does the rs.update command do the save all by itself, or do I have to do it for each field?

I am sorry for so many dumb questions, this is the last part of my DB that I have to complete, and it has me stumped.

 

[Leopardfist]

To simplify this for me, could you give me an example for how to do this with the following info?

Database Name=tester
Table 1=main
Table 2=history
The fields are identical for each table, for this example they are=name,sex,age

Also, to put this function into the ondelete event, does it require any other code?

 

[Leopardfist]

Private Sub Form_Delete(Cancel As Integer)
    history.AddNew
    history!Name = main!Name
    history!Age = main!Age
    history!Sex = main!Sex
    history.Update
End Sub

Is that the right way? Is it being inside the form_delete function correct?

Also, can I use an autonumber on the history table for my PK without using the .addnew for the autonumber field?

 

[Leopardfist]

Well, I had a LOT of errors and bugs to work thru, but I FINALLY got it to actually write the copy of the record upon deletion. The problem now is it wrote it to the same table that it deleted from, the one named main. The code is below, and I don't know what is wrong with it.

Private Sub Form_Delete(Cancel As Integer)
    Dim dbs As DAO.Database, history As DAO.Recordset, mydate
    Set dbs = CurrentDb
    Set history = Me.RecordsetClone
    mydate = Date
    With history
        .AddNew
            !lname = Me!lname
            !fname = Me!fname
            !number = Me!number
        .Update
    End With
End Sub

I also wanted to write the date of deletion to the history table, which is why I dim the mydate and then do mydate = date. The statement I had in the .addnew statement kept getting an error, it was:

!date = mydate

It kept bugging it out, and I had to delete it to get past the error. Can you tell me how to correctly do the date of deletion into the history table as well?

 

[GamingResources]

Well, what you could do, if understood correctly, is simply add a new field within your primary that basically says, deleted (yes or no)...when deleted if could check this box and perhaps input a closed date...

You could do this all simply by using an UPDATE query...Just create a new query, click the type UPDATE, and input which fields to update and update to what...

If the above, you would say, update this YES/NO field to YES, the date field to like Now()

Just an idea, would eliminate having multiple tables...

Or, just control your updating through queries within VB

Docmd.Hourglass True
Docmd.SetWarnings False
Docmd.OpenQuery "query_name"
Docmd.Hourglass False
Docmd.SetWarnings True

Hourglass = signifying a process is occurring
SetWarnings = don't want the users to be bugged with useless messages

 

[Leopardfist]

That will not work for me, I actually have over 50 fields in my main table, and only want to copy 7 to my history table. The names of the fields however are exact matches. I want it to addnew when the user deletes a record from the main form, so I have been puttting the sub into the on delete event handler. As I said, I can get it to copy it to the same table it deletes from, in several different ways or syntax, but I cannot for the life of me get it to write it to the other table. My ignorance of vb is vast, so if anyone can just post the whole sub using my simple example tables/fields, I can get the jest of it and hopefully make it work. The example above worked great for getting me started, but the ....'s that you assume I understand I don't. If you could include a way that I could also add the current date to the history table as well I would be totally covered.

Thanks for any assistance!

 

[Leopardfist]

AWESOME PAT! I have been trying to get it to work for several days, asking for help on several forums, and was at the point where a guy gave me three times that amount of code, to do it a lot harder. This is absolutely what I needed, and I GREATLY appreciate you being so understanding and generous with me!

THANK YOU!!!

One last question about a problem I did not even contemplate until this morning. This example was very basic, and the table I am using to get the data from to write to the history table is huge. I am only copying a vew fields though. Something that occurred to me this morning is, a couple fields that I want added, are not actually shown in the form I am using. They are in the same table, but they are not visible on that form, only a separate one for separate data. Is there a way I can still use this code to copy those fields too, without them being viewable on the form? Or is it just a matter of defining the main database with a variable, and using the variable to get the data, and then to write the data to the history table?

 

[Leopardfist]

Pat,

this code worked in my Tester DB, which used the exact same structure, but when I tried to use it in my real database, it gave me a compile error on the 2 Dim's. The error was: User-defined type not defined.

It triggers from both of the Dim statements.

It also highlights the "dbs As DAO.Database" and "history As DAO.Recordset"

What am I doing wrong?

It's in access 2000 format just like the Tester Database it works on.

 

[RuralGuy]

You need to set a reference to DAO. <ALT> F11 Tools>References and scroll down to Microsoft DAO 3.x Object Library and check it.

 

[Leopardfist]

Thank you.

That list of references is IMMENSE! Are there others that I should include?

 

[RuralGuy]

Not until you need them.