By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

form data loads from one table and saves to another

P: 9
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_CLX" 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.CreateQueryDef("")

' 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_code, session_end, " & _
"checker_name, checkee_name, " & _
"program_code, 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_type") = cboSessionType
qdf.Parameters("customer_code") = cboCustomerCode
qdf.Parameters("checkee") = txtUsercreate
qdf.Parameters("checker") = txtSpecialDetails
qdf.Parameters("prog_code") = cboProgramCode
qdf.Parameters("simconfig") = cboSimConfig
qdf.Parameters("simfmc") = cboFmc_Load
qdf.Parameters("Instructor") = cboInstructor
qdf.Parameters("updated_date") = txtcreatedate
qdf.Parameters("priv_fee") = txtPrivFee
qdf.Parameters("sim_motion_type") = cboSimMotionType
qdf.Parameters("Case") = cboCase
qdf.Parameters("CLX_UserName") = 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
'--------------------------------------------------------------------------
'--------------------------------------------------------------------------
Mar 20 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 374
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_CLX" 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.CreateQueryDef("")

' 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_code, session_end, " & _
"checker_name, checkee_name, " & _
"program_code, 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_type") = cboSessionType
qdf.Parameters("customer_code") = cboCustomerCode
qdf.Parameters("checkee") = txtUsercreate
qdf.Parameters("checker") = txtSpecialDetails
qdf.Parameters("prog_code") = cboProgramCode
qdf.Parameters("simconfig") = cboSimConfig
qdf.Parameters("simfmc") = cboFmc_Load
qdf.Parameters("Instructor") = cboInstructor
qdf.Parameters("updated_date") = txtcreatedate
qdf.Parameters("priv_fee") = txtPrivFee
qdf.Parameters("sim_motion_type") = cboSimMotionType
qdf.Parameters("Case") = cboCase
qdf.Parameters("CLX_UserName") = 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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim MyDB as DAO.Database
  3.     Dim MyRS as DAO.Recordset
  4.     Dim MyRSArch as DAO.Recordset
  5.  
  6.     Set MyDB = CurrentDb()
  7.     Set MyRS = MyDB.OpenRecordset("SELECT * FROM SESSION WHERE ID = " & me.id, dbOpenDynaset) 'The ID control needs to be on the form and displayed to work
  8.  
  9.     Set MyRSArch = MyDB.OpenRecordset("SELECT * FROM SESSION_CLX",dbOpenDynaset)
  10.  
  11.      If Not MyRS.EOF Then
  12.        MyRSArch.Add
  13.        MyRSArch!simcode = MyRS!simulatorCode
  14.        MyRSArch!start_date = MyRS!txtStartDate 
  15.        '.... Keep on going for each field that you're wanting to move over.
  16.        MyRSArch.Update
  17.        MyRS.Delete
  18.     End if
  19.  
  20. MyRSArch.Close
  21. MyRS.Close
  22. MyDB.Close
  23.  
  24. Set MyRSArch = Nothing
  25. Set MyRS = Nothing
  26. Set MyDB = Nothing
  27.  
  28.  
  29.  

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.
Mar 20 '08 #2

P: 9
Great, that makes it all easier.. thanks for your help
Mar 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.