Save an Edited Record to another Table

[manguilla]

Hello all. I am working on a project and I want to be able to save the information when a record is changed or added to a table. I have an Installer table with InstallerID, InstallerName, InstallerType and there is the option to add a new installer or edit a current installer. I want to be able to save the information added/changed to another table so I can keep record of what other people are doing in the database. I know this will have to be done in VB but I am really confused on where to start. Any help will do. Thank you in advance.

 

[Rolliee]

This can be done with DAO or ADO. I prefer DAO and the following code will do what you want.

DIM r2 as DAO.recordset , r1 as DAO.recordset

' this requires the DAO library to be checked in Tools/References in the Design Mode.

set r2 = currentdb.recordset("NuTable")

r2.addnew
r2("fieldname1") = me.fieldName1
r2("fieldName2") = me.fieldname2

r2.update

r2.close


This is the code you would use in say a command button to add the fields to a new table.

Rollie E

 

[manguilla]

I tried the code but it keeps erroring out on me. I am using Access 2003 so I didn't know if I needed anything else to add. Here is the code I have so far. I have created a command button on the Installer Form to Save the new record and then copy that information over to a History table with the InstallerID and InstallerName Fields already created. Is this correct? Thanks again for the quick response.

Private Sub Save_Installer_Click()
On Error GoTo Err_Save_Installer_Click

Dim r2 As DAO.Recordset

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Set r2 = CurrentDb.Recordsets("History")
r2.AddNew
r2("InstallerID") = Me.InstallerID
r2("InstallerName") = Me.InstallerName

r2.Update
r2.Close

Exit_Save_Installer_Click:
Exit Sub

Err_Save_Installer_Click:
MsgBox Err.Description
Resume Exit_Save_Installer_Click

End Sub

 

[Rolliee]

What is the error? Try it with the "s" removed from the set command.

r2 = currentdb.recordset("History")

Rollie E

 

[manguilla]

I will get "Compile error: Method or data member not found when I use r2 = currentdb.Recordset("History")." If I use r2 = currentdb.Recordsets("History"), I get "Item not found in this collection." Thanks again.

Manguilla

 

[Rolliee]

Use "set" in front of the r2 - good practise.

What it appears to be saying is that you have not created an empty table of the name you use or you have not set the

Tools/ Refverence/Microsoft DAO 3.6 in your libraries

Rollie E

 

[RoyVidar]

Try:

[tt]set r2 = currentdb.openrecordset("History")[/tt]

Roy-Vidar

 

[manguilla]

Thanks Rollie, the table was not empty. However, I did need to use the OpenRecordset command to insert the data added to the Installer table. Thanks alot RoyVidar! I may have another question later on when I move to another form. I'm pretty sure it deals with editing a record. I remember now. I will have an InstallLocation table and there will be InstallerName and InstallerID columns in this one too. There will be the option to edit the current InstallerName and InstallerID and replace the data with a new InstallerName and InstallerID for the current Install Location. Is there a way to save this old record and copy that information to the History table before it's changed. Thangs again in advance.

 

[Rolliee]

ooops!!!!

Rollie

 

[cas20002000]

You mentioned Access 2003.
Have you tried to use triggers?


Carlos

 

[manguilla]

I am not that familiar with triggers but will that help me out?


Manguilla

 

[manguilla]

I looked up triggers and noticed that you have to be using a Microsoft Access Project and I am not. I am just using an Access Database.mdb to do my work. Should I change everything over to a Microsoft Access Project in order to use Triggers or is there another way to keep track of an edit on a table? Thanks again.

Manguilla

 

[TheAceMan1]

How are ya manguilla . . . .

[blue]I will get "Compile error: Method or data member not found[/blue]

The above is telling me you need to set and Object Reference to the CurrentDB. So try this:

[blue]Private Sub Save_Installer_Click()
On Error GoTo Err_Save_Installer_Click

   Dim db as DAO.Database, r2 As DAO.Recordset

   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
   Set db = CurrentDb()
   Set r2 = db.OpenRecordset("History")
           [green]'
           'Rest of code
           '[/green][/blue]

See Ya! . . . . . .

 

[Jacbop]

Had a problem similiar to this but mine was giving "Method or data member not found" on Me.FieldName, which was a field in the recordsource of my form. I corrected this issue by replacing Me.FieldName with Me!FieldName the first assumes there will be a FieldName reference in the Me object and the second specifies a field in the object's recordset. It is the lazy version of Me.RecordSet.Fields("FieldName").Value but I'm working off of someone else's code.

 

[manguilla]

Yea I had to do the same thing at first. This is probably due to the fact I am using Access and not SQL Server. Oh well at least I got it to work. Thanks again.

Manguilla