Query Documenting Code

[Jeremy Wallace]

I have a ton of queries that I need users to be able to view. I'd like to
have them viewed in a datasheet-view form instead of directly, so that I can
keep the users from futzing with the data.

I'd like to do this in a temporary database, creating an autoform based on
the query and making that form the source object for a subform in a
pre-existing main form. I just ran a quick-and-dirty test, using the code at
the bottom of this post, and it seems to work reasonably well (the client
doesn't have much money to spend, so reasonably well is good enough, or will
be once I tweak the code and nudge the main form).

I'm using Call DoCmd.RunComman d(acCmdNewObjec tAutoForm) to create the form.
It works fine, as long as I've got the query highlighted in the database
window. Is there a way to supply a query name for this wizard? Or is there a
way to control what's highlighted in the database window?

Many thanks for any pointers.

Jeremy

--

Sub MakeTheForm()
Call DoCmd.DeleteObj ect(acForm, "Form1")
Call DoCmd.RunComman d(acCmdNewObjec tAutoForm)
Call DoCmd.Save
Forms!form1.All owEdits = False
Forms!form1.All owAdditions = False
Forms!form1.All owDeletions = False
Call DoCmd.OpenForm( "form1", acDesign)
Forms!form1.Def aultView = 2 'datasheet
Call DoCmd.Save
Call DoCmd.Close(acF orm, "Form1")
Call DoCmd.OpenForm( "frmTempMai n")
Forms!frmMain.S etFocus
End Sub

=============== ==
Jeremy Wallace
AlphaBet City Dataworks
ABCDataworks dot com

[Pieter Linden]

Re: Query Documenting Code

Just wondering if this might help... any way you could tweak the code
in the SQL section of the Developer Handbook... just pick a query from
a dropdown or something and then have it show in the bottom window as
read only?

[rkc]

Re: Query Documenting Code


"Jeremy Wallace" <cdma@abcdatawo rks.SKIPTHISPAR T.com> wrote in message
news:bF6dnVRMya keowqiXTWc-w@speakeasy.net ...[color=blue]
> I have a ton of queries that I need users to be able to view. I'd like to
> have them viewed in a datasheet-view form instead of directly, so that I[/color]
can[color=blue]
> keep the users from futzing with the data.
>
> I'd like to do this in a temporary database, creating an autoform based on
> the query and making that form the source object for a subform in a
> pre-existing main form. I just ran a quick-and-dirty test, using the code[/color]
at[color=blue]
> the bottom of this post, and it seems to work reasonably well (the client
> doesn't have much money to spend, so reasonably well is good enough, or[/color]
will[color=blue]
> be once I tweak the code and nudge the main form).[/color]

Don't know the answer to your question, but I think I would look
at using a simple ListBox and set it's properties to match each query
you want to view. Lighter weight widget, no need to re-create a
subform each time.

[David W. Fenton]

Re: Query Documenting Code

rkc@yabba.dabba .do.rochester.r r.bomb (rkc) wrote in
<S_Nlb.46585$Sc 7.14155@twister .nyroc.rr.com>:
[color=blue]
>
>"Jeremy Wallace" <cdma@abcdatawo rks.SKIPTHISPAR T.com> wrote in
>message news:bF6dnVRMya keowqiXTWc-w@speakeasy.net ...[color=green]
>> I have a ton of queries that I need users to be able to view.
>> I'd like to have them viewed in a datasheet-view form instead of
>> directly, so that I[/color]
>can[color=green]
>> keep the users from futzing with the data.
>>
>> I'd like to do this in a temporary database, creating an
>> autoform based on the query and making that form the source
>> object for a subform in a pre-existing main form. I just ran a
>> quick-and-dirty test, using the code[/color]
>at[color=green]
>> the bottom of this post, and it seems to work reasonably well
>> (the client doesn't have much money to spend, so reasonably well
>> is good enough, or[/color]
>will[color=green]
>> be once I tweak the code and nudge the main form).[/color]
>
>Don't know the answer to your question, but I think I would look
>at using a simple ListBox and set it's properties to match each
>query you want to view. Lighter weight widget, no need to
>re-create a subform each time.[/color]

Interesting suggestion.

I once had an application that had way too many fields in the
tables (design error on my part), and I needed forms that would
allow editing of all the data. I already had forms for the three
main tables, but didn't want to create three other complex forms
that combined the data from those three tables with the data from
the 1:1 data tables that were not part of the data entry process
(the records were partly created from an import process and partly
from data entry). So I used a listbox populated by a function and
allowed editing via a text box at the top of the form. You could
edit the value of the particular row of the listbox, where each row
was a field from a particular record (each record being combined
from two 1:1 tables).

