Where am i going wrong with this?

[owens2k]

im trying to achieve a command button which copies a record from one table to another and then deletes the original once it has been copied. The code i have used is as follows:
-------------------------------------------------------
Dim strSQL As String
strSQL = "Insert Into tblresults Select TOP 1 * from tblfixtures"
DoCmd.RunSQL strSQL

strSQL = "Delete TOP 1 * from tblfixtures"
----------------------------------------------------------

does anybody know where i am goin wrong with the "delete" bit because that is the part whihc does not work!!

 

[JeremyNYC]

Several issues here. The most fundamental of these is that there's probably no need to store these records in different tables. Unless you've got a massive dataset, it would probably be better to add a field to the original table that gets used to indicate which set of data this record goes with. It might be a yes/no field with a creative name, or a text or numeric field filled with one of two (or more) values, or it could even be a date field, indicating when the status changed (null would indicate it's still in the first category).

This is one of the fundamental rules of data normalization. Do some googling on the subject or check out Paul Litwin's article Fundamentals of Relational Database Design. There's a copy on my website.

The next issue is that TOP doesn't really make much sense without an ORDER BY clause, as records in a table in access are not stored in any particuar order. It's not possible to know beforehand what record will turn up using TOP unless you sort.

Also, I'm not sure you can delete records from the TOP structure. If you are going to delete from one table after inserting into the second one, I would suggest finding the primary key of the record you want, storing that PK in a variable, doing the insert based on that PK, and then doing the delete based on that PK.

Hope this helps.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.