Re: [ProgSoc] MS Access question

[Telford Tendys <telford@nospam.progsoc.uts.edu.au>]

I hate to admit it but I have used MS Access.
I can at least say that the newer versions (with VB) are worse
than the older versions (with non-OO Access basic) basically because
all the OO stuff in VB is a lot of effort for next to no reward.

> we have two main tables, one with store details and with media
> publication details. There is a third index table matching up which
> store are "covered" by which publications.
> 
> i need to create an Access (97 or 2000) form that alloows the user to
> search either by store details and bring up a list of media
> publications, or vice versa.

I don't think it is possible on one single form, unless you do nasty
tricks like writing code on the form that modifies the form itself
(yes this is possible -- all form settings are just database entries 
in a table of form settings so forms may legally self-modify).

Much simpler and more sensible to do it on two forms: one to display
stores covered by a given puplication; another to display publications
covering a given store. Each form should have its own query and that
query should be the ``data source'' for the form -- don't ever use the
form creation wizard because it packs the whole SQL query into a string
field in the form properties making it just about impossible to edit
and confusing as hell for anyone who wants to understand what is going on.
Much better to use a named query with a similar-sounding name to the
form.

> it's a cinch to write the queries etc, and i could do it in 2 seconds
> in ASP, but to get this user interface functionality in Access is
> prooving to be a pain.
> 
> now a couple of questions.
> 
> i've got queries like;
> 
> SELECT [Stores].[suburb], [Stores].[state], [Stores].[storetype]
> FROM Coverage INNER JOIN Stores ON
> [Coverage].[storeId]=[Stores].[id]
> WHERE Coverage.mediaId =
>     (SELECT Media.id 
>      FROM Media 
>      WHERE Media.name = Forms![Search Form]![name].text 
>      AND Media.type2 = Forms![Search Form]![mediatype])
> ORDER BY [Stores].[storetype];
> 
> and i have created a user input form (hence the links to the fields
> in the above query). I can get that form to launch the query using a
> macro, and i can also get the form to launch another form to display
> the results of the query nicely, but i can't get the first form to
> pass on the user entered data to the query (ie: the above
> forms![search form]!name.text syntax does not work)

I would not try to do this macro launch thingy but would instead
put a text-entry gadget in the header (i.e. top of page stuff) for the
output form and would set the query to be the data source for that
form... this forces the form to run the query whenever the form
executes a ``requery'' command. The text-entry gadget would be ``unbound''
(so the user can set it to anything) but would be referenced by the
query so that the query results depend on the contents of that gadget.
Then all you have to do is put a tiny function in the ``after update''
event for the text gadget and that function just calls requery on the
form (follow all this?) so the form reruns the query and displays
the results every time the gadget is adjusted.

You can be a bit fancier by making the gadget a drop-down box which
contains a query to find all the unique names of stores or publications.

Once you have one form done, the other is exactly the same but the
query is backwards (no big deal). Then you want some menu-selector form
that jumps to one or other of the query forms.

> how can i get that data into the query or is there an altogether
> better approach?

The basic idea of what you have is correct except that inside the
SQL you must list the ``full path'' to the gadget that contains
the data. So if you have a form called ``floodle'' and a gadget on
that form called ``key'' then you need an expression like:

   [forms]![floodle]![key].data

or possibly even longer... the use of [] and ! and . (and sometimes "" too)
is so arcane that no one can reliably get it to work without using
the ``expression builder'' (which you invoke from some menu that
pops out of the page if you know some the alt-key and mouse combination,
yes I am serious, it changes with every version). Once you get into the 
expression builder, the path tree of the hierarchical namespace is available
and you can click on each step down the path and it will figure out the
full expression.

If all else fails you can define a global variable to hold the data
and get the gadget to copy its data into the global (a bit fiddly but
good if you are going to use the same data in a bunch of places).

	- Tel
-
You are subscribed to the progsoc mailing list. To unsubscribe, send a
message containing "unsubscribe" to progsoc-request@nospam.progsoc.uts.edu.au.
If you are having trouble, ask owner-progsoc@nospam.progsoc.uts.edu.au for help.