Move or append selected record then delete it

[Dave Reid]

I've read several posts that came close but I still can't seem to find what I'm looking for.

I have in a subform, a datasheet view of 'table1'

I would like to select an entire record using the record selectors on the left, and move or append this entire record to 'table2' which is built identially to 'table1'.

Upon moving the record from 'table1', it should be deleted.

I would like to perform this via Command Button.

Help in detail would be most genuinely appreciated.

TIA,
Dave

 

[jfgambit]

This should help...it shows how to open another form based on the record selector...

 

[Dave Reid]

Thanks for the reply. Downloaded your file but fail to see how to copy the selected record to another table then delete that record.

I guess I'm looking right over the answer, been one of those days

Dave

 

[dgm]

Try something like this, where:
tran2 is the table u are copying to,
transaction is the table u are copying/deleting from,
Child0 is the name of your subform (not the form itself),
tranID is the unique identifier of the record being copied/deleted,

Private Sub Command2_Click()
Dim intID As Integer

intID = Me.Child0.Form.tranID

If Not IsNull(intID) Then

strSQL = "INSERT INTO tran2 SELECT Transaction.* FROM [Transaction] WHERE (((Transaction.tranID)= " & intID & "))"

DoCmd.RunSQL strSQL

strSQL = "DELETE * FROM transaction WHERE [tranID] = " & intID
DoCmd.RunSQL strSQL

Me.Child0.Requery

End If

End Sub


The easiest way of writing the query is to make a new Append or Delete query in Access, change to SQL view and copy/paste it into your code.

 

[Dave Reid]

Hmm, ok, if tran2 is .... mumblemumblemumble....and we declare the sub...mumblemumblemumble...and the...mumblemumblemumble...but wait . . . oh ok mumblemumble mumble..........fire?????? Did he say fire?
FIRE IN THE HOLE?!?!?!?!?!?! AAAAAAAAAAAAAAAAAA!!!!!!!

EUREKA!!!! The code you proposed works, however, I didn't use this part:
"The easiest way of writing the query is to make a new Append or Delete query in Access, change to SQL view and copy/paste it into your code."

what is the purpose of that part and how would I impliment it with the other?

and by the way... thank you tremendously!!!

 

[dgm]

Ok, in Access you have Tables, Forms, Queries, Reports, etc.
If you create a new query in design view, go to the "query" menu and choose "Append" or "Delete", and then design your query.

When you have finished making your query, go to the view menu and choose "SQL View". The text you see here you can copy and paste into this part of your code:

strSQL = "INSERT INTO tran2 SELECT Transaction.* FROM [Transaction] WHERE (((Transaction.tranID)= " & intID & "))"

You'll have to change the "& intID &" part to work with your variables, but this just makes it a bit easier if you don't know the syntax for the "INSERT INTO ...FROM ..." queries, and it gives u all the table and field names. Of course u don't need to do this, it just makes it easier IMO.

If all u see above is mumblemumblemumble, then one of us needs help.

and by the way . . . "Your Welcome"

Dave <- - (that's me not u)

 

[Dave Reid]

Ah, ok, I gotcha now, you're talking about using the query builder for just that, and not necessarily saving the query as such, just using the builder as an "in house" editor of sorts?

The "mumblemumblemumble" was that of me talking to myself as I worked out your solution . . . at least that's what my wife says I sound like when I'm deeply involved in this stuff . . . just a bit of fun intended for the message board

...and we all must need help working on this sort of stuff, weather it's programming support, syntax support or beer support!

dave