Copy/Paste Records Displayed with a Tab Control. 1

[jasonmac]

Hello everyone. I'm using access '97 for this.
I have a form that uses a tab control to display a large amount of data. Sometimes 2 records, due to customer requests for quotes, are very similar. To save time for the users and eliminate reentering data I am trying to allow the user to copy and paste a record. I used the following code in some testing.

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

This works great and the autonumber is automatically incremented so there are no duplicate records. However when I try to use the above code on a form with a tab control I get a warning stating that not all the information will be copied and when I continue only the information on the first tab is copied. Can anyone suggest a way around this or even another way of accomplishing my goal?

Thanks in advance,
Jason

 

[OnTheFly]

I think you would be better off using an Append Query using the current records unique identifier to duplicate it and then appending it to the table, requering the form and then going to the new record (which would be the last record if you are sorting by your auto number).

Be carefull doing this, however, because data integrity can degrade quickly if the user is adding duplicate information. With the append query you can limit the duplication to only the fields that are the same in both records and leave blank the information that should be different.

Hope this helps.

OnTheFly

 

[jasonmac]

Could this be done in the on-click event of a command button?

 

[OnTheFly]

Yes

Hope this helps.

OnTheFly

 

[jasonmac]

could you possibly give an example of some syntax?

 

[PHV]

Take a look at the DoCmd.RunSQL method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OnTheFly]

I will give you an example. You will have to interpret this into your database using your field names.

In this example the AutoNumber field (unique ID) will be called ID, the Table the data is stored in is called Table1. The other fields in the table are ClientName, OrderDate, ItemID, OrderQuantity.

In the OnClick event of the command button you would have something like.

Dim lngID as Long
Dim strSQL as String
Dim DB as Database

lngID=Me!ID
Set DB=CurrentDB()

strSQL="INSERT INTO Table1 ( ClientName, OrderDate, ItemID, OrderQuantity ) SELECT ClientName, Date() AS OrderDate, ItemID, OrderQuantity FROM Table1 WHERE ID=" & lngID

DB.Execute strSQL

DB.Close
Set DB=Nothing
Me.Requery

This process will insert a new record into the table and then requery the form to show it. I have inserted the system date for order date just to show you how to modify the data you are copying to be not an exact duplicate of the other record.



Hope this helps.

OnTheFly

 

[jasonmac]

Okay that's working great! Thanks. Next question: My table have many many fields. Is there a way to select all?

 

[jasonmac]

I tried using this but it didn't insert anything into the table.

strSQL="INSERT INTO Table1 SELECT * FROM Table1 WHERE ID=" & lngID

 

[OnTheFly]

The reason using the * does not work is because it includes ALL the fields, including the ID (AutoNumber) from the record you are copying. Therefore it will not add the record becuase it would be a duplicate.

There is really no way around listing the fields for what you are trying to accomplish.

Hope this helps.

OnTheFly

 

[jasonmac]

Thanks for all your help OnTheFly. I think this will work just fine.