Moving Records [RESOLVED]

[Animelion]

I am using Microsoft Access 2003. This is a vba question. I have 2 tables with identical structures. How can I easily copy the 5th record in one table and make it a new record in the other table ?

Thanks for the help,
Bryan

[RobDog888]

You can execute an Append Query or just execute via VBA a INSERT INTO query which will select th desired record from the source table and insert it into the destination table.

INSERT INTO Table1 VALUES (SELECT * FROM Table2 WHERE Field1 = 5)

Something like that. I just free typed it in the reply box without testing.

[Animelion]

Thanks but either I am entering it wrong or it just plain doesn't work.

I made a command button with the following code under the click event.

INSERT INTO tblNames_Storage VALUES (SELECT * FROM tblNames WHERE Field1 = 5)

*when you have Field1 could I put in ID and then just name the ID# that I want to move ?

** what if there are multiple instances.

Thanks for any clarification you can give.

[Hack]

Thanks but either I am entering it wrong or it just plain doesn't work.

I made a command button with the following code under the click event.

INSERT INTO tblNames_Storage VALUES (SELECT * FROM tblNames WHERE Field1 = 5)

*when you have Field1 could I put in ID and then just name the ID# that I want to move ?

** what if there are multiple instances.

Thanks for any clarification you can give.

First, are your records number 1 through X using the Field1 field? If so, then you should have gotten the 5th record, and your second question (about multiple instances) would be moot.

What did you get when you ran that code?

[RobDog888]

Field1 = 5 is where you would identify the record to be copied. If its Field1 value is equal to 5 or whatever it is then that record would be the one that gets copied.

[Animelion]

Thanks rob, but when I entered it in it doesn't run. It's not recognizing it as anything at all. It just highlights the word insert and ask me to debug. ~Thanks Again

[RobDog888]

Can you post your code and give some info on it?

[Hack]

Thanks rob, but when I entered it in it doesn't run. It's not recognizing it as anything at all. It just highlights the word insert and ask me to debug. ~Thanks Again

What is the specific error message and how are you executing your INSERT statement?

[salvelinus]

Sorry if this sounds dumb, but you aren't actually using "Field1" as the fieldname, are you? If so, use whatever field name is correct.
One problem you may have is that records in a table aren't guaranteed to be in any particular order (unless you always want the 5th record, regardless of what it is).

*when you have Field1 could I put in ID and then just name the ID# that I want to move ?
** what if there are multiple instances.

If the ID field is a primary key, it will copy just that one record. If there are multiple instances, it's not a primary key, and will move all records with a matching ID field.
You could also do a SELECT INTO query; that way if you didn't have the second table, it would be created for you.

[Animelion]

Thanks again for all the help. Here is the code I am using

Option Compare Database
Option Explicit
 
Private Sub cmdTable1_to_Table2_Click()
Dim strMove As String
strMove = lstTable1.Column(0, lstTable1.ListIndex + 1)
INSERT INTO tblTable2 VALUES (SELECT * FROM tblTable1 WHERE Last_Name = strMove)
lstTable1.Requery
lstTable2.Requery
End Sub

Sorry for the delay in response time, but internet usage is limited here.

[salvelinus]

Try something like (new code in red):

Thanks again for all the help. Here is the code I am using

Option Compare Database
Option Explicit
 
Private Sub cmdTable1_to_Table2_Click()
Dim strMove As String[COLOR=DarkRed], strSQL AS String[/COLOR]
strMove = lstTable1.Column(0, lstTable1.ListIndex + 1)
[COLOR=DarkRed]If strMove <> "" Than[/COLOR]
    strSQL = "INSERT INTO tblTable2 VALUES (SELECT * FROM tblTable1 WHERE Last_Name = [COLOR=DarkRed]'" & strMove & "')"
    DoCmd.RunQuery strSQL[/COLOR]
    lstTable1.Requery
    lstTable2.Requery
[COLOR=DarkRed]Else
    MsgBox "No value selected!"
End If[/COLOR]
End Sub

There are other ways, but this is a quick one. Simply writing an INSERT statement doesn't do anything (it was also incorrect). You may have to SetFocus to the listboxes to requery them.

[Animelion]

Thanks for the tip, I will try it tonight. One other question though, is there a way to delete all of those records instead of moving them ?

[Animelion]

I just tried your new code, however docmd.RunQuery is not an option for some reason. It doesn't seem to be recognized. Any suggestions ?

[salvelinus]

Do you have references set? Are you using DAO or ADO? DAO is faster if your app is just Access. I've never used Access 2003, don't know why you wouldn't have DoCmd. I did make a mistake; it should be DoCmd.RunSQL, not RunQuery.
To delete, just use a DELETE statement instead of an INSERT statement.

[Animelion]

The code is now working, however..... It ask if I want to append the record to the new table ? is there a way to supress this or is it just something that can't be bypassed ?

[salvelinus]

To turn off most warnings, there are two ways I know of. Somewhere (don't know Access 2003) you should have a setting under Options to turn them off. The VBA way is to use code - DoCmd.SetWarnings = False. Remember to set it back to True afterwards.
This won't trap for errors - you'll still want to do that. But it'll stop annoying warning messages.
Why not just turn them off altogether? I have users that want them. Also, the Options setting has to be done on every install of Access. Turning them off on my development machine doesn't affect the user's machine - unless I use code.

[Animelion]

Thanks a bunch, the docmd.setwarnings worked perfectly.

[Animelion]

One last question

How can I locate a record where Field1 = "27381284". After that I want to change Field3's value of that record to "Sent".

Thanks again, ~Bryan

[DKenny]

use the following SQL syntax

UPDATE YourTable SET YourFieldToUpdate = 'Sent' WHERE YourOtherField = '27381284'