[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Hide Datasheet View Using Docmd.Openquery

Posted on 2004-11-16
10
Medium Priority
?
1,517 Views
Last Modified: 2008-01-09
Hi EE,

I've created several SELECT queries and run them using docmd.openquery.  Unfortunately, the results of these queries are visible in datasheet view.  I'd like to hide the datasheet view and display the final query's results in a form.  Oddly, I have dozens of other SELECT queries in this MDB that aren't displayed in datasheet view.

     'below queries added by JH 11-11-04 to create accurate supplier
     'rfq and single source counts
    DoCmd.OpenQuery ("qry_PRIMARY_SS_AVL") 'excludes offline/estimate/customer from AVL table
    DoCmd.OpenQuery ("qry_SS_MFG_PN") 'builds single source mpn data to query
    DoCmd.OpenQuery ("qry_Supp_SS_RFQ_Counts") 'joins supp name w/ SS count
    DoCmd.OpenQuery ("qry_Final_Supp_RFQ_Count")  'joins supp name w/ ss count and avl table supp count

    DoCmd.OpenForm "frm_Final_Supp_RFQ_Count", acNormal

All of the "DoCmd.OpenQuery..." actions are displayed in datasheet view...argh.  

Any suggestions?

Thank you in advance!
Jay

P.S.  I've tried using the currentdb.querydefs("myquery").execute per another EE post but as my queries are SELECT actions, execute isn't allowed. One other variable:  the queries are querying other query's datasets.
0
Comment
Question by:scuttlebuttin
  • 5
  • 5
10 Comments
 
LVL 7

Expert Comment

by:davidrichardson
ID: 12598974
You dont need to run the select query to display the data on a form, just set the forms datagrid or text box controle source to the query

0
 

Author Comment

by:scuttlebuttin
ID: 12599023
Hi David,

Actually, I don't have a problem displaying the results in the form. The problem is that all the queries prior to the OpenForm are displayed in datasheet view and I require that they are hidden.  Does that clarify my problem?

Thanks,
Jay
0
 
LVL 7

Expert Comment

by:davidrichardson
ID: 12599107
Do you have the DoCmd.OpenQuery in the form open event, if so try removing them
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:scuttlebuttin
ID: 12599262
Nope, the queries are in a function.  

Public Function TurnOffXMLandLot()
On Error GoTo ERROR:

    Forms![frm_main]![Command31].SetFocus
    Forms![frm_main]![SourcingHyperLink].Visible = -1
     Dim X As Integer
     X = MsgBox("Output Files Ready to Upload", vbSystemModal, "Finished")


     'below queries added by JH 11-11-04 to create accurate supplier
     'rfq and single source counts
     Call AVLQueries
'    DoCmd.OpenQuery ("qry_PRIMARY_SS_AVL") 'removes offline/estimate/customer from AVL table
'    DoCmd.OpenQuery ("qry_SS_MFG_PN") 'builds single source mpn data to query
'    DoCmd.OpenQuery ("qry_Supp_SS_RFQ_Counts") 'joins supp name w/ SS count
''    DoCmd.OpenQuery ("qry_Final_Supp_RFQ_Count")  'joins supp name w/ ss count and avl table supp count

    DoCmd.OpenForm "frm_Final_Supp_RFQ_Count", acFormDS
ERROR:
    Debug.Print err.Description
    Debug.Print err.Number
End Function
0
 
LVL 7

Expert Comment

by:davidrichardson
ID: 12599285
if they are all select querys then you dont need to referance them try removing them
0
 

Author Comment

by:scuttlebuttin
ID: 12599286
Sorry, but I pasted the code from an attempt to call the queries when they were added to a module instead.  The current code is minus the Call AVLQueries; and the following 3 DoCmd.OpenQuery lines aren't commented.
0
 
LVL 7

Accepted Solution

by:
davidrichardson earned 2000 total points
ID: 12599304
you must be opening the queries somewhere, find out where and remove the DoCmd.OpenQuery
0
 

Author Comment

by:scuttlebuttin
ID: 12599323
you are right i am opening them.  i am querying the select queries to build the final dataset.  i thought that i would have to open them to build the data for the final query that is referenced in the DoCmd.OpenForm "frm_Final_Supp_RFQ_Count", acFormDS recordsource.  i'm taking your advice and removing them.  if this doesn't work, maybe i need to run make tables to create my final dataset?
0
 

Author Comment

by:scuttlebuttin
ID: 12599387
Apparently there was a fundamental flaw in my docmd.openquery understading; which would explain why there weren't other topics related to this problem here or elsewhere on the web!  Thank you very much David!!
0
 
LVL 7

Expert Comment

by:davidrichardson
ID: 12599473
No problem
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month19 days, 11 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question