form data loads from one table and saves to another

**PianoMan64** · Mar 20 '08, 06:30 AM

Hi,

I have a Access 2003 database Form that opens containing data from one table and when clicking a button i wish it to transfer the forms text box information into another form, for the reason being that i wish to delete the original information ("session" table) where all the current data is and make a copy of it into another table ("Session_CL X" table) for it to be archived.

What changes do i need to make in code?

i hope you can help... Colin

a sample of my inheritted code is as below:-

'-------------------------------------------------------
'-------------------------------------------------------
Private Sub btnDelete_Click ()

Dim dbs As Database, qdf As QueryDef, rst As Recordset

' Return reference to current database.
Set dbs = CurrentDb

' Create new query.
Set qdf = dbs.CreateQuery Def("")

' Construct SQL statement including parameters.
qdf.SQL = "PARAMETERS [simcode] TEXT, " & _
"[start_date] DATETIME, [end_date] DATETIME," & _
"[session_type] STRING, [customer_code] STRING, " & _
"[checkee] STRING, [checker] STRING, [prog_code] STRING, " & _
"[simconfig] STRING, [simfmc] STRING,[updated_date] STRING,[Instructor] STRING; " & _
"INSERT into session_last ( simulator_code, session_start, session_type," & _
"customer_c ode, session_end, " & _
"checker_na me, checkee_name, " & _
"program_co de, sim_fmc, sim_config, updated_date, priv_fee, sim_motion_type , Instructor, Case, CLX_Date, CLX_UserName, CLX_Authority, CLX_Reason ) values ( [simcode], [start_date]," & _
"[session_type], [customer_code], [end_date], " & _
"[checker], " & _
"[checkee], [prog_code], " & _
"[simfmc], [simconfig], [updated_date], [priv_fee], [sim_motion_type],[Instructor], [Case], [CLX_Date], [CLX_UserName], [CLX_Authority], [CLX_Reason]);"

qdf.Parameters( "simcode") = simulatorCode
qdf.Parameters( "start_date ") = txtStartDate & " " & txtStartTime
qdf.Parameters( "end_date") = txtEndDate & " " & txtEndTime
qdf.Parameters( "session_ty pe") = cboSessionType
qdf.Parameters( "customer_code" ) = cboCustomerCode
qdf.Parameters( "checkee") = txtUsercreate
qdf.Parameters( "checker") = txtSpecialDetai ls
qdf.Parameters( "prog_code" ) = cboProgramCode
qdf.Parameters( "simconfig" ) = cboSimConfig
qdf.Parameters( "simfmc") = cboFmc_Load
qdf.Parameters( "Instructor ") = cboInstructor
qdf.Parameters( "updated_da te") = txtcreatedate
qdf.Parameters( "priv_fee") = txtPrivFee
qdf.Parameters( "sim_motion_typ e") = cboSimMotionTyp e
qdf.Parameters( "Case") = cboCase
qdf.Parameters( "CLX_UserNa me") = txtEmployee
qdf.Parameters( "CLX_Date") = txtCLXDate
qdf.Parameters( "CLX_Authority" ) = txtCLXAuthority
qdf.Parameters( "CLX_Reason ") = txtCLXReason
'Run select query and populate
Debug.Print "about to execute the update"
qdf.Execute

Set dbs = Nothing
End Sub
'--------------------------------------------------------------------------
'--------------------------------------------------------------------------

First off I would change it from a query definition to a Recordset. This way we can deal with the items on a record by record level.

First thing you're going to need to make sure of is that both tables have a Numeric Primary Key assisgned to each record in the table. Make sure for demo purposes that it is named ID. This way, I can write an example that should work for you.

Open the Visual Basic Code Editor Alt-F11.

Locate the BtnDelete_Click ()

Paste the following code into it.

[code=vb]

Dim MyDB as DAO.Database
Dim MyRS as DAO.Recordset
Dim MyRSArch as DAO.Recordset

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecord set("SELECT * FROM SESSION WHERE ID = " & me.id, dbOpenDynaset) 'The ID control needs to be on the form and displayed to work

Set MyRSArch = MyDB.OpenRecord set("SELECT * FROM SESSION_CLX",db OpenDynaset)

If Not MyRS.EOF Then
MyRSArch.Add
MyRSArch!simcod e = MyRS!simulatorC ode
MyRSArch!start_ date = MyRS!txtStartDa te
'.... Keep on going for each field that you're wanting to move over.
MyRSArch.Update
MyRS.Delete
End if

MyRSArch.Close
MyRS.Close
MyDB.Close

Set MyRSArch = Nothing
Set MyRS = Nothing
Set MyDB = Nothing

[/code]

Please make sure that all the table names are correct for what you're wanting to move from and what you're wanting to move too.

I hope that helps,

Joe P.

**coldude** · Mar 25 '08, 01:11 AM

Great, that makes it all easier.. thanks for your help

form data loads from one table and saves to another

form data loads from one table and saves to another

Comment

Comment