asked on

Hide Datasheet View Using Docmd.Openquery

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.

davidrichardson

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

scuttlebuttin

ASKER

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

davidrichardson

Do you have the DoCmd.OpenQuery in the form open event, if so try removing them

scuttlebuttin

ASKER

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

davidrichardson

if they are all select querys then you dont need to referance them try removing them

scuttlebuttin

ASKER

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.

ASKER CERTIFIED SOLUTION

davidrichardson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

scuttlebuttin

ASKER

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?

scuttlebuttin

ASKER

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!!

davidrichardson

No problem