 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / March 2008copy a record and add a revision number
I have a form (Purchase Order) with a sub-form (Parts). Sometimes our customers may request a change so we need to revise the Purchase order. What I need is to copy the Purchase Order ans subform. I have done this using a command button, record operation, duplicate record. This works fine, I then need to ammend the Purchase Order number from xxx to xxx.1, to indicate a revision, but as soon as I add .1 the sub-form breaks its' link (the xxx PO number) and I loose the information of the subform. I can understand why this is happening but how can I update the new PO number on the Sub-form at the same time as the form? Sounds like you are using the PO Number to relate records. If the value of a field can be changed, it should not be a primary key or used as the one side of a relationship. You might try add an Autonumber field to your table and using that as the relationship field. It will be created when you create a new record. Then you can change the PO Number without it affecting your relationships.  SignatureDave Hargis, Microsoft Access MVP > I have a form (Purchase Order) with a sub-form (Parts). Sometimes our > customers may request a change so we need to revise the Purchase order. What [quoted text clipped - 5 lines] > this is happening but how can I update the new PO number on the Sub-form at > the same time as the form? Yes, I was using the PO Number as the primary key as ideally it should be unique. Logically though this doesn't allow me to make the duplicate and add a revision. I will add an auto-number as you suggested. It will then be down to the User to avoid using the same number, or do you know how I can alert the User if they try to save a duplicated PO number? A message should be enough to avoid this happening. Thanks for your advice so far. > Sounds like you are using the PO Number to relate records. If the value of a > field can be changed, it should not be a primary key or used as the one side [quoted text clipped - 13 lines] > > this is happening but how can I update the new PO number on the Sub-form at > > the same time as the form? I have changed the primary key to an auto-number but when I duplicate I am loosing the detail in the subform. Also once I have closed the form and re-open it, I can no longer make the duplicate, I get the message "The command or action 'PasteAppend' isn't available now." > Sounds like you are using the PO Number to relate records. If the value of a > field can be changed, it should not be a primary key or used as the one side [quoted text clipped - 13 lines] > > this is happening but how can I update the new PO number on the Sub-form at > > the same time as the form? When you changed your primary key, you lost some relationships. You will need to add change the child table's foreign key to match the new primary key. Don't forget to change the linking fields in the subform control. As to checking for duplicates, you can use the Before Update event of the control where you enter the PO number. It will check for a duplicate number, warn the user, and cancel the update: If Not IsNull(DLookup("[PONumber]", "POTable","[PONumber = """ & Me.txtPONumber & """") Then MsgBox "This PO Number is already in use" Cancel = True End If SignatureDave Hargis, Microsoft Access MVP > I have changed the primary key to an auto-number but when I duplicate I am > loosing the detail in the subform. Also once I have closed the form and [quoted text clipped - 18 lines] > > > this is happening but how can I update the new PO number on the Sub-form at > > > the same time as the form? > I have a form (Purchase Order) with a sub-form (Parts). Sometimes > our customers may request a change so we need to revise the [quoted text clipped - 6 lines] > can understand why this is happening but how can I update the new > PO number on the Sub-form at the same time as the form? Just copying the main record is never going to create the new subform records. What you need to do is copy the 1 record from the form to a temporary table, then copy the subform records to a temporary table, update the po numbers in both tables, copy the changed records back to the respective permanent tables and then clear the temporary tables, requery the form and go to the new record number. It will be a lot easier if you move the PO Revision to a separate column in the main table and sub-table, because if your po number is already 12345.3 you want to change it to 12345.4, and you then have to get the .3 value, remove it from the current value, add .1 and replace in the string. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.