Copy a record and modify 2 fields at the same time.

[ScottyJones]

Hi all,
So I have a query of a table that has data that is imported from an external system. This table has a Primary Key that is also imported from the other system. (so I can update it to whatever I want)

From the query I am displaying a form that has the few records of that query. At times I need to copy a record and add it the same table (display in the same form just like Copy / Paste but I want to be able to change 2 fields the PK called "Barcode" and another one called "TB Pallet". All I need to do is update both fields by appending an "A" at the back of them, but I want to be able to do this during the copy/paste function.

This is what I have so far:

With DoCmd
.RunCommand acCmdSelectRecord
.RunCommand acCmdCopy
.RunCommand acCmdRecordsGoToNew
.RunCommand acCmdSelectRecord
.RunCommand acCmdPaste
End With
' If I end it here, it runs fine and does exactly what I want, but I don't want the user to have to remember to update the 2 fields'
Set [TB Pallet] = [TB Pallet] & "A"
Set [Barcode] = [Barcode] & "A"
'This fails with "Compile error: Invalid use of property"'

I guess I am not calling the field correctly or it could be that the paste function already violated the Primary Key so it won't change focus to update the fields..... ???

Any help would be appreciated..

Scott

 

[Mark_]

If these are fields on the screen and the user is "Saving" the record, you would want to reference the controls on the screen.

NOT

Set [Barcode] = [Barcode] & "A"

BUT

Me.Barcode = Me.Barcode & "A"

The user can then do any other edits needed prior to saving.

 

[Ranman256]

Or run an append query and alter the values in the query.

 

[ScottyJones]

Mark_:
Thanks, That works perfectly.
Why I forgot about the "Me" tag I don't know {smh}.....
Anyway it works now.

Ranman256 - That would work but as Mark_ deduced I have the user making two more edits prior to closing the form.

Thanks all for the help!!