Help with Copy/Paste in subform

[Max Yaffe]

I'm having a problem copying and pasting records in a subform.

Here's the context -- I have a Purchase Order form with a subform
containing line items. The PurchaseOrder table's primary key is PO.
The LineItem table's primary key is PO+LineNo.

I can select a line item record on the subform and using the menu or
^C do a copy. The line item record is copied.
However, when I go to the end and do a paste I run into the problem
that the line number has also been copied and cannot be pasted since
the key already exists.

So... I need to modify the line number in the paste buffer before the
data is pasted.

I've tried putting code into the BeforeInsert() and BeforeUpdate()
event handlers without success.

What is the best way to do this.

Thanks,
Max

 

[Max Yaffe]

I don't think that will solve the problem. My users want Access to
work like a spreadsheet, e.g. highlight a line on one PO, move to a
different PO, paste the line. They'd also like to highlight a group
of subtable records and paste the entire group.

I think the only way I can do it is to eliminate the line number from
the subtable index but that causes other problems.

Any further ideas?

 

[Marshall Barton]

You may not be able to do all that with an Access form.
Access forms, even in datasheet view are not at all like an
Excel spreadsheet.

One thing to note is that Copy/Paste only operates on the
visible form controls. Maybe you can make the lineNum text
box invisible???

 

[Max Yaffe]

Thanks, I'll try that.
I have been able to use Copy/Paste in the past but the tables were
Access/Jet tables. In Jet, the subtable didn't have a unique line
number requirement. New I'm trying upsize the database & app to SQL
Server but SQL Server requires PO+LineNumber as a primary key.
Therein lies the rub.
Max

 

[Max Yaffe]

A further resolution:
I can (almost) get where I need to go by making the PO-LineNo a
secondary, non-unique key in the SQL table. I added an new identity
column which is used as the primary key. I can still create a query
based on PO=something and now this query is updatable. Line numbers
can be duplicated. Then, I'll create another button which explicitly
resequences the line numbers.

Whew!