place duplicate record in 2nd table

[shaffy08]

I'm using a command button to duplicate a record and I would like to place the duplicated record in a different table, is it possible if so, how i can achive that?

Thanks for your help!

[missinglinq]

You could do this with an Append Query, but unless you intend to delete the record from the original table after copying it to the new table, you'd be in gross violation of normalization!

Welcome to TheScripts!

Linq ;0)>

[NeoPa]

Consider what you need.
There are situations (backup etc) where normalisation issues don't count.
Is your situation one of them? Only you know at this point, but it's an important issue to consider nevertheless.

[shaffy08]

place duplicate record in 2nd table

thanks for your reply...
Ok, i changed the command button to a check box (because i could only figure out how to do an append query with a check box). How do i delete the record from the first table after duplicating it? that is without running another append query...is it possible?

[NeoPa]

The usual (and probably easiest) way is to create and run a DELETE query specifying the Primary Key of the record in the WHERE clause.

[shaffy08]

thanks for the reply...I did create a delete query also, but i would like to some how combine both quries or use a comand button to run both queries at the same time...i'm really new at all this and even access for idiot book isn't much help on this matter....

I also placed a command button on the form and would like to run Both queries once the button is clicked...and i have no idea how to do it...i'm furitlessly playing with the code...any help will be great.

[NeoPa]

Have the code to create and run the APPEND query in the same routine as the code to create and run the DELETE query.
I can't go into much more detail at this time as I have so few details of your situation.

[shaffy08]

Although i'm not using it for archiving purposes, but what i'm trying to do is pretty much same as archiving. i have one table which gets information from two forms....there is a reason for having two different forms instead of just one.

1st form is filled and printed out at the beginning of a project to obtain storm water permit.

2nd form is filled out after some time (sometime project is 10 year long) and print out to terminate the permit.

I have also queries and reports to track when an annual payment is due (each project has different billing date) and send email to project managers reminding them about payment.

Once the process of terminating the permit is started, i would like to move that project information to another table. As of right now, i have two command buttons on my notice of termination (NOT) form, 1 to run append query and the other one to run the delete query. I would like to have just one command button which does both. To accomplish this, i believe i'm going to need a micro, right? I have only taken one programming class many many years ago and hence don't know how to write a micro. Any help with this will be greatly appreciated.
Please let me know if you need more information.

[NeoPa]

I wouldn't recommend using macros at all.
Post in here the code behind the two individual buttons and we'll see if we can't design you a "One size fits all" button that does the whole job.

By the way, notice Linq's comment was related to making copies WITHOUT then deleting the original. There is no such situation here, so you can relax on that score :)

[shaffy08]

i'm not really sure what is going on...one minute everything is working ok, the next minute i get the following error:


Microsoft office Access can’t append all the records in the append query.
Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and it didn’t add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 records(s) due to validation rule violations.

Weird thing is that I get this message when I hit delete command, not append. Its copying the data just fine.

here is the code:

Private Sub add_Click()
On Error GoTo Err_add_Click

    Dim stDocName As String

    stDocName = "NOI form table Query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_add_Click:
    Exit Sub

Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click
    
End Sub
Private Sub delete_Click()
On Error GoTo Err_delete_Click

    Dim stDocName As String

    stDocName = "NOI form table Query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_delete_Click:
    Exit Sub

Err_delete_Click:
    MsgBox Err.Description
    Resume Exit_delete_Click
    
End Sub

thanks for all the help.

[shaffy08]

sorry, ignore the code in the last message...this code is working with two command buttons

Private Sub add_Click()
On Error GoTo Err_add_Click

    Dim stDocName As String

    stDocName = "NOI"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_add_Click:
    Exit Sub

Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click
    
End Sub



Private Sub Remove_Click()
On Error GoTo Err_Remove_Click

    Dim stDocName As String

    stDocName = "delete"
    DoCmd.OpenQuery stDocName, acNormal, acNormal

Exit_Remove_Click:
    Exit Sub

Err_Remove_Click:
    MsgBox Err.Description
    Resume Exit_Remove_Click
    
End Sub

thanks!

[shaffy08]

i got it to work with one command button with the following code, but it doesn't work if i'm on the record which needs to be moved and deleted....i have to move to the next record and then perform the action.

for example, if i check box on record one to append and delete, and press the command it doesn't work, but if i move to the next record and then press the command button, it works just fine. Is there a way around that? Thanks for all the help!

Private Sub add_Click()
On Error GoTo Err_add_Click

  

    DoCmd.OpenQuery "NOI", acNormal, acEdit
    DoCmd.OpenQuery "delete", acNormal, acNormal
Exit_add_Click:
    Exit Sub

Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click
    
End Sub

[NeoPa]

It looks like you've already found the problems with your first couple of posts ;)
I can't check the queries (NOI & delete) that you've created and used as you use them by name rather than by executing the SQL. This should not be a problem, from what you say they are both working correctly anyway.
I would guess your latest problem is that the current record is locked by the form.
I'm not sure how one would move from one record to another (programaticall y) in a form I'm afraid.

PS. I think your "delete" query line should probably be changed to :

DoCmd.OpenQuery "delete", acNormal, acEdit

[NeoPa]

I haven't got your setup to test but try this code to see if it gets around your problem.
Be careful and make sure it doesn't move the wrong record though. It's hard to know exactly what's what your end so this may not be correct, but give it a go.

Private Sub add_Click()
On Error GoTo Err_add_Click

    Call DoCmd.RunCommand(acCmdRecordsGoToNext)
    DoCmd.OpenQuery "NOI", acNormal, acEdit
    DoCmd.OpenQuery "delete", acNormal, acEdit
    Call DoCmd.RunCommand(acCmdRecordsGoToPrevious)

Exit_add_Click:
    Exit Sub

Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click
End Sub

[shaffy08]

Thanks a lot! it works beautifully!