[Solved] Parameter query in macro?

Creating tables and queries
Post Reply
Bollards
Posts: 3
Joined: Thu Feb 26, 2009 5:13 pm

[Solved] Parameter query in macro?

Post by Bollards »

Hello. I have done the following:
Created a query using the GUI which contains a parameter so that when the query is run, the user has to input a string in order to filter the data appropriately.
I want to run this query from within a macro, so I have written an executeQuery macro in basic. The SQL is of the form SELECT * INTO NOWPT FROM(my query which has been copied and pasted from the SQL view).
The end of the SQL looks like this ....... AS ""TermDates"" WHERE ""Term""=:Term
This seems to be causing a problem as I get an error message "Direct execute with parameter count>0" etc. I know that it is this bit of the code which is causing a problem because when I remove the "WHERE ""Term""=:Term" it executes properly.
My question is, is it possible to execute a query from a macro where the user can input to filter the records or do I have to write some sort of message box thing in basic which I don't currently know how to?
Please help if you can - Thanks.
Last edited by Bollards on Sat Feb 28, 2009 5:08 pm, edited 1 time in total.
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: parameter query in macro?

Post by Villeroy »

Parameter queries are part of the frontend application. Base parses the query, promts you for parameter substitution, compiles the query and sends it to the database server. SELECT INTO "talks" directly to the backend database. It does not recognize the =:Param syntax. It can not prompt for the substitution since it does not even know if there is a GUI or if it runs on some web-server.

How I would tackle this:
Create a filter-table which takes the parameters entered through a simple form. One list box may suffice.
Create a view which selects the records from some table where table.this=filter.that. A view is like a query but within the scope of the backend.
Your form may include a subform linked to the view and a button which calls your macro triggering the SELECT * FROM "view" INTO "table".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Bollards
Posts: 3
Joined: Thu Feb 26, 2009 5:13 pm

Re: parameter query in macro?

Post by Bollards »

Villeroy wrote:Create a filter-table which takes the parameters entered through a simple form. One list box may suffice.
Create a view which selects the records from some table where table.this=filter.that.
Thanks for your response. I don't understand how to create a filter-table taking the parameters from a form. I've made the form with a list box from which the term can be selected, but can't get any further than that currently. Could you point me in the right direction please as I've done some searches on the internet and can't seem to find what I'm looking for. Thanks.
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: parameter query in macro?

Post by Villeroy »

combo_filter3.odb contains several (simple but ugly) forms where a combo box filters the records of another form one way or the other.
Base requires form controls to be bound in order to be usable without macro code. I simply created an extra table "Filter" to bind the filtering box to some field.
The same trick can be used to limit a report to certain records defined in a form:pictures.odb has a form to display/edit one picture at a time. The report is uses a query to show only the single record that has been selected in the form.

For a single string-parameter you may prefer the Basic Inputbox, but forms offer some specialized controls for different types of data.

Code: Select all

s = Inputbox ("Type a name","Box Title")
if s = "" then
  s = IS NULL
else
 s = "='"& s & "'"
endif
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Bollards
Posts: 3
Joined: Thu Feb 26, 2009 5:13 pm

Re: parameter query in macro?

Post by Bollards »

Thanks for that. I've used the message box solution as it is only a single string. It works very nicely thank you!
OOo 3.0.X on Ms Windows XP
Post Reply