Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123

    Any way to copy & paste a long list of objects onto another type of document

    Hi. My subject line may or may not give you an idea of what I’m asking. Allow me to explain myself (or rather my database) & hopefully you’ll understand my objection. I have 2-completely different/unrelated databases which each contain approximately at least 80-queries. Almost half of these queries in each database are ‘yearly’ in which in many of these queries I need to change the year, I.E. from “2021” to “2022.” I just wished Access allowed you to copy a list of objects names (Tables, Queries, Forms, etc.) & to be able to paste them somewhere else. You may have a bunch of queries &/or Forms that you want to make a record of or make notes on those particular queries/forms. Like, I’m trying to list all of my many-many yearly queries & checking off those yearly queries in which I need to change the year. For now, I’m right-clicking on each individually yearly query, choosing “Rename” copying the quey’s name & then pasting it into an Excel worksheet. Needless to say, this is extremely time-consuming & tedious. I’m aware that I can easily press F2 for renaming any objects. However, I’m sort of in a unique situation. I’m physically disabled & I type with a head-stick/pointer. I can use neither my arms nor my hands & I use a laptop computer. I choose to rather have my multi-media keys accessible to me vs. the Function keys & I cannot press down on the “fn” key & one of the function keys at the same time. I tried using Auto_Hot_Keys, but nobody on Auto-Hot-Keys’s forum seemed interested in trying to help me out.



    I’m greatly sorry for digressing from the purpose of posting this thread. I will leave a picture of the top of my yearly queries, which is only one-third of my yearly queries I want to have a list of elsewhere. Any advice/suggestions would be greatly appreciated! Thanks in advance!

    Click image for larger version. 

