473,323 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

form data loads from one table and saves to another

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
2 1459
PianoMan64
374 Expert 256MB
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
Great, that makes it all easier.. thanks for your help
Mar 25 '08 #3

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

Similar topics

5
by: Raffi | last post by:
Hi folks, I'm new to JavaScript and need some help. I have a form with a select field. Depending on what is selected in this field, I want to display or not display another select field. For...
2
by: nic | last post by:
Hi I am currently having problems getting two forms to behave the way I want. I have two tables Student and Application, and their respective forms. (Tables)Student has StudentID (PK) & an...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
5
by: Aspnot | last post by:
Background: I have a data entry form that is bound to a DataSet. This DataSet contains 9 tables and the form displays data from each table in textboxes, not a DataGrid. One of the tables in the...
6
by: jean.ulrich | last post by:
Hi I have a form that is not related with a table neither a query (unbound form) On this form I have a text field where the user can put a text or a number As I dont want to create a table...
13
by: kev | last post by:
Hi all, I have created a database for equipments. I have a form to register the equipment meaning filling in all the particulars (ID, serial, type, location etc). I have two buttons at the end...
5
by: BD | last post by:
I am coding with C# in Visual Studio 2005 for a database application residing on remote MS SQL Server 2005. What I want to do is open the same form but from 2 different places and only one...
1
by: QCLee | last post by:
Hello Sir, Im here again asking for help. I have a form and a subform relate to HVAC Windward Table. i have a SAVE button on the form that when clicked it saves data on the HVAC Windward Table but...
3
by: jeremy.gehring | last post by:
Hey all, OK I'm not much of a PHP programmer; but needs must as they say. I have written AJAX file upload system that uses a PERL CGI script so that a PHP script can get the progress (nifty...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.