Results 1 to 5 of 5
  1. #1
    rodrich1954 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    2

    Datasheet "views" of Queries are forever?

    Pick any table with a dozen or so fields and use them to create a test query. Include all fields in the order they occur in the table. Now run the query, or just request datasheet view. Now move some columns around, hide a couple, and change the width of a few. Make a useful view of the data. Save your query. Now open it. How nice. The view is how you get to see your datasheet.



    This is useful when you run a query inside a subform control without creating a form. Just set the sourceobject of the subform control like so: me.yoursubformcontrol.sourceobject = "Query.NameOfTheQueryYouCreated". The leading Query. part of the string has got to be there. Voila, your datasheet appears with the view you created.

    All this is very handy.... BUT, how do you GET RID of this view that has bound itself to your query? I haven't figured that one out and I really need to know. And just as an aside.... where the heck is this "view" being stored by Access anyway?

    Anybody?

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    If you don't want a saved query, you can do this:
    Open the query in SQL view and copy the SQL string,
    then in the subform paste the sql string as the subform's recordsource
    When all works well, delete the original query.

  3. #3
    rodrich1954 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    2
    Thanks for your reply. I know I can fix it by recreating the base query but there has to be a way to do it without going to that extreme. I am hoping somebody out there knows the secret, if there is indeed one. MS Access can do some slick things but there has to be a way to revert to the unformatted view within the system one would think.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not really sure what you mean by 'GET RID of this view'. What 'view' is there to get rid of? You have a subform container with a query object as SourceObject. Query has some design settings associated with it - change the settings. If you prefer, use table as SourceObject.
    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.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    when you say 'get rid of this view' do you mean revert to some earlier view? In which case which one? the one just before you hid that column? or the one before that when you widened another column? Or do you mean the original 'view' when the query was first opened?

    where the heck is this "view" being stored by Access anyway?
    in the query properties e.g.

    currentdb.QueryDefs("myquery").fields(0).Propertie s(0).name

    properties have name and value elements - use vba to loop through a fields properties to find the names, then work out which ones you want to change - assuming you can, I've not tried. Either way you will need to capture those values at the point you want the 'view' to revert to so you can store them somewhere and reference as required.

    Note that providing you do not save the query once you have changed the view, you will get the original view again when next opened. Same as excel/word etc. Open a file, move stuff around and save. You cannot revert back to the original. Don't save and you can.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Completely disable "Read Only" forever
    By Joseph_Rice in forum Security
    Replies: 2
    Last Post: 07-16-2016, 03:34 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 2
    Last Post: 02-27-2015, 10:03 PM
  5. Replies: 1
    Last Post: 12-28-2012, 02:54 PM

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