Name:	Employees_17_Queries1.png 
Views:	54 
Size:	63.5 KB 
ID:	46856

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well you should not have queries,tables reports for each year. You should have a set and a parameter for the year.
    Saying that, some vba could, copy and rename said queries?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I agree. Dynamic parameterized query or apply filter to form or report would be a lot simpler and lot less work.

    Access does allow copy paste of multiple objects as well as drag and drop. https://www.accessforums.net/showthr...ight=drag+drop
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then there is the database documenter, which if you play with the options, can give you way more info than you need.
    Agree with above - one object per year is a mistake. Could be one query which asks you for the year, or even doesn't ask and just assumes you'll always want the current year (or last year, or 2 years ago, but always the same deviation). Same for reports - could be based on such a query. Can also use form controls to choose year/department/whatever and pass those values to the query that opens the report with what you asked for.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    [QUOTE=June7;487180]I agree. Dynamic parameterized query or apply filter to form or report would be a lot simpler and lot less work.

    Access does allow copy paste of multiple objects as well as drag and drop. https://www.accessforums.net/showthr...ight=drag+drop[/QUOT


    Thank you for replying to my post! Unfortunately for me I am an amateur with Access & haven't a clue how to begin to do what Welshgasman & you suggested above. Would you care to elaborate for me, please? I know how many of you wouldn't understand how my database is set up because I'm sure it's far-far from being Normalized. I just maintain a small employees database. Keeping track of which days employees work & for how many hours. Therefore, as senseless as it may sound I have a lot of yearly forms (or actually queries) base on the current year (Jan -- Dec). So come December i actually have two separate databases which I’ll need to change the year in many-many queries. It’s quite possible that what you have suggested may not work if my databases are so out-of-wack. Again, thanks for your advice, but I can use a little more explaining.

    Also, I tried selecting 4 or 5 queries, dragged & dropped them into an blank Excel worksheet, onto a blank MS Word, & tried a Notepad, but the names of these selected queries are not tranferring. The picture above is a picture of just the first 29 or 30-quueries in my database. I think I can show you how I would like to have a list of just my yearly queries like ia atable or in a spreadsheet format shown in the attchment below. Nevermind I'm unable to bring up the attachment I wanted.







  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by djclinton15 View Post
    Therefore, as senseless as it may sound I have a lot of yearly forms (or actually queries) base on the current year (Jan -- Dec). So come December i actually have two separate databases which I’ll need to change the year in many-many queries.


    Would you post the SQL of a couple of the yearly queries?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The only thing worse than replications of reports/forms/queries because they are based on parameters (i.e. such things being dependent dates) is replicating databases for the same reason. Just my opinion of course. I think ssanfu has made a good suggestion as it will probably give some clue as to whether or not I/we are barking up the wrong tree so to speak. If you've ever had a dog that chases squirrels that saying is so apropos
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The copy/paste and drag/drop I referred was to copy to another Access database. Why would Excel, Word, Notepad be involved?

    In a conventional database, records would be filtered dynamically instead of duplicating queries with static parameters.

    Dynamic parameterized query is basic database feature. So is applying filter to form or report when it opens, e.g.:

    DoCmd.OpenReport "MyReport, , , "Year([MyDateField]) = " & Me.tbxYear

    Unfortunately, this will likely require major redesign of your db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    How long have you been doing this? as I would expect a core of queries.
    If you took the time now, to fix the problem, it is a one time fix, and should not be that difficult, if you have managed to get where you are today?

    Continue down this path and this will repeat year after year.

    For a little bit of pain now, you can save yourself a lot of pain further down the road. I myself, and I am sure others, would be prepared to help with it as well.?

    That said, and still against my better judgement, I knocked up a little code to show what I was thinking of.

    It is purely for queries at the moment, but the concept would be the same for reports, forms etc.
    Comment out the copy and uncomment the Debug.print until you get the queries you need to copy, then reverse the procedure.
    Obviously the criteria is different as well, so replace the "1" with whatever you have now and the Year(Date) with the correct year value.

    Code:
    Sub CopyQuery()
    Dim qdf As DAO.QueryDef
    Dim db As DAO.Database
    Dim strNewname As String
    
    Set db = CurrentDb
    For Each qdf In db.QueryDefs
        If Left(qdf.Name, 1) <> "~" And InStr(1, qdf.Name, "1") > 0 Then
            'Debug.Print qdf.Name
            DoCmd.CopyObject , Replace(qdf.Name, "1", Year(Date)), acQuery, qdf.Name
        End If
    Next
    
    Set qdf = Nothing
    Set db = Nothing
    Application.RefreshDatabaseWindow
    
    End Sub
    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in your first post you said

    'allowed you to copy a list of objects names (Tables, Queries, Forms, etc.) & to be able to paste them somewhere else.'

    I'm reading this as 'them' means the name, not the actual object (i.e. the contents) - is this correct?

    If this is the case, you could look at using a query to list the names from msysObjects - something like

    Code:
    SELECT [Name]
    FROM msysObjects
    WHERE [Type] In (1,5,-32768,-32764,-32761) AND [Flags] In (0,16) AND [Name] Like '*Yrly*'
    ORDER BY [Type], [Name]

    For reference
    [Type] - 1=Table, 5=Query,
    -32768=Form, -32764=Report, -32761=Module
    [Flags] are 0 for your objects (so ignores system objects) plus 16 which applies to cross tab queries

    Then open the query, select all records and copy/paste


    Edit: But I do agree with the other responders



  11. #11
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    Yes Ajax, you are exactly correct & picked up on what I meant. it was clearly my fault for not clarifying what I was asking. Using the right wording is crucial when posting a question (making sure I check my grammar also would help). Anyway, I used your code in a black query (SQL) & in return, I got a very long list I think of all of the entire queries (as well as forms in my database. If I use this code again for the purposes to obtain just a list of queries in a database where is it says, “type” I can just have the number “5” there & I ought to only get just the queries’ names in a database. I’m only going by what you had written below the code, “For reference[Type] - 1=Table, 5=Query, -32768=Form, -32764=Report, -32761=Module.” But, that’s okay, it’s a start because then I’m able to filter all of my ‘yearly’ queries from that list. Thanks a lot for providing me with that code. You may not care to know my reason why I would want to create a list of my queries (or objects) names as June7 questioned & rightfully so couldn’t understand. My purpose in creating a list of my ‘yearly’ queries is so I can cross out/check-off the yearly queries in which I did not need to change-update the year. This way, next year if I have to repeat this task I’ll have an idea of which yearly queries I’ll need to change. Thanks, Ajax & I’m going to post again to all of the members who kindly respond to this post.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    can just have the number “5” there & I ought to only get just the queries’ names in a database.
    yes but that will also include system queries (ones used on forms, reports, combos etc) to exclude those you also need [Flags] In (0,16)

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Can I suggest a further refinement to your SQL to list all the queries but exclude all 'temp' queries used in form combos and listboxes.
    So you want all objects with Type=5 and Flags<>3
    Code:
    SELECT MSysObjects.Name FROM MSysObjects
    WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3));
    You may be able to filter the Name field further to just get those for the current year if that is part of the query name
    Last edited by isladogs; 12-11-2021 at 10:55 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    Before I consider closing out of this post which I don’t know if I would consider it to be, “solved” I wanted to thank all of the members who took the time & effort to try & help me out, I’m afraid that I do not understand what has been suggested. Welshgasman, thank you for taking out the time to write out that code, but ii honestly don’t have a clue what it means nor what to do with it. To say the least I know that my database is way out of kilter & if any of you were to see it you would see how greatly I lack in database design & etc. Even if I were to attempt to try what was suggested most likely it wouldn’t work. Someone said that I may need to do a little reconstructing with my database. Actually, I believe the whole database needs to be scrapped & started over. I really don’t have the time to invest in starting over with rebuilding a database that seems to work for me. Yes, my database has tons of errors & glitches, but more importantly aside from the time, I honestly do not understand how to build a database correctly. I m known as a visual learner, I cannot comprehend things by reading a book or on some website. Even as a visual learner I have watched countless videos on how to begin to build a database & it still just doesn’t register in my mind. I think that part of my problem may be because I’m so used to using my databases, “my way” that it blocks me from trying something different. If that makes any sense.

    Anyway, as I have said many times in past posts I think all of you guys/members are just extraordinary special people in my book. Not only to volunteer your time but to also share your vast amount of knowledge in helping others (for no cost) is such a rarity in the world we live in today. I also know that some of you have been doing this for a long-long time because I’ve been coming to this site for many years & I remember a lot of the members’ names. Keep up the good work guys & gals!

    Any final thoughs would be welcome.

  15. #15
    djclinton15 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    123
    Just to retreive the qeury names here'ss what I tried:
    FROM msysObjects
    WHERE [Type] In (5) AND [Flags] In (0,16) AND [Name] Like '*Yrly*'
    ORDER BY [Type], [Name]

    Didn't work, what am I missing here? -- I didn't copy/paste SELECT NAMES Now I think I have just a list of my queries. Question though, it's listing 155 queries/Recorrds, but that seems too many queries for this database. Could it be inacurate a little?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy paste
    By Devi in forum Access
    Replies: 1
    Last Post: 11-23-2020, 05:02 PM
  2. Rename word document during copy/paste
    By Homegrownandy in forum Programming
    Replies: 3
    Last Post: 07-17-2018, 08:15 AM
  3. Is it possible to copy and paste?
    By DubCap01 in forum Forms
    Replies: 1
    Last Post: 12-21-2016, 03:01 AM
  4. Replies: 4
    Last Post: 08-24-2015, 12:57 PM
  5. Copy and Paste Row (vb)
    By Computer202 in forum Programming
    Replies: 7
    Last Post: 03-28-2014, 01:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums