Moving Records from one table to another

[Tracy2]

Hello

I am new to this forum and have found a lot of the problems that have been posted very interesting......

Although I ahve been unable to find an answer to a problem that I am having moving records from one table to another. I have tried using an append query but all that happens is that all records are added to the selected table. How can I move one record at a time

many thanks for any advice
Tracy2

 

[Pat Hartman]

Your append query needs selection criteria so that it selects only the rows that you want to append.

 

[Tracy2]

thanks vey much for your advice - i will try that

 

[SQL_Hell]

You also need a delete query from the table you are moving from.

Assuming you actually want to move the record rather than copy it.

 

[SQL_Hell]

Hi,

got your private message,

ok

will this command be run from a form?

do both tables have unique primary and foreign keys? If so what are they


Might be easier if you zip a copy of your database and send it to me

 

[Tracy2]

Hi

thanks very much - i hae zipped the db but it is still too large to send

basically i have four tables villa, item, room and store, i want to be able to move an item record from item or room to the store table. The item and room tables have primary keys but the store room does not. [i'm not sure if it needs one??? - i could be wron g but this table will be used just to store items, also if items are moved to the store then i also want to be able to move items from the store to room.

this command will run from a command button on the form

i hope this makes sense

i have searched through the archives of the newsgroup and have found a lot of information regarding action queries and whilst i have some knowledge of SQL and of VB i have never used either language in Access - this is where i am getting stuck - i understand the concept of select from and insert to but am not suer how to use this in an access query.

many thanks for your help

 

[SQL_Hell]

ok you need a primary key for the store table, just set up an autonumber field.


what are the fields you would like to transfer from item or room to the store?

 

[SQL_Hell]

also what are the text box names on the form for the room or item?

I am assuming the user will select the record on the form before moving, is this correct?

 

[Tracy2]

hi

The primary key in store is item reference

the fields coming from Item [this will be a sub for of room and so the records will only have to come from item] the fields i would like to transfer from Item to store are as follows

Item Reference, Room Name, Amount, Item Description, Replacement Cost

the text box names are the same as the field names and yes, the user will need to select the record before the record can be appended.

i have designed the form so that for each record there is a command button that has been set to run the append query

many thanks

 

[SQL_Hell]

Hi,

I'm working on this for you, wont be long

 

[Tracy2]

thanks very much - I have found that you can view the SQL in the design view of the query and have being trying out many different possibilities - but have gone from appending all records to 0 records!!!

does it matter that the PK in the Item table has the same name as the PK in the Store table = item reference?

once again - many thanks for your time and help

 

[SQL_Hell]

right ok, sorry for the delay had a nice long lunch break, boss is away

Firstly change your field names in your tables so they have no spaces in them


Ok put this code behind the button, delete any other code you have put in there.

'declare variables for holding data
Dim ItemRef, RoomName, ItemDes, ReplacementCost
AS STRING
Dim Amount as INT
Dim strSQLApp, strSQLDel AS STRING

'load variables from data on form
ItemRef = me.ItemReference
RoomName = me.RoomName
ItemDes = me.ItemDescription
ReplacementCost = me.ReplacementCost
Amount = me.Amount


strSQLapp = "INSERT INTO Store (ItemReference, RoomName, Amount, ItemDescription, ReplacementCost) VALUES ('" & ItemRef & "', '" & RoomName & "','" & Amount & "', '" & ItemDes & "', '" & ReplacementCost &"')

strSQLdel = "DEL FROM Item WHERE ItemReference = '" & ItemRef &"'"


'more to follow

 

[SQL_Hell]

Ok now you got all that,


are you able to change field names so they don't have spaces in them?

Are all the field names correct?

 

[Tracy2]

Hi

Yes I have been able to change the names of the fields in the Item table. The question that i have is that where do i enter this code behind the button. I am using a command button??

alos i get a compile or syntax error and the VB debugger highlights the following code:AS STRING
Dim Amount as INT

many thanks - glad you enjoyed your lunch - it's always nice when the boss is away!!

 

[Tracy2]

Hi

I have changed the code so that AS STRING is not in uppercase and have changed the variable Amount to Dim Amount as String??? and tried again - this then found an error with me.ReplacementCost - i found this was due to the fact the field name is ItemReplacementCost - i have amended the code and it now looks like this:

Private Sub Command30_Enter() - is this correct or should this be on click?
'declare variables for holding data
Dim ItemRef, RoomName, ItemDes, ItemReplacementCost As String
Dim Amount As String

Dim strSQLApp, strSQLDel As String

'load variables from data on form
ItemRef = Me.ItemReference
RoomName = Me.RoomName
ItemDes = Me.ItemDescription
ReplacementCost = Me.ItemReplacementCost
Amount = Me.Amount


strSQLApp = "INSERT INTO Store (ItemReference, RoomName, Amount, ItemDescription, ItemReplacementCost) VALUES ('" & ItemRef & "', '" & RoomName & "','" & Amount & "', '" & ItemDes & "', '" & ReplacementCost & "')"

strSQLDel = "DEL FROM Item WHERE ItemReference = '" & ItemRef & "'"


End Sub

i click the command button and i do not get any code error messages - but nothing is added to the store table????

i am sure that i am doing something stupid

can you see where i am goiung wrong - thanks for all your help - i am learning a lot believe me!!

 

[SQL_Hell]

Hi again,

Put code in the OnClick event of the command button, this will open the VBA editor and past the code there.
Does your form still work now you have changed the table names?

Ok ok a few errors in the code

here you go:

'declare variables for holding data
Dim ItemRef, RoomName, ItemDes, ReplacementCost
AS STRING
Dim Amount as INTEGER
Dim strSQLApp, strSQLDel AS STRING

'load variables from data on form
ItemRef = me.ItemReference
RoomName = me.RoomName
ItemDes = me.ItemDescription
ReplacementCost = me.ReplacementCost
Amount = me.Amount


strSQLApp = "INSERT INTO Store (ItemReference, RoomName, Amount, ItemDescription, ReplacementCost) VALUES ('" & ItemRef & "', '" & RoomName & "','" & Amount & "', '" & ItemDes & "', '" & ReplacementCost &"')

strSQLdel = "DEL FROM Item WHERE ItemReference = '" & ItemRef &"'"

' ok now the next bit

Docmd runSQL strSQLApp

Docmd runSQL strSQLDel

msgbox "The Record has been moved"

 

[Tracy2]

I have a bit of VB knowledge and you code shows how this can work - its a simple way of doing this - my only problem is i don't understand how it is working - is the data being exported [for want of a better word] to the variables and then added to the fields that have been associated with the variables??

i think i need beer now!!

 

[SQL_Hell]

Should be onclick event

like:

command30_OnClick()

 

[SQL_Hell]

Yes thats pretty much it the data is being put into separate variables, then the variables are being used for the values in an SQL INSERT INTO statement.

The delete statement works by comparing the ID number from the one in the variable and deleteing the record that matches.

Does this work now?

 

[Tracy2]

Hi

i have created new forms from the amended tables so the field names match and have created a new command button and have entered the code into the onClick event i have added the rest of the code you sent and VB has a problem with the following syntaxocmd runSQL strSQLApp Docmd runSQL strSQLDel - am i right in thinking this should go in the onClick event?