It was pretty complex, but it did mean that I got a single form
that allowed viewing and editing of all the data in the
application. I'm not certain it was easier than hand-building the
forms would have been, but in terms of maintenance it was much
easier. Six months after I implemented this, the organization where
the data originate restructured their data tables in minor ways and
adapting this form to deal with that was as easy as editing a few
rows in the the data tables that I used to help with the UI (I got
user-friendly descriptions of the fields from this data table, for
instance, and also used it to control which fields were editable
and which were not).

I've often thought about trying to put together a demo from it, but
it's so application-specific that I don't think it's worth it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

Re: Query Documenting Code


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:941D964EBd fentonbwaynetin vali@24.168.128 .78...[color=blue]
> rkc@yabba.dabba .do.rochester.r r.bomb (rkc) wrote in
> <S_Nlb.46585$Sc 7.14155@twister .nyroc.rr.com>:[/color]
[color=blue][color=green]
> >Don't know the answer to your question, but I think I would look
> >at using a simple ListBox and set it's properties to match each
> >query you want to view. Lighter weight widget, no need to
> >re-create a subform each time.[/color]
>
> Interesting suggestion.
>
> I once had an application that had way too many fields in the
> tables (design error on my part), and I needed forms that would
> allow editing of all the data. I already had forms for the three
> main tables, but didn't want to create three other complex forms
> that combined the data from those three tables with the data from
> the 1:1 data tables that were not part of the data entry process
> (the records were partly created from an import process and partly
> from data entry). So I used a listbox populated by a function and
> allowed editing via a text box at the top of the form. You could
> edit the value of the particular row of the listbox, where each row
> was a field from a particular record (each record being combined
> from two 1:1 tables).
>
> It was pretty complex, but it did mean that I got a single form
> that allowed viewing and editing of all the data in the
> application. I'm not certain it was easier than hand-building the
> forms would have been, but in terms of maintenance it was much
> easier.[/color]

What the op was asking for isn't nearly as complex as what you were
doing. If all that is wanted is to display the result of a saved query,
the whole deal can be accomplished in about ten lines of code. The
only thing that needs to be done besides setting 4 listbox properties
is to determine the field count. That can be done via the querydef
object. The only thing a read only datasheet has to offer over a
listbox is the built in column sorting. Which admittedly is a big thing
if it's desired.

[Jeremy Wallace]

Re: Query Documenting Code

Pieter,

Thanks. I had completely forgotten about that form. I remember how long it
took me to digest what was going on in that form, but then, that was back in
1996 or 1997.

I dropped it in my database, added the combo box from my sql editior form
and it didn't work right away. I'll tweak it a bit and see how it goes. I
don't imagine any major problems. I'm sure this will save me tons of work,
and do exactly what I'm looking for.

Thanks again.

Jeremy

--
=============== ==
Jeremy Wallace
AlphaBet City Dataworks
ABCDataworks dot com
"Pieter Linden" <pietlinden@hot mail.com> wrote in message
news:bf31e41b.0 310222314.10d7a 130@posting.goo gle.com...[color=blue]
> Just wondering if this might help... any way you could tweak the code
> in the SQL section of the Developer Handbook... just pick a query from
> a dropdown or something and then have it show in the bottom window as
> read only?[/color]

[Jeremy Wallace]

Re: Query Documenting Code

Just had to change the form references to function calls in the
queries and then everything's hunky dory.

Thanks again for showing the light.

"Jeremy Wallace" <cdma@abcdatawo rks.SKIPTHISPAR T.com> wrote in message news:<MRGdnSbGQ 79z9D-iXTWc-w@speakeasy.net >...[color=blue]
> Pieter,
>
> Thanks. I had completely forgotten about that form. I remember how long it
> took me to digest what was going on in that form, but then, that was back in
> 1996 or 1997.
>
> I dropped it in my database, added the combo box from my sql editior form
> and it didn't work right away. I'll tweak it a bit and see how it goes. I
> don't imagine any major problems. I'm sure this will save me tons of work,
> and do exactly what I'm looking for.
>
> Thanks again.
>
> Jeremy
>
> --
> =============== ==
> Jeremy Wallace
> AlphaBet City Dataworks
> ABCDataworks dot com
> "Pieter Linden" <pietlinden@hot mail.com> wrote in message
> news:bf31e41b.0 310222314.10d7a 130@posting.goo gle.com...[color=green]
> > Just wondering if this might help... any way you could tweak the code
> > in the SQL section of the Developer Handbook... just pick a query from
> > a dropdown or something and then have it show in the bottom window as
> > read only?[/color][/color]