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
'--------------------------------------------------------------------------
'--------------------------------------------------------------------------
2 1459
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. -
-
Dim MyDB as DAO.Database
-
Dim MyRS as DAO.Recordset
-
Dim MyRSArch as DAO.Recordset
-
-
Set MyDB = CurrentDb()
-
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
-
-
Set MyRSArch = MyDB.OpenRecordset("SELECT * FROM SESSION_CLX",dbOpenDynaset)
-
-
If Not MyRS.EOF Then
-
MyRSArch.Add
-
MyRSArch!simcode = MyRS!simulatorCode
-
MyRSArch!start_date = MyRS!txtStartDate
-
'.... 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
-
-
-
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.
Great, that makes it all easier.. thanks for your help
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |