Question Move selected record from one table to another (1 Viewer)

[DanJames]

I need a button on a form, that when clicked on copys the selected record from table [tblPersonData] to [CancelledClients] and when this is successfull to delete the record from [tblPersonData].

There is a dropdown list on the form where you select a name from [tblPersonData] the details come up in the text boxes and the selected client is the one I want to copy/delete. If I can have VBA code to do this it would be an advantage.

Thanks alot in Advance, Dan.

 

[Scooterbug]

I need a button on a form, that when clicked on copys the selected record from table [tblPersonData] to [CancelledClients] and when this is successfull to delete the record from [tblPersonData].

Why not just have Yes/No field to denote who is cancelled and who isn't?

There is a dropdown list on the form where you select a name from [tblPersonData] the details come up in the text boxes and the selected client is the one I want to copy/delete. If I can have VBA code to do this it would be an advantage.

Not sure what you are looking to do. Based on your other post, I am assuming that you are storing the current client that the person cleans for in the tblPersonData. Check your other thread...and go with the Junction Table route. This way, you can select if the relationship is the current cleaner/client by adding a field in the Junction table called Current. Make it a Yes/No field. When a cleaning person changes clients, you mearly uncheck the Current field to denote a prior relationship and add the new relationship and mark that record as Current.

 

[DanJames]

Eventhough the checkbox situation would be easier, our company needs to have all cancelled people in a certain table because it causes more problems for other code in the database, if it is a YES/NO field. I am sure it is possible and would owrk if you could copy data into another table?

Thanks, Dan.

 

[Scooterbug]

The problem with moving data around like that is that you are, in essence, storing duplicate data in multiple tables. This violates the rules of normalization. What you want to do is certainly possible, but I can gaurentee you it will bit you in the behind at some point down the road. Good table structure the the key to a good database.

But, to do what you are looking to do, you would simply run an append query and then a delete query. You can either build the sql in code and execute it, or build the queries in the query builder, save it, then run it via code. Something like this:

dim sAppendSql as String
dim sDeleteSql as String
 
sAppendSql = "INSERT INTO tblCancelledClients.* " & _
                   "SELECT tblPersonData.* FROM tblPersonData " & _
                   "WHERE tblPersonData.YourPrimaryKey = " & _
                    me.PrimaryKeyField
 
sDeleteSql = "DELETE TblPersonData.* " & _
                  "WHERE tblPersonData.YourPrimaryKey =" & _
                    me.primarykeyfield 
 
currentdb.execute sInsertSql
currentdb.execute sDeleteSql

*Note that is air code....I dont normally write out sql statements...I generally build what I need in the query builder and then copy over the sql into code if needed.