Copy/paste row from a datasheet view into new row does not paste all columns.

Hi Experts,

I have a form opening in datasheet view and users some time want to copy rows and paste them into another row.
What we are experiencing is that not all columns get the data of the original row copied.

See first screenshot of row being copied, and then see second screenshot after being pasted in new row, how some columns take their values from either default or from code that fills in when there is no value entered.

What could be the reason?
Untitled.png
Untitled1.png
LVL 5
bfuchsAsked:
Who is Participating?
 
bfuchsAuthor Commented:
Hi,

Try with a new form.
Tried with a new form, copied all controls from original form and still happened.
However when I create new controls they dont happen, this seems like the controls are corrupted. (whatever this means..).
Doing some testing if I can use same form with new controls or have to replace the form.
Will keep you posted.

Thanks,
Ben
0
 
hnasrCommented:
Datasheet view of a form displays the columns included in the form.
I copied and pasted and was unable to reproduce the issue.

You need to check the code in Form_Current () event, or other relevant codes that processes the fields before saving to table.
Other extra properties of your table's columns, as you mentioned, may contribute to the issue.

Try to reproduce the issue using a demo database and upload.
0
 
bfuchsAuthor Commented:
Hi,
You need to check the code in Form_Current () event, or other relevant codes that processes the fields before saving to table.
The only relevant code I can see is the following
Private Sub Staffer_AfterUpdate()
    Me.StafferDate = DATE
    Me.StafferInitial = GetInitial
End Sub

Open in new window

However I see if code is omitted then columns are null, and not get the column values of copied record.

Thanks,
Ben
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hnasrCommented:
This code in AfterUpdate, modifies the copied data.

Me.StafferDate = DATE, sets the value to current date.
Me.StafferInitial = GetInitial, this probably executes a function GetInitial, that modified LU to PH2.

A demo database may help in and simplify explaining the issue.
0
 
bfuchsAuthor Commented:
This code in AfterUpdate, modifies the copied data
When I put breakpoint in code before it starts execute, I see those columns are null..

Will have to work on a demo tom.

FYI- This is an A2003 ADP linked to SQL 2008 Express (if that matters..)

Thanks,
Ben
0
 
hnasrCommented:
Sorry, but myy current access does not support ADP.

Using SQL, you may have stored procedures that fire from SQL itself.

I am happy to continue to help, if you can create an mdb database and add necessary local tables from SQL tables.
Doing so you may spot a solution to the problem.
Upload the mdb if the issue persists and further help is required.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How are you copying those rows? In a relational database system, users should NOT use Copy/Paste to move rows. Instead, you should provide them with VBA methods to copy the data, which you can control
1
 
bfuchsAuthor Commented:
@hnasr,
Sorry for the delay, hope to be in office later on & try to create a demo.

@Scott,
Why can't I have users copy/paste records if its not involved anything special functionalities?
use Copy/Paste to move rows
Its not moving rows, its adding new rows instead of typing in again.
you should provide them with VBA methods
Since I use datasheet view, its not possible to add command boxed to accomplish things, will have to program at an event like double click on this will do so & so, which is not that elegant..

Thanks,
Ben
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Why can't I have users copy/paste records if its not involved anything special functionalities?
You certainly can do so, if you wish. However, you run the risk of inconsistent data, as you're experiencing now. The only "fix" for this is to do it correctly, using VBA.

Remember that Access is not Excel, even thought it sometimes looks and acts like Excel. While you can copy several columns of data in Excel and be sure that Excel will "paste" that data into the correct columns, you cannot be sure of that with Access.

   use Copy/Paste to move rows
Its not moving rows, its adding new rows instead of typing in again.
Same thing ...

   you should provide them with VBA methods

Since I use datasheet view, its not possible to add command boxed to accomplish things, will have to program at an event like double click on this will do so & so, which is not that elegant..
"Elegance" and a pretty UI should never trump functionality or data consistency. You can embed the datasheet view in a form, and add buttons to that main form to allow users to select rows to use for your functionality.
0
 
bfuchsAuthor Commented:
@Scott,

