copy a record and add a revision number

[AnnieJ]

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?

 

[Klatuu]

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.

 

[AnnieJ]

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.

 

[AnnieJ]

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."

 

[Klatuu]

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

 

[Bob Quintal]

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?

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.