Query from Form

[kerickson]

I have a form where a user enters a Project ID number. I need to then bring up another form that contains only the records that have that same Project ID. I have that form already designed, and the user will use it to edit data for multiple records - whichever records contain that Project ID they entered in the previous form.

I think I need to create a query to do this. I'm guessing the query should have the user-entered Project ID as a criteria, but I don't know how to reference something like that in the criteria field. How do I do a query based on a user input to a separate form? Can someone help me with the syntax for that?

Also, how do I set up that form for the user to enter something? Does that information need to go in a table somewhere, or how does that work?

Thanks for your help!!

[QuazzieEvil]

You can use form filters. The easiest way is to switch the form to filter mode (click on form filters button in form navigation bar--when in data mode). when in filter mode, enter the filter criteria and apply the filter.

Also, you can write a script to apply the filter. If you will only have one column for the filter, you can use an input box to promp the user for the id. the take that entry to apply the filter. Else create another form, which has your controls to create the filter.

Example:

Sub cmdFilter_onClick(Event As Ojbect)
REM CODE BOUND TO MOUSE BUTTON PRESSED EVENT OF COMMAND BUTTON
Dim Form As Object
Dim strFilter As String
Dim strID As String

Form=Event.source.Model.parent
strID=InputBox("Please enter an ID")
strFilter="PROJECTID=" & strID REM PROEJCTID IS THE NAME OF THE COLUMN TO FOR THE FILTER
Form.Filter=strFilter
Form.ApplyFilters=True
Form.reload() REM MUST RELOAD FORM TO VIEW FILTERED FORM

End Sub

[NiksaVel]

Hi,

this fileter by input is EXACTLY what I've been looking for, but I need just a little bit of help here... if I use it to filter by ID column (number) it works as advertised

But if I try to use it on another column which is varchar (lastname) than it fails with:

SQL Status: S0022
Error code: -28

Column not found: a in statement [SELECT * FROM "Pacijenti" WHERE "Prezime" = "a" ORDER BY "Prezime" ASC, "Ime" ASC, "Datum_rod" ASC]

Some1 please help

[NiksaVel]

I figured out by messing around with querries that:

Column not found: a in statement [SELECT * FROM "Pacijenti" WHERE "Prezime" = "a" ORDER BY "Prezime" ASC, "Ime" ASC, "Datum_rod" ASC]

should be single quotes... not sure why I get double quotes (" ")?? How to get around this... than I get no error... at least in a query SQL view. I need to figure out how to get it working in this macro !?


#2 question is how to use a wildcard in here? i.e. I can only filter by specific lastname here... I'd like to filter by a letter of the alphabet... or the first few letters of a lastname...


thanks in advance for all the help!

[Villeroy]

I have a form where a user enters a Project ID number. I need to then bring up another form that contains only the records that have that same Project ID. I have that form already designed, and the user will use it to edit data for multiple records - whichever records contain that Project ID they entered in the previous form.

The simple feature that shows data related to the selected record in a form is called "subform" (no macros, no voodo, no problems). It will not load another separate form. You define a form within a form and link the subform through a common field (ProjectID in this case).
See this tread with the attaced database "movies_2.odb". For editing forms I use to recommend my example database http://user.services.openoffice.org/en/ ... hp?id=1266 with a calculated field in a subform and some hints how to edit forms manually without wizard. Hint: Forms Navigator

[NiksaVel]

but I actually need EXACLY this feature (filtering) as I need to actually filter the large table (several hundred patients)... I know I'm really close with this, just need a few pointers

[Villeroy]

Two filters by combo-box, also mentioning the macro approach and why I'm against macros if they are avoidable:
Beginner stuck linking combo to subform

However, I still believe that the form & subform approach matches better with the initial request:

I have a form where a user enters a Project ID number. I need to then bring up another form that contains only the records that have that same Project ID. I have that form already designed, and the user will use it to edit data for multiple records - whichever records contain that Project ID they entered in the previous form.

"Previous form" = main form
"another form that contains only the records that have that same Project ID" = subform.

By the way: The Base GUI provides quite usable filter tools on the navigation tool bar. You can define some filter for the currently viewed form (without changing the form's data source) and you can toggle this filter on and off, but this is not dynamicly based on the current record or something. Oh, yes you can also select one value and then push "Auto FIlter". [can anybody explain why this works with writable record sets only?]

[NiksaVel]

I agree this is the best solution to the original problem.. just not to my added question

I also agree that it is better to avoid macros if possible.. or as the saying goes.. "keep it simple stupid"


I know I have the filter in the GUI, and I can achieve what I need with it... but I am making a simplified user friendly database for end users and I don't want them to mess around in the standard GUI at all... in fact I am running all my forms/subforms in fullscreen (via macro:) ). In fact I already have a few dozen macros at work here to achieve all the stuff I need/want..


To sum up - I'd like to have two buttons... one is "filter" which than pops a question form and I fill in a letter and get the form reloaded with a filter set at the lastname column of the table (wildcard included so it's not an exact match filter). The other button is "Remove filter" which I have already created.

[Villeroy]

but I am making a simplified user friendly database for end users and I don't want them to mess around in the standard GUI at all...

In http://user.services.openoffice.org/en/ ... 281#p47033 voobase links to http://www.oooforum.org/forum/viewtopic.phtml?t=72134
What's wrong with my macro-free suggestions? OK, may be the helper table and the refresh button.
Personally I find the built-in filter tools acceptable, intuitive[*] and very close to filter buttons of other database tools, pretty much "standard" so to say.
[*] They are intuitive enough once you got the concept of set up and then toggle on/off. The form-based filter provides a very powerful GUI for arbitrary nested AND/OR-connected filter-criteria.

[NiksaVel]

yes I agree... but I am making this database to be used by senior medical doctors... which are at least in these parts still typing on mechanical type-writers for the most part... I need to simplify this process to two buttons as described... I'll take a look at these links later when I'm home from work. thanks!

[NiksaVel]

figured it out btw...

Code: Select all

Sub cmdFilter_onClick(Event As Object)
REM CODE BOUND TO MOUSE BUTTON PRESSED EVENT OF COMMAND BUTTON
Dim Form As Object



Form=Event.source.Model.parent
strID=InputBox("Unesite prezime ili dio prezimena")


Form.Filter = " UPPER(Prezime) LIKE " + "UPPER('%"+strID+"%')"


if strFilter <> "" then
Form.ApplyFilter = True
else
Form.ApplyFilter = False
end if

Form.reload

End Sub

and here is a simple macro for the "remove filter button"

Code: Select all

Sub cmdunFilter_onClick(Event As Object)

Dim Form As Object


Form=Event.source.Model.parent
Form.ApplyFilter=False
Form.reload 

End Sub