Transfer Data and then store it 1

[erictheman]

Hello,

I just have a question. Say if you had 6 fields. Exp. SSN, ID, CCP on the top of the form and name, address, notes, gender on the botttom. And we make a history button that as soon as we click it, it will clear out those fields (name, address, notes, gender) and it will take that same data and transfer it to a another table called the history table. But those top fields will stay the same and will not be transfered. Thank you very much.

 

[BobWman]

What is the question

 

[erictheman]

Sorry. The question is how do you do this? That you take those fields and store it to other table. And clear them out from that record. Do you do this throught VBA?

 

[BobWman]

Probably the best way would be to write a stored procedure and call it from your VB program. However, I will offer a simpler solution.

Let's say we have this data:
SSN = "111-11-1111"
ID = 34
NAME = "John Doe"
ADDRESS = "123 E. Main"

When the button is clicked, use an sql like this to open an empty recordset in you history table:
strSql="Select * from history where 1=0"

After opening the recordset, use AddNew to add the name, address, etc. to thje history table. I haven't included any code for this because I don't know if you are using DAO, ADO, etc.
.Addnew
!NAME = txtName.Text ' Contains John Doe
!ADDRESS = txtAddress.Text
.Update

Then clear your text boxes. Also, clear the fields in your original table.
!NAME = ""
!ADDRESS = ""
. Update

I know this is incomplete, but it's the best I can do with the info. given.

Hope it helps.

 

[erictheman]

How do call a sql to open a empty recordset? What's the code and were do you do it? In the history button?
Thank you.

 

[BobWman]

If you open a recordset using this query

strSql="Select * from history where 1=0"

it will return an empty recordset, because "where 1=0" is always false.

Using ADO, the code would be something like:
rs.Open strSql, cnnMyConnection

You would open the recordset after defining strSql and before the AddNew.

 

[erictheman]

Hi,

I'm using DAO. So some of the ADO codes that you gave me bring back sytax errors. Is there a difference? Take note I'm using Access 97. But I did manage to use my update button and transfer the data. As soon as I click it the fields go into the history table. Here's the code I used.

Dim dbs As Database

' Finds were's the database location

Set dbs = OpenDatabase("P:\Human Resource Services\Employment Services\Access Project\database Project.mdb&quot

' Select records

dbs.Execute " INSERT INTO History " _
& "SELECT Employee Name, Startdate, PostDate, Notes, " _
& "FROM [Accounting Service - General Accounting];"

dbs.Close

End sub

I haven't writed the codes to clear the fields out yet.
But there is a problem. When I click the button it takes all the records (not fields) from the Accounting table to the History table. I just want that one record to be transfered. Like record 1 of 1, when I click the button it just takes record 1 data and leaves out record 2, 3, 4, etc. How would you do that? If possible using this code. Also one more question. This may sound stupid but what's the code that you use to clear out the fields? I use yours and it didn't work. Thank you very much for all your help.

 

[BobWman]

You'll need a Where clause in your execute query to limit the records transferred to one. To clear the old table, you could use an update query with a similar Where clause. You need a unique index field in each table, which should be used in the where clause.