Moving Data From One Table To Another

[Ripley]

I have got a problem! What i want to do is click a command button in a form (named command81), and it will transfer the data on the selected record in the form to another table.

The field names for the tables are the same, apart from there is another column on the second table, where i want to insert the date, which is automatically inputed as todays system date.

Can some one please write me the skeleton code for MS Access VB?

Thanks!

 

[FireStrike]

your can try using something like

strSQL = "insert into tblData " & _
"(date, data1, data2, data3) " & _
"select '" & format(date,"short date") & "','" & rcd!data1 & "', " & _
"'" & rcd!data2 & "','" & rcd!data3 & "'"

DoCmd.RunSQL (strSQL)

In this example rcd is going to be the record source where the data is coming from. the format(date,"short date") will give you todays date in the 08/04/06 type format. tblData is the table you want to insert it into. This is also assuming that tblData is all text type fields, if not, then you will not need the single quotes.

 

[Ripley]

sorry im new to this. ok, i'll rephrase the question. Could some one possibly take a few moments and write me out the full code for the following?!

I want to click the button named command81 on the form, and the record the form is currently viewing from table A, i want transfered to table B.

The fields in Table A are: Name, Telephone Number, Date Added
The fields in Table B are: Name, Telephone Number, Date Added, Date of Promotion

The field "Date Of Promotion" in table B i would like as automatically generated as the current system date.

 

[ghudson]

Based on what you are trying to do...I have to state that your database is not normalized. Search the forum on the keyword "normalize" or "normalization" and read and understand what it means to have your data structure normalized.

All you need to do is update the "Date of Promotion" field in Table A [once you have added it to Table A and to your form to allow the user to update the date in that field. There is no need to create a separate table to hold redundant information with the exception being the Date of Promotion.

 

[Ripley]

i missed some information out, i want the data from table A to be deleted from it when it is transfered to table B, sound funny i know but that is what i need!

 

[dwilliams]

Try this

I have got a problem! What i want to do is click a command button in a form (named command81), and it will transfer the data on the selected record in the form to another table.

The field names for the tables are the same, apart from there is another column on the second table, where i want to insert the date, which is automatically inputed as todays system date.

Can some one please write me the skeleton code for MS Access VB?

Thanks!

Hi
I use this code to write data from an unbound form to a table. You will need to declare the recordset and database variables first.
Cheers
David


MyRS.AddNew
MyRS![Balence] = MyBal
MyRS![Credit] = [Forms]![FrmTransaction]![Credit]
MyRS![Debit] = [Forms]![FrmTransaction]![Debit]
MyRS![Transaction Date] = [Forms]![FrmTransaction]![Date]
MyRS![Category] = [Forms]![FrmTransaction]![Category]
MyRS.Update

 

[RV]

The fields in Table A are: Name, Telephone Number, Date Added
The fields in Table B are: Name, Telephone Number, Date Added, Date of Promotion

What you should check upon first is whether each and every combination of Name, Telephone Number and Date Added in Table A is unique.

To guarantee unique rows, you always need to include a unique identifier (primary key) in your tables.
For Table B, add an autonumber column NameID.

And don't use Name as object name as it is a reserved word in Access and is likely to cause issues...

RV

 

[Ripley]

hmm all i get is an error message saying "Can't find the field "|" reffered to in your expression"

 

[RV]

hmm all i get is an error message saying "Can't find the field "|" reffered to in your expression"

Would be helpfull to post your expression

RV

 

[Ripley]

ok here is my expression. I have remaned the name fields as follows:

Table 1: Fields as follows: (form where button is located)

Name1
Telephone Number
Date Added


Table 2: Fields as follows:

Name1
Telephone Number
Date Added
Date of Promotion

I want to click the button (command81) and the data currently been viewed in the form where the button is located, i want transfering to table 2, and the Date of Promotion is set to the current system date.

code as follows that i have:

Private Sub Command81_Click()
Table2.Recordset.AddNew
Table2.Name = Forms.Table1.Name1
Table2.[Telephone Number] = Forms.Table1.[Telephone Number]
Table2.[Date Added] = Forms.Table1.[Date Added]
Table2.[Date of Promotion] = IsDate(Now)
End Sub

 

[RV]

For table 2, columns should be:

NameID (data type Autonumber, this is your primary key)
Name1 (change this into something more obvious)
Telephone Number
Date Added
Date of Promotion

Private Sub Command81_Click()
Dim strSQL As String

strSQL = "INSERT INTO Table2 (Name2, [Telephone Number], [Date Added], [Date of promotion] VALUES ('" & Me.Name1 & "', '" & Me.[Telephone Number] & "', # " & Me.[Date Added] & " #, Date)" 

DoCmd.RunSQL strSQL

You'll have to adapt the code to your object names yourself.

RV

 

[Ripley]

ahh great.....thanks

 

[aromero]

Hello Ripley,

Silly question....I have the same problem posted in this thread, however, being new to access I can seem to figure out where to place this code so that I do not get an error message well at the same time be able to link it to a button. If you can write some steps as to where you placed the code and how you linked it into a button located on a form I would greatly appreciate it!

Thanks!