The only "fix" for this is to do it correctly, using VBA.
Well as you see, hnasr claims this is not happening by him..
In a relational database system, users should NOT use Copy/Paste to move rows
If MSFT added those menus select record/copy/paste append etc..then its meant to use it in Access, would you create VBA procedures to replace all built in menus in Access like find/replace, all kind of filters, import/export etc...why is this any different?

@hnasr,
So far I was not able to create a demo yet, but will try to get it in next few days.

BTW, I ruled out any code behind the form, as I created a new form, copied all controls into the new form w/o the code and same happens there.

Thanks,
Ben
0
 
hnasrCommented:
While waiting for a demo, try this:
  1. Capture the screen with one record.
  2. Copy the record, and paste in the form, and capture the screen as you did before.
  3. Do this extra step, by pasting the clipboard into the comment area, this rules out Access  form effect.
  4. Upload the two screenshots.
0
 
bfuchsAuthor Commented:
Here you go,

Emp File      Employee:      St      StafferDate:      StafferInitial:      Tr      Submitted:      Status:      Status Date:      Initial:      Reason:      Source:      Last Note:      Last Note Date:      Last Note Init:      Rate:      RMV      Hide Note      Hide Rec      Pr      DateEntered:      Initial:      Employee ID:
0      A. O.      True      12/5/2017      H3            12/5/2017      Approved      12/19/2017      CP                  lm congrat      12/21/2017 10:50:54 AM      CP                                    12/5/2017      H3      159412

Thanks,
Ben
Untitled.png
Untitled1.png
0
 
hnasrCommented:
With the info available, it looks like Access side or SQL side has some code that is run before updating the copied record.
You commented:
BTW, I ruled out any code behind the form, as I created a new form, copied all controls into the new form w/o the code and same happens there.

So one needs to check SQL
Are there any table triggers; INSERT, UPDATE, or DELETE statements on a table or view?
0
 
bfuchsAuthor Commented:
Hi,

Are there any table triggers; INSERT, UPDATE, or DELETE statements on a table or view?
Just got hold of SSMS, there is absolutely no triggers associated with those tables/views in question.

What else can it be?

Thanks,
Ben
0
 
hnasrCommented:
@bfuchs
I nearly exhausted my attempts without having a demo database.
If you are still interested in more help, try to recreate the issue using an mdb with linked tables to a small SQL database.
Create the small SQL database using scripts.

If you manage to reproduce the effect, upload the mdb with the script to create the SQL database.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If MSFT added those menus select record/copy/paste append etc..then its meant to use it in Access, would you create VBA procedures to replace all built in menus in Access like find/replace, all kind of filters, import/export etc...why is this any different?
Or course I wouldn't recreate the things MSFT offers, but in many cases we have to decide if they're appropriate for use in specific contexts. I would not use the builtin filtering if I needed to give the user more control over the process, for example, I would also perhaps not use the builtin import/export functions if they were not appropriate for the context (like I needed to guide the user a bit more during the process, perhaps). But if I'm working with a more advanced audience, and I can be sure they'll use the functions correctly, then perhaps I'd provide them with the ability to use them - it all depends on the context.

Microsoft adds quite a few things that are inappropriate for more complex operations. Copying data from a single column in one row to a single column in another row is certainly fine. Copying an entire row of data to another is another matter entirely. Not saying it cannot be done, but (as is evidenced by your current issue) the reliability of these sorts of operations is suspect, at best.
0
 
bfuchsAuthor Commented:
Hi Experts,

Just updating, I might be close on getting to the source of the problem..
While trying to create a demo and duplicate the issue in MDB, I was not successfull
and this really led me to think that something is corrupted with the form..
Still experimenting some things, but looks like this is the right direction here..

Thanks,
Ben
0
 
hnasrCommented:
Try with a new table.
Try with a new form.
0
 
hnasrCommented:
Good luck!
0
 
bfuchsAuthor Commented:
Hi Experts,

For me it looks like the above solved the problem.
Hope to get word of users by tom..

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Thanks experts for your assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.