Custom Menu

[terri]

Using 2000RT, I have a number of dynamic queries that I need to display in
datasheet format for review only. Using DoCmd.OpenQuery "xxx", acViewNormal,
acReadOnly does the display just fine, however, my custom menu is replaced by
the standard Access menu until the query window is closed.

Is there a way to assign a custom menu to this window? Or, as an
alternative, is there a way to make the window behave like a modal window
with no menu access?

Thanks.

 

[Jeanette Cunningham]

terri,
use a form to display these queries as a datasheet.
In the form you can specify which menu to display while the form is open.
Queries have very limited use for showing data to users - they are designed
to do all the work behind forms - not to show data to users.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Albert D. Kallal]

I like the other posters suggestion to actually build a form or report to
display this data.

However, if you specified your custom menu as the main application menu at
startup (tools->startup), you should see just your custom menu bar. In fact,
for any form or report that you haven't specified a menu bar, you'll see the
main default menu bar that you've specified in your application. It is quite
likey this is NOT not the effect your looking for. (but it would show your
custom menu bar when viewing a query however).

Is there a way to assign a custom menu to this window? Or, as an
alternative, is there a way to make the window behave like a modal window
with no menu access?

You can see that you don't have any settings like menu bar settings, modal
settings, popup settings etc. This "lack of" list goes on for a long period
of time and you have virtually NONE of these settings and options when
viewing a query (this means they're not intended to be used by end users of
your application).

Thus, you need to consideer a datasheet, some type of continuous form, or
perhaps better even a report.

It's considered a poor design practice to open up a query for your end
users. That simple query view has no way of verifying the data, no before
update events, virtually nothing to aid you in testing and checking what the
user enters into that table view to ensure that the data entry is done
correctly.

 

[terri]

Thanks for replying Jeanette.

I had initially thought to use a form, but these queries are pivot tables
that return a variable number of columns. I got awfully close to making it
work:

1. In the form, I placed an unbound textbox for each of the maximum number
of columns that could be returned.
2. I save the results of the query to a table.
3. In the form’s Open event, I cycle through the Fields collection, setting
the controlsource for each of the textboxes that should be visible and set
the ColumHidden to true for the unused controls.
4. I set the recordsource to the table.

The form opens in datasheet view just dandy. However, the column headings
reflect the control names – Text0, Text1, Text2 – not the field names. This
obviously makes the display useless!

Is there a way to programmatically set the datasheet column captions?

 

[terri]

Thanks for your response. Setting the default menu (along with a little
tweaking) will allow me to do what I need.

BTW -- these queries are read only – not for data entry. They provide
summary statistics in pivot tables.

 

[Jeanette Cunningham]

terri,
the column captions will take their names from the textbox controls.
Can you cycle thru the fields collection and set the each txtbox to the
appropriate field name?
When you set the control source for each textbox, set the name of the text
box as well.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[John Spencer]

How about using a subform on the form?
You can set the subform's Source Object to a query.

Me.sfrmDisplayData.SourceObject = "QUERY.Display_Results"


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[terri]

The control's name property can only be changed in design -- not at runtime.

 

[terri]

That worked perfectly.

Thanks John!!!
--
terri

How about using a subform on the form?
You can set the subform's Source Object to a query.

Me.sfrmDisplayData.SourceObject = "QUERY.Display_Results"


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[terri]

John,

Swapping out the queries as the SourceObject worked great -- until I got to
one that has a parameter that references a value of a control on the calling
form. The subform doesn't display because the object "is closed or doesn't
exist."

Since this query happens to return a set number of columns, I worked around
this by creating a separate subform. The parameter value is the only
dynamically named column in the pivot, so I set the controlsource of the one
unbound control (and the window caption) to this value and set the
recordsource to the query in the subform Open event. In this one case, I set
the SourceObject to the subform instead of the query.

This works -- but I'm curious as to why the parameter cause this... any idea?
--
terri

How about using a subform on the form?
You can set the subform's Source Object to a query.

Me.sfrmDisplayData.SourceObject = "QUERY.Display_Results"


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County