By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,220 Members | 978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,220 IT Pros & Developers. It's quick & easy.

place duplicate record in 2nd table

P: 20
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!
Dec 13 '07 #1
Share this Question
Share on Google+
24 Replies


missinglinq
Expert 2.5K+
P: 3,532
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)>
Dec 13 '07 #2

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 13 '07 #3

P: 20
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?
Dec 13 '07 #4

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 13 '07 #5

P: 20
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.
Dec 13 '07 #6

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 13 '07 #7

P: 20
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.
Dec 13 '07 #8

NeoPa
Expert Mod 15k+
P: 31,660
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 :)
Dec 13 '07 #9

P: 20
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub add_Click()
  2. On Error GoTo Err_add_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "NOI form table Query"
  7.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  8.  
  9. Exit_add_Click:
  10.     Exit Sub
  11.  
  12. Err_add_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_add_Click
  15.  
  16. End Sub
  17. Private Sub delete_Click()
  18. On Error GoTo Err_delete_Click
  19.  
  20.     Dim stDocName As String
  21.  
  22.     stDocName = "NOI form table Query"
  23.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  24.  
  25. Exit_delete_Click:
  26.     Exit Sub
  27.  
  28. Err_delete_Click:
  29.     MsgBox Err.Description
  30.     Resume Exit_delete_Click
  31.  
  32. End Sub
thanks for all the help.
Dec 13 '07 #10

P: 20
sorry, ignore the code in the last message...this code is working with two command buttons
Expand|Select|Wrap|Line Numbers
  1. Private Sub add_Click()
  2. On Error GoTo Err_add_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "NOI"
  7.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  8.  
  9. Exit_add_Click:
  10.     Exit Sub
  11.  
  12. Err_add_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_add_Click
  15.  
  16. End Sub
  17.  
  18.  
  19.  
  20. Private Sub Remove_Click()
  21. On Error GoTo Err_Remove_Click
  22.  
  23.     Dim stDocName As String
  24.  
  25.     stDocName = "delete"
  26.     DoCmd.OpenQuery stDocName, acNormal, acNormal
  27.  
  28. Exit_Remove_Click:
  29.     Exit Sub
  30.  
  31. Err_Remove_Click:
  32.     MsgBox Err.Description
  33.     Resume Exit_Remove_Click
  34.  
  35. End Sub
thanks!
Dec 14 '07 #11

P: 20
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!
Expand|Select|Wrap|Line Numbers
  1. Private Sub add_Click()
  2. On Error GoTo Err_add_Click
  3.  
  4.  
  5.  
  6.     DoCmd.OpenQuery "NOI", acNormal, acEdit
  7.     DoCmd.OpenQuery "delete", acNormal, acNormal
  8. Exit_add_Click:
  9.     Exit Sub
  10.  
  11. Err_add_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_add_Click
  14.  
  15. End Sub
Dec 14 '07 #12

NeoPa
Expert Mod 15k+
P: 31,660
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 :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "delete", acNormal, acEdit
Dec 14 '07 #13

NeoPa
Expert Mod 15k+
P: 31,660
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub add_Click()
  2. On Error GoTo Err_add_Click
  3.  
  4.     Call DoCmd.RunCommand(acCmdRecordsGoToNext)
  5.     DoCmd.OpenQuery "NOI", acNormal, acEdit
  6.     DoCmd.OpenQuery "delete", acNormal, acEdit
  7.     Call DoCmd.RunCommand(acCmdRecordsGoToPrevious)
  8.  
  9. Exit_add_Click:
  10.     Exit Sub
  11.  
  12. Err_add_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_add_Click
  15. End Sub
Dec 14 '07 #14

P: 20
Thanks a lot! it works beautifully!
Dec 17 '07 #15

NeoPa
Expert Mod 15k+
P: 31,660
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 :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
  2. 'Query(ies) here
  3. Call DoCmd.SetWarnings(True)
Dec 17 '07 #16

P: 20
so my code will look like this? if so, it didn't work.
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
  2. Private Sub add_Click()
  3. On Error GoTo Err_add_Click
  4.  
  5.     Call DoCmd.RunCommand(acCmdRecordsGoToNext)
  6.     DoCmd.OpenQuery "NOI", acNormal, acEdit
  7.     DoCmd.OpenQuery "delete", acNormal, acEdit
  8.     Call DoCmd.RunCommand(acCmdRecordsGoToPrevious)
  9.  
  10. Exit_add_Click:
  11.     Exit Sub
  12.  
  13. Err_add_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_add_Click
  16.  
  17. Call DoCmd.SetWarnings(True)
  18. End Sub
Dec 18 '07 #17

NeoPa
Expert Mod 15k+
P: 31,660
Not exactly. The only code between the two new lines is the queries.
IE :
Expand|Select|Wrap|Line Numbers
  1. Private Sub add_Click()
  2. On Error GoTo Err_add_Click
  3.  
  4.     Call DoCmd.RunCommand(acCmdRecordsGoToNext)
  5.     Call DoCmd.SetWarnings(False)
  6.     DoCmd.OpenQuery "NOI", acNormal, acEdit
  7.     DoCmd.OpenQuery "delete", acNormal, acEdit
  8.     Call DoCmd.SetWarnings(True)
  9.     Call DoCmd.RunCommand(acCmdRecordsGoToPrevious)
  10.  
  11. Exit_add_Click:
  12.     Exit Sub
  13.  
  14. Err_add_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_add_Click
  17.  
  18. End Sub
Dec 18 '07 #18

P: 20
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?
Dec 26 '07 #19

NeoPa
Expert Mod 15k+
P: 31,660
Try the following after the code that does the work :
Expand|Select|Wrap|Line Numbers
  1. Call Me.ReQuery()
Dec 28 '07 #20

P: 20
nope. that didn't work :(
Jan 15 '08 #21

Expert 100+
P: 374
just ry me.requery at the end off that sub routine
Jan 15 '08 #22

NeoPa
Expert Mod 15k+
P: 31,660
nope. that didn't work :(
So what does it do? What did you notice?
Jan 15 '08 #23

P: 20
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.
Jan 16 '08 #24

NeoPa
Expert Mod 15k+
P: 31,660
No worries Shaffy. I'm pleased you got it sorted :)
Jan 17 '08 #25

Post your reply

Sign in to post your reply or Sign up for a free account.