creating a filter textboxes for a subform

D

DawnTreader

Hello

on my unbound mainform i would like to have a box that controls the query
that the subform is based on. is there a way through code to tell fields in
the subform to be filtered on what is typed in text boxes on the mainform?

do i need to place any special stuff in the query? i know how to create
parameter queries, but this would seem to be more complicated than that.

any help is appreciated! :)
 
M

Marshall Barton

DawnTreader said:
on my unbound mainform i would like to have a box that controls the query
that the subform is based on. is there a way through code to tell fields in
the subform to be filtered on what is typed in text boxes on the mainform?

do i need to place any special stuff in the query? i know how to create
parameter queries, but this would seem to be more complicated than that.
Click to expand...


Using parameters in the query is a common way to do that.
The parameters will look like :
Forms!mainformname.controlname

If you want some of the controls to be optional parameters
in the query's criteria, then use:
Forms!mainformname.controlname OR
Forms!mainformname.controlname Is Null

If you feel comfortable using VBA code to construct the
query's SQL statement, I think this is a better way.
 
D

DawnTreader

Hello

thanks for the response. i am not sure how to implement the code you have
given. where do i put it?

i am going to try and clairify how i have the situation set up.

first i have a query. that query was the basis for a form. i used that form
as a subform in an unbound form. so i have a part query, a part form based on
the query and an unbound form with unbound controls.

on my main unbound form, i have a set of check boxes that hides and unhides
columns on the part (sub)form. i have on the main unbound form several text
boxes, one for each column in the part (sub)form, that i want people to be
able to type in criteria for the query that is the record source for the part
(sub)form. this way they can query the parts list without having to create a
query in the query grid.

what i need to know is; what code do i put on the text boxes on the main
form, and/or what code to i put in the controls on the part (sub)form? i
understand your code, but i am uncertain where to put it.

further, would it be possible to put a second set of text boxes in a second
row to create "and" and "or" criteria?

thanks in advance. :)
 
D

DawnTreader

i seem to have put this post in more than once and gotten responses from
each. here is one of them from another thread.

"Hi -

I do just that, and my approach is to just change the subform's
recordsource using SQL, like this:

SubFormContainer.Form.RecordSource = SQL

where SQL selects all the required fields, with the WHERE clause doing
the filtering as required. Here's an example SQL from my application:

SELECT distinct [film], [frame], [picture number formatted], [topic],
[location], [loc2], [storagecde],[storage1] FROM [PHOTO LIST] WHERE
[word] like 'burton*' AND [location code] = '603'

The most important part is getting the where clause right - the selected
fields will be the same.

John"
 
D

DawnTreader

Here is another answer to another thread

"Lets say you have MainForm with control ComboBox. Create Query1 based on the
value Forms!MainForm!ComboBox. Create SubForm based on Query1 and when
ComboBox in MainForm is updated, invoke Me.SubForm.Requery. HTH"
 
M

Marshall Barton

The criteria I posted is supposed to be used in the criteria
row of the subform's record source query. However, as I
tried to imply earlier, this approach is not appropriate for
anything beyond simple situations and totally out of the
question for all the options you want, so let's start over.

This may seem pretty complex to you, so be sure to feel free
to retrench your needs if this is dropping you in the deep
end ;-) You will also need to check with VBA Help for
each item that you are not familiar with. I also think we
should not get involved with the OR option until we get the
AND option working.

First, we need to create a VBA procedure that creates the
subform's Filter property. To do this, we need a way for
the function to find all the criteria text boxes. This is
most easily done by naming the text boxes in a uniform way
such as txtA1, txtA2, etc.

We then need a way for the function to determine the related
column and its data type for each unbound criteria text box.
The easiest way to convey this information from the text box
to the function is to place it in the text box's Tag
property. Lets choose a syntax like fieldname:typenumber
where fieldname is the name of the field in the table and
typenumber is the code for the data type of the field
(3 - Integer, 4 - Long, 5 - Currency, 10 - Text, 12 - Memo,
etc).

With all that taken care of, the procedure in the main
form's module would look something like this air code:

Sub ApplyFilter()
Dim strWhere As String
Dim k As Integer
Dim intPos As Integer
Dim strField As String
Dim intType As Integer

For k = 1 to N ' N is number of text boxes
With Me("txtA" & k)
If Not IsNull(.Value) Then
intPos = InStr(.Tag, ":")
strField = Left(.Tag, intPos -1)
intType = Val(Mid(.Tag, intPos + 1)
strWhere = " AND " & BuildCriteria(strField, _
intType, .Value)
End If
End With
Me.subformcontrolname.Form.Filter = Mid(strWhere, 6)
Me.subformcontrolname.Form.FilterOn = True
End Sub

Now you can add a button to the main form (I'll call it the
search button) to apply the criteria by using the line of
code:
ApplyFilter
in the button's Click event procedure
 
Top