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]

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 (programatically) 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!

[NeoPa]

No problem. Glad I could help.
By the way, when running queries from the code it's often considered neater to hide any query warnings that indicate how many records are deleted / copied etc. If you want to include that then try :

Call DoCmd.SetWarnings(False)
'Query(ies) here
Call DoCmd.SetWarnings(True)

[shaffy08]

so my code will look like this? if so, it didn't work.

Call DoCmd.SetWarnings(False)
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
 
Call DoCmd.SetWarnings(True)
End Sub

[NeoPa]

Not exactly. The only code between the two new lines is the queries.
IE :

Private Sub add_Click()
On Error GoTo Err_add_Click
 
    Call DoCmd.RunCommand(acCmdRecordsGoToNext)
    Call DoCmd.SetWarnings(False)
    DoCmd.OpenQuery "NOI", acNormal, acEdit
    DoCmd.OpenQuery "delete", acNormal, acEdit
    Call DoCmd.SetWarnings(True)
    Call DoCmd.RunCommand(acCmdRecordsGoToPrevious)
 
Exit_add_Click:
    Exit Sub
 
Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click
 
End Sub

[shaffy08]

Thanks! it works great. Is there a way to move automatically to next record after deletion...as of right now, it shows #delete in the fields?

[NeoPa]

Try the following after the code that does the work :

Call Me.ReQuery()

[shaffy08]

nope. that didn't work :(

[PianoMan64]

just ry me.requery at the end off that sub routine

[NeoPa]

nope. that didn't work :(

So what does it do? What did you notice?

[shaffy08]

So what does it do? What did you notice?

it works now :-) I was placing it at the wrong place - I was placing it after the code that hide the warring message instead of right after the code that runs the delete query.

thanks for the help.

[NeoPa]

No worries Shaffy. I'm pleased you got it sorted :)