Duplicating Multiple records in a form

[Dan Knight]

I've got a continous formwith an AutoNumber as the Primary key field.

At present I can select multiple records and using the Edit Menu, Copy
command, I can copy these records and then use the Edit Menu, Paste Append
command to append the multiple records to the table.

I want to be able to automate that two step process using a command button
on the Header of the form. I've used the command button wizard and it works
fine, BUT it only select a single record; So with the button, I select my
multiple records, then press the button and it only paste the first record of
the muliple selection.

I've looked at the resultant code from the wizard and see that it references
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

I attempted unsucessfully to reference
DoCmd.DoMenuItem acmenuBar, acEditMenu, acCopy
and even to use
DoCmd.RunCommand acCmdDuplicate
I can't get these to work. They return an error message that says:
"the command or action 'Duplicate' (or Copy) isn't available now"

What I'm wondering is:
1) Can the functionality that is present natively using the menubar and Edit
menu, not be replicated in VBA?
2) If it can, how?

Thanks in advance,

Dan Knight
I'd like to be able to have a button that enables

 

[Jeff Boyce]

Dan

"How" really depends on "what"... and I'll ask "why"?

It sounds like you are trying to do something (select multiple records,
copy/append them) that you would almost certainly need to do ... if you were
using a spreadsheet! It is fairly rare to need to copy/paste records in a
well-normalized Access database.

What will having copies of those records allow you to do? (I don't ask from
curiosity, but because there may be ways to do what you need done that
better use Access' relationally-oriented features/functions.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dan Knight]

The why is almost incidental; it is because the client requests the
functionality.
And in reality, despite it being not good normalized database process,
Microsoft gives the functionality via the menu options.

You're deduction is correct! The client wishes this simply because in Excel
he "can easily create a new row by copy-paste an existing row" and the "fact"
that you "can't do that in a database" is why the client's user resists
moving his data into a database. Can you say politics? I tell you, sometimes
office politics makes the presidential trip look like child's play. So quick
answer, Yes, I totally agree with your deduction.

I also know that it's possible to go the long route of creating recordsets
&/or reading the specified record (although I'd have to work out the how to
for multiple records) data field by field and programatically adding a new
record one at a time, which IF I had relationships, etc. I would defend that
process. But in truth we're talking about a flat file (single table)
"spreadsheet" being moved into Access for better & more consistent reporting.
An Autonumber field is the primary key, in part because the data is not
normalized. My intention is to add code to check for duplicate data in
specific fields at a later date. But for now I've got to get them to move
first, then we can tighten up the normalization.

So much for the short answer. Now I'm looking for the How?

Thankyou for whatever help you can provide.

Dan

 

[Jeff Boyce]

Dan

Before you write off the possibility of using the tool (Access) the way it
was designed, bear in mind that your users probably should NOT be messing
around directly with the tables in Access ... because it is NOT a
spreadsheet, and because they can cause themselves and their data some
serious harm.

A common approach to dealing with micro-managing users is to point out that
"if it will do what you want, do you care what it looks like behind the
curtain?" You could use what you know about good relational design to build
the database as it needs to be, then provide the users a "flat" view via a
query. They interact via a form that uses that query, so from their point
of view, they are still using a "spreadsheet".

While I agree that users sometimes think they understand relational database
design (and some do), most often they are more interested in functionality
than in dictating "where the electrons go".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dan Knight]

Jeff,
I appreciate your involvement in answering these questions.

However, I'm a bit frustrated. It appears that this thread has moved along a
line of discussing the theory of whether a user should be able to copy &
paste a record or multiple records in a table or a form.

The reality of my situation is that the data is secure RE: normalization
within the form structure I have set up.

I currently have a command button that enables the user to select ONE
record, copy it and paste append it to the underlying table. That exists &
works.

Within the form view, using the Menu commands I can do the same with
MULTIPLE records - all within the form view. What I'm needing to know is:
a) Is it possible to use VBA to replicate the menu commands?
b) If so, what commands utilise the MenuBar not the FormBar commands?

If it's not possible, then a simply no can do would also work.

Again, thanks for your assistance and I agree with your assessment of the
theory and have taken care of that.

 

[Jeff Boyce]

Dan

My response appears to have been deleted.

My suggestion was to find out if the users wanted to go to the effort of
(properly) learning Access or if they just wanted their data in a
spreadsheet-appearing fashion.

If the latter, you can "flatten" data stored relationally by using a query.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dan Knight]

Jeff,
Thanks for your time. However, I believe my original question to be valid:
How does one replicate in code what one can do using the menu commands.

With respect, I wasn't looking for a lesson - nor ammo to push my clients -
on normalization; yet it seemed that that was the only answer, in various
forms given.

IF it is not possible, then a simple "Can't do it" would suffice; although
obviously that would beg the question: "Why is it possible using the menu
commands?"

IF it is not advisable, as you've pointed out, it isn't, then this begs the
question: "Why is the functionality there using the menu commands?"

IF you don't know...I serously doubt this is the case - MS doesn't appoint
MVP cavalierly.

Anyway, forgive me, I rant, and I wish to respect the fact that you're more
knowledgable and spend a fair bit of time on these discussion boards helping
out many others. For that I thank you!

 

[Jeff Boyce]

Dan

I don't claim expertise. MS awards MVP status for helping. ... and there's
plenty I don't know about.

I don't know about this. I've probably not spent the time to learn it
because there are other ways to get the job done, and that was the focus of
my observations.

Sorry if it came across pedantic...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP