libreoffice base: building query, need help with input parameter development

users
#1

Hi,
Thank you in advance for your help!
I'm trying to build a query that would list the records in a database where
the user selects from an existing list of categories.

IE
there are 12 existing categories, each with a bunch of records.
the user wants to review just the records under the category "gas", but he
doesn't know that exact category name so he needs a listbox to show his
existing categories to select from.

What would I need to write as a criteria so that when the query is run, it
prompts the user to select from a listbox so he can review the records for
that said category?

Thank you.
Alanon

#2

What would I need to write as a criteria so that when the QUERY is run, it

prompts the user to select from a listbox

You must create a form to use the List Box control. If you really only
need a query you can add the choices to the query parameter name. For
example: SELECT "F1", "F2" FROM "Table1" WHERE "F1" =
:enter_dog_cat_or_bird (see simple attached example).

From the installed help see: Contents > Database Functionality > General
Information > Working with Queries (Query Design / Parameter queries) and
Working with Forms (Form Controls / List Box).

#3

thank you Bruce.

I have the Query setup as well as the stand alone or unbound form with the
listbox in it.

I find a way to get the form to execute the Query. In the controls window,
I am finding where actions can trigger, but it only appears to allow me to
add macros...I can't type anything in the text boxes next to the event
items. I've also tried adding a push button hoping a wizard would come up
to build the action...no such luck.

I'd prefer not typing in all 12+ categories in the Query criterion field as
more categories will be added in the future on an as-needed basis. Plus
some are rather longly named.

Can you or anyone assist with getting a form to execute a query? The
"help" was more like a dictionary than a how-to.

thank you in advance,
alanon

#4

*correction* I can't find a way to get a form to execute a query.

#5

Hi Alanon,

could be you are looking for something like this:

http://robert.familiegrosskopf.de/download/Filter_Database.odb

One table "Table" with the content you will list, one other table
called "Filter" with only one row. A query lists the content of
"Table" dependent of the content of "Filter".

In the form there are two separate forms - one for "Filter", one for
the query. "Filter" will read out the value you have choosen from a
listbox. The button refreshes the other form for the query. There are
no macros needed.

Regards

Robert

#6

On form Form_Filter, is it possible to define the List Box using General -
List Entries and Data - ValuelList (without using the separate table Filter
and query Query_Filter)? The Base Handbook has brief comments on the List
Entries & ValueList but only defining those items does not produce a
working List Box.

#7

Hi Bruce,

On form Form_Filter, is it possible to define the List Box using
General - List Entries and Data - ValuelList

No problem to define it separately. But I would only use this kind of
entries if I would knew all entries, which ever should be chosen by
the listbox. So, for example, a listbox for "Mrs.", "Mr." or something
like this. All others I would fill with entries of a table of the
database.

(without using the separate table Filter and query Query_Filter)?

What you write inside the brackets has nothing to do with the
value-list of the listbox. It is one possibility to filter a table by
a listbox without using any macro. You could get nearly the same
result with a construction of mainform and subform. Then you could do
it without the table "Filter", but also without a listbox. You could
only navigate through the mainform with the distincted values of
"Category" and the subform will show the rows of this "Category".

The Base Handbook has brief comments on the List Entries &
ValueList but only defining those items does not produce a working
List Box.

The entries you are defining must match with the entries the field for
the values would accept. You could create a listbox without any
connection to data of any table - but you can't use this for a filter
without also using macros.

Regards

Robert