Access Query Forms - Trying to Use Multiple Together 2

[rchamb1010]

I have several search categories I'm using to query a database. However, Access tells me I was using too many filters in a single query form and cancels the operation everytime I try to run it. To get around that, I created two forms (splitting up the search categories) and that worked fine, but I have two forms. I really need to have all of the categories presented to the user on a single form. So I tried pasting the forms as subforms on another form which I would use as a "frame" - but that isn't working, either when I paste and link or tell it nothing to link or do not give the main form a record source. Any ideas on how I can otherwise show all of my search categories on a single "form"? Many thanks!

 

[CautionMP]

rchamb1010,
What mechanism are you using to apply the data from your form as the filter criteria for you query?
[ol]
[li]Are the form fields functioning as parameters for your query?[/li]
[li]Are you building a filter string using the fields on the form and then applying it to the query?[/li]
[li]Other?[/li][/ol]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[rchamb1010]

Thanks - the form fields function as parameters.

 

[CautionMP]

rchamb1010,
Have you considered adding a mechanism to your form that converts all the search categories into a filter string you can pass to the query?

As long as the filter string is less than 255 characters you could then use [tt]DoCmd.ApplyFilter()[/tt] to pass the string to your query.

Just a thought,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dhookom]

What are you attempting to filter, a form or report? How are you attempting to filter? Are you using code to create a filter or where condition? Or, are you using references to controls on forms in the criteria of a query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rchamb1010]

Thanks, Duane.

I'm attempting to filter a form, using a query linked to a large data table. I'm using references to controls on forms in the criteria of a query.

 

[rchamb1010]

To follow up with Duane and CMP, I currently have 16 filters, search categories. Many thanks!

 

[dhookom]

I rarely build queries that reference dynamic criteria from prompts or controls on forms. I generally loop through criteria controls on a form to build a where condition:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & " AND [DateField]>=#" & _
        Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " AND [DateField]<=#" & _
        Me.txtEndDate & "# "
End If
[green]'more conditions[/green]

I then open a form or report using the strWhere as the Where Condition. I suppose you could also use strWhere to apply to the filter property of an open form.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CautionMP]

rchamb1010,
I was thinking along the same lines as dhookom but ended up being pretty busy today so I couldn't follow the post.

Here is a generic routine you can use in a form to build a filter sting on the fly. Because I don't know the names of your form controls and query fields I stored this information in an array to demonstrate the idea. You will need to update all the info in the array to reflect your real data.

Function Build_Filter_String() As String
'This will hold the Control name, Field name, Field type
Dim strFields(1 To 5, 1 To 3) As Variant
Dim intIndex As Integer
Dim strFilter As String
'Load the control/field/data type info
strFields(1, 1) = "Text1": strFields(1, 2) = "Field1": strFields(1, 3) = dbText
strFields(2, 1) = "Text2": strFields(2, 2) = "Field2": strFields(2, 3) = dbLong
strFields(3, 1) = "Text3": strFields(3, 2) = "Field3": strFields(3, 3) = dbDate
strFields(4, 1) = "Text4": strFields(4, 2) = "Field4": strFields(4, 3) = dbCurrency
strFields(5, 1) = "Text5": strFields(5, 2) = "Field5": strFields(5, 3) = dbBoolean
'Build the filter string
For intIndex = 1 To UBound(strFields)
  If Trim(Me.Controls(strFields(intIndex, 1))) <> "" Then
    'Add the spaces and AND clause
    If Len(strFilter) > 0 Then
      strFilter = strFilter & " AND "
    End If
    'Use the BuildCriteria clause to properly format the expression
    strFilter = strFilter & _
    BuildCriteria(strFields(intIndex, 2), _
                  strFields(intIndex, 3), _
                  Me.Controls(strFields(intIndex, 1)))
  End If
Next intIndex
Build_Filter_String = strFilter
End Function

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[rchamb1010]

Thank you Duane and CMP! I also got tied up yesterday and wasn't able to respond. I'll try both of these.

 

[lespaul]

Duane,

I've noticed in several posts the 'Where 1 = 1' clause and have wondered what that is for and what it's doing. Could you please explain it for me?

thanks!
les

 

[PHV]

Starting a WHERE clause with an always true condition permits to append any number of AND conditions without problem.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

PH hit the nail on the head. I rarely write code like this with a where clause built from a single criteria. Stringing multiple criteria together beginning with "1=1" allows me to add additional criteria beginning with " AND ". I don't ever have to check the length of strWhere to see if I need to add or remove " AND ".



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Thanks for explaining that, always like to understand what's going on!

Les