Copy/Paste a query

[mrabrams2]

I have been working in Access for 5 years now, and have never seen this issue !!

When I go to the Query tab and try to right click on a query to "Copy" and "Paste" it, after I click Copy, (if it is a query with a parameter) the parameter box pops up, I click cancel, then it pops up 3 or 4 more times !! Then I am able to right click and select Paste.

This only happens on queries with a parameter.

I have imported everything into a clean, fresh database = and repaired and compacted the new database.

This phenomenon still occurs!

Has anyone ever seen this before? Can you point me in the right direction for a fix? I don't even know where to look for any particular settings.

This is happening in ALL databases with queries that have a parameter.

Thank you for any insight regarding this.

Michael

 

[Pat Hartman]

There are better ways to copy a recordset than "cut and Paste". Use TransferText or TransferSpreadsheet for starters.

The prompting is occuring because apparently, Access reruns the query (and so needs to re-prompt for the parameters) during the cut and paste operation.

 

[mrabrams2]

Actually, this is the scenario - I have done this 1,000 times over the years.

I have simple select query - qryABC. 5 fields. One field, in the criteria section, I have [Enter Name]. It works fine.

I need another query with a slight variation. Instead of reinventing the wheel, I right click on qryABC and select COPY. Then I right click again and select PASTE. A box pops up asking me for a Query Name. I type in qryDEF. I open qryDEF and make whatever modifications necessary.
This works fine on every database I have ever developed.

Today, when I right click and select copy - the parameter Enter Name pops up 5 times unti I can right click Paste.

I am on WIN XP - Access 97. This is crazy !! I did the import everything into a clean database, compact/repair. Still is happening !

I CAN copy/paste the non-parameter queries.

Thanks for any suggestions.

Michael

 

[LaBelette]

I might be totaly beside the track, but I would try turning Name Autocorrect off (Tools>Options>General>Track name autocorrect info).
Maybe it runs the query to verify that all the names are correct in it.

 

[mrabrams2]

I don't see Tools>Options>General>Track name autocorrect info listed.
See print screen.

 

[ghudson]

I might be totaly beside the track, but I would try turning Name Autocorrect off (Tools>Options>General>Track name autocorrect info).
Maybe it runs the query to verify that all the names are correct in it.

Access 97 does not have that feature.

 

[mrabrams2]

Access 97 does not have that feature.

Heh - no wonder I couldn't find it!!

Anyone have any ideas?

Thanks !!

 

[neileg]

If it's popping up 5 times then it can't be something as simple as the Windows double click setting for your mouse. If it affects all databases and they all used to behave properly, then the common factor is Access itself. Since there aren't any settings that would affect this behaviour, this makes me suspect that your software is corrupt. I would uninstall and reinstall Access or Office (whichever is relevant) and try again. I'm presuming that you have appropriate antivirus protection and that you've scanned for trojans and spyware, already.

 

[mrabrams2]

Thanks Neil.

Yeah - at work here the PCs all have the latest spyware protection/virus stuff.

It is quite interesting though, because when I remove the parameter from the queries, they copy/paste fine !!

I will talk to the IT folks here because it appears we are running Access 97 SR1. I'll ask them to reinstall also (but that may be a while !!!)

Thanks for the reply !!

Michael

 

[LaBelette]

I knew I was right!

Heh - no wonder I couldn't find it!!

Anyone have any ideas?

Thanks !!

Once again, I was right : I was beside the track!

 

[The_Doc_Man]

Have you tried this:

Open the query in SQL design view.

Select the entire SQL clause.

Copy it via CTRL/C or the Copy icon

Close the query.

Create a NEW query.

Immediately go into SQL design view.

Paste what you copied.

Save the new query.

Try that as a way to save a parameter query without actually opening it.

 

[mrabrams2]

First of all, I would like to thank everyone for taking the time to reply.

I have used the 'workarounds', but I need to find out what is the bottom line cause of this behavior.

I am having our IT folks re-install Access, with the hopes that the application is just corrupt.

I have tried to copy/paste these queries on other machines, and it works just fine !!

Again, if anyone else has experienced this, please post what you did to fix it !!

Have a good weekend all !!

Michael

 

[ghudson]

I recall having an Access 97 db that would crash if I tried to copy and paste certain queries. They were big with all the stuff going on inside but I did not think that they were out of the ordinary. I tried everything but nothing worked except what the DOc Man suggested. Opening the query, selecting, copying the SQL and pasting it inside of a new query. I have not had that problem since the db was converted to Access 2003.

 

[mresann]

What has probably happened is that A2K onward has required more stringent SQL syntax than before, in particular with queries created in code. In many cases, queries that didn't have brakets around the field names or table names, or was not properly prioritized parenthetically, caused errors.

The best way to fix it is to, indeed, copy/paste the code in a QBE grid of the upsized application, let the QBE generator create the new code, then repaste it back into VBA, where you can tweak it back to what you need.

This was especially prevelant from A97 to A2K, but A97 to anything will cause that problem. Also, explicitely declaring all recordset objects to the proper syntax is necessary.

 

[mrabrams2]

One of these days I will include ALL the relevant information in my first post !!

I am not upgrading. I am not copying the queries TO another database.

I am attempting to do something I have done 1000 times successfully. As far as I know, nothing on my system has changed in the last 2 weeks.

I open the database. I click on the query tab. I right click on a query. I select Copy. At this point, the parameter box pops up - I click Cancel (or OK , doesn't matter). This goes on 5 or 6 times. Then I can right click Paste (in the same database window - in the same query tab. If I open the query in design view and take out the parameter, and then close the query, I can then right click and COPY/Paste. Hunh??

(I am using Access 97. I am not copying the query to another database.)

I am still baffled.....

Michael