Moving Records from one table to another

err sorry its

docmd.runSQL strSQLApp
docmd.runSQL strSQLDel


Ummm I'm gonna need a litre of beer after this I think, where are you from?
 
i am sorry to cause you grief - i ahve learnt a lot today and have been force to remember my VB code from Uni - i would like to learn to use vba in access as i am sure that a lot of tasks could be simplified

i am from Brighton - it's a cool town - there are lots of pubs adnd clubs and lots going on in the summer

i will try the amended code
 
Ah cool I used to go there with one of my ex's, we used to stay in the Thistle Hotel and go out to the Honey club, then drink champayne on the beach.

I'm a big fan of Brighton, it's abslolutley buzzing in the summer.
 
hi - yes summer is good in brighton even if you are just sitting on the beach drinking beer - i go to the honey every other saturday night!!!

well we are getting somewhere the record was appended and was added to the store table - i got an SQL error message though = invalid SQL statement expected DELETE INSERT PROCEDURE SELECT OR UPDATE??? - ok i ahve amended code to read = strSQLDel = "DELETE FROM Item WHERE ItemReference = '" & ItemRef & "'"

the error i get now is data type mismatch in criteria expression? if i choose to debug the debugger highlights a problem with the following line of code:DoCmd.RunSQL strSQLDel??

it is adding the records but it is not deleting them from the original table

any ideas?
 
Last edited:
Hi

Its 7.30pm now and i think its time to go - its still not quite working - but i really appreciate your help and time with this today - i ahve certainly got a lot further with it - i shall plug away tomorrow

have a good night and thanks again
 
Morning,

how's it going? Well I had three pints last night, which I think is more than a litre.
How was your night?

I think you just need to add this:

Delete * from item (you don't need the * with SQL Server)

try that,
to be honest my access knowledge is a bit rusty, as I do SQL server stuff nowadays.
 
Good Morning

I had several pints last night..... and a 2 hour driving lesson this morning! Now its back to work - i tried your suggestion - it now throws up this errror: data mismatch and the debugger highlights this bit of code = DoCmd.RunSQL strSQLDel

once it gets past this line all i need it to get to is the message!!!!!!!!!!!

as i am sure you will be i will be glad to see the back of this project!
 
Ok maybe the Item reference is a number, change the ItemRef variable to an integer.

and change the SQl to read:

"DELETE * FROM item WHERE ItemRef = " & ItemRef"
 
aaaaaaaaaaagggggggggghhhhhhhhhh

ok so this is how the code looks now

Private Sub Command30_Click()

'declare variables for holding data
Dim RoomName, ItemDes, ItemReplacementCost As String
Dim Amount As Integer
Dim ItemRef As Integer


Dim strSQLApp As String
Dim strSQLDel As Integer

'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 = "DELETE * FROM Item WHERE ItemReference = '" & ItemRef & "'"

DoCmd.RunSQL strSQLApp

DoCmd.RunSQL strSQLDel

MsgBox "The Record has been moved"

End Sub


for this line i get a type mismatch error = strSQLDel = "DELETE * FROM Item WHERE ItemReference = '" & ItemRef & "'"

if i change itemRef to ItemReference - i still get the same error!!!
 
are the data types for itemreference the same in both tables?
 
Ok got it:

here is is:

Dim strSQLDel As Integer

it should be a string
 
yes i have double checked.......both are auto-number
 
ok something very strange happened - i get a dialogue box asking for a parameter - i enter the item ref # of the record that is to be moved and then deleted - i then get a message advising that all records will be deleted? is this something to do with itemRef and itemReference?
 
Hmm it looks like the itemref variable is not being filled, so it can't verify what its trying to delete.

output the variable before you do the delete:

like this

msgbox "" &ItemRef
 
ok so - is this the correct place to put the code:
DoCmd.RunSQL strSQLApp
MsgBox "" & ItemRef
DoCmd.RunSQL strSQLDel

now this is what happens

i get the message you are about to append 1 row - i then get a message box with just the ref numebr in it an option to click ok - when i click ok i get another dialogue box that requests to enter parameter value = itemRef - i enter the ref number and click ok and then get the same message as before - you are about to delete all records???......

apart from me driving you mad - how is your day going - what do you do by the way?
 
i have tried changing "DELETE FROM Item Where ItemReference = '" & ItemRef & "'" and get the data type mismatch error and the debugger highlights the DoCmd.RunSQL strSQLDel

is this because strSQLDel is string and the data item it is dealing with is an integer?
 
"DELETE FROM Item Where ItemReference = " & ItemRef


???
ken
 
SQL_Hell said:
Ok maybe the Item reference is a number, change the ItemRef variable to an integer.

and change the SQl to read:

"DELETE * FROM item WHERE ItemRef = " & ItemRef"


Yeah thats what I said, have you tried this Tracy?
 
Hello - thanks very much for that - that has moved the record to the correct table and i gt a message telling me it is only deleting 1 record but will not delete the record due to key violations? not sure what is going on there??

many thanks for your help
 
Hello again SQL_Hell - yes i ahve tried this and i now get the message you are about to delete 1 record but it will not delete due to key violations??
 

Users who are viewing this thread

Back
Top Bottom