Form Values as Query Criteria

Creating and using forms

Form Values as Query Criteria

Postby slc193 » Wed May 28, 2008 9:35 pm

How do you take a value entered or selected in a field and set it as a criteria for a query? Is there any sort of tool for OO Base like MS Access' Expression Builder to accomplish this? Or is it simply a must to know SQL code in order to work with Base?

TIA.
slc193
 
Posts: 11
Joined: Sat Mar 15, 2008 5:08 pm

Re: Form Values as Query Criteria

Postby kabing » Wed May 28, 2008 11:58 pm

Can you say more about what it is that you want to do?

Depending on your needs, form based filters might accomplish the same thing for you.

If you really need a query based on a value in a field, then you will need to use a macro written in Star Basic.

kabing
NeoOffice 3.3, OpenOffice.org 3.4 and LibreOffice 3.4.3 on Mac OS X 10.8
User avatar
kabing
Volunteer
 
Posts: 634
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Form Values as Query Criteria

Postby slc193 » Fri Jul 18, 2008 2:39 pm

I have a form where data about an invoice is entered. The fields present are: Vendor, Invoice#, Date of Purchase, Purchaser, Entered By, Date of Entry, Season, and Total Price.

There is a subform in table view for entering the each line of purchase on the invoice. These fields are: Quantity, Item, Catalog #, Purchase Price, Show, and Department.

I have the query that looks up the data that I want on the report built and it works fine if I manually enter the InvoiceID (which is an autonumber field in the Invoice Table) into the query. I also have the report built and finished.

The issue is: How do I have the value of the InvoiceID field in the query be defined by whichever invoice is currently displayed on the Invoice form?

My dilemma is that I am very much a newbie at BASIC. I have just learned some of Visual Studio 2005, but the commands are different and I have been having trouble finding how to do the simple commands (like Open, which is show.form.* in VB and Close, which is *.close) in OO BASIC.
slc193
 
Posts: 11
Joined: Sat Mar 15, 2008 5:08 pm

Re: Form Values as Query Criteria

Postby slc193 » Fri Jul 18, 2008 2:50 pm

I guess the bigger question would be:

How do you dimension the value currently displayed in a field on a form?

and...

How do you call that value up in a query?
slc193
 
Posts: 11
Joined: Sat Mar 15, 2008 5:08 pm

Re: Form Values as Query Criteria

Postby ugm6hr » Fri Jan 16, 2009 10:02 pm

I have been struggling with a slightly less complex situation, but would benefit from working towards a complete solution.

You need to add the Form Value as a field in the Query on which the Form is based, and ensure that this field is assigned an Alias (e.g. Alias). In the Query criteria, just enter anything for now (e.g. =1).

Then re-edit the Query in SQL mode, and edit the query criteria to change whatever you entered as criteria for the Alias in " quote marks (i.e. ="Alias")

This appears to work in the Query itself. Hopefully, a form based on this query will also work.

My blog re: this issue http://cardiologylogbook.wordpress.com/ ... date-range
Last edited by ugm6hr on Tue Feb 17, 2009 9:33 am, edited 1 time in total.
ugm6hr
 
Posts: 4
Joined: Sun Apr 27, 2008 12:02 pm

Re: Form Values as Query Criteria

Postby Villeroy » Fri Jan 16, 2009 10:27 pm

Download download/file.php?id=3371
Add a query ...
Code: Select all   Expand viewCollapse view
SELECT "T"."ID", "T"."Name", "T"."Picture", "T"."Description", "F"."ID"
FROM "Table1" AS "T", "Filter" AS "F"
WHERE ("T"."Name" = "F"."Name") AND ("F"."ID" = 0)

... and build a simple report based on that query.

Open the form, pick a record by picture name from the combo, hit [Refresh], load the report.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17311
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Forms

Who is online

Users browsing this forum: No registered users and 5